6a299d231f
- Implemented tests for RouterConfig, RoutingOptions, StaticInstanceConfig, and RouterConfigOptions to ensure default values are set correctly. - Added tests for RouterConfigProvider to validate configurations and ensure defaults are returned when no file is specified. - Created tests for ConfigValidationResult to check success and error scenarios. - Developed tests for ServiceCollectionExtensions to verify service registration for RouterConfig. - Introduced UdpTransportTests to validate serialization, connection, request-response, and error handling in UDP transport. - Added scripts for signing authority gaps and hashing DevPortal SDK snippets.
99 lines
4.0 KiB
PL/PgSQL
99 lines
4.0 KiB
PL/PgSQL
-- IssuerDirectory PostgreSQL schema (designed from docs/modules/issuer-directory/architecture.md)
|
||
-- Status: PROPOSED (2025-12-05) – replaces Mongo collections issuer_directory.issuers / issuer_keys / issuer_audit
|
||
|
||
CREATE SCHEMA IF NOT EXISTS issuer;
|
||
|
||
-- Issuers (tenant or global)
|
||
CREATE TABLE IF NOT EXISTS issuer.issuers (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
tenant_id UUID NOT NULL, -- use @global GUID for seed publishers
|
||
name TEXT NOT NULL, -- logical issuer name (slug)
|
||
display_name TEXT NOT NULL,
|
||
description TEXT,
|
||
endpoints JSONB DEFAULT '{}'::jsonb, -- CSAF feeds, OIDC issuer URLs, contact links
|
||
tags TEXT[] DEFAULT '{}',
|
||
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','revoked','deprecated')),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
created_by TEXT,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_by TEXT,
|
||
UNIQUE (tenant_id, name)
|
||
);
|
||
|
||
-- Keys
|
||
CREATE TABLE IF NOT EXISTS issuer.issuer_keys (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
issuer_id UUID NOT NULL REFERENCES issuer.issuers(id) ON DELETE CASCADE,
|
||
key_id TEXT NOT NULL, -- stable key identifier
|
||
key_type TEXT NOT NULL CHECK (key_type IN ('ed25519','x509','dsse','kms','hsm','fido2')),
|
||
public_key TEXT NOT NULL, -- PEM / base64
|
||
fingerprint TEXT NOT NULL, -- canonical fingerprint for dedupe
|
||
not_before TIMESTAMPTZ,
|
||
not_after TIMESTAMPTZ,
|
||
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','retired','revoked')),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
created_by TEXT,
|
||
revoked_at TIMESTAMPTZ,
|
||
revoked_by TEXT,
|
||
revoke_reason TEXT,
|
||
metadata JSONB DEFAULT '{}'::jsonb,
|
||
UNIQUE (issuer_id, key_id),
|
||
UNIQUE (fingerprint)
|
||
);
|
||
|
||
-- Trust overrides (tenant-scoped weights)
|
||
CREATE TABLE IF NOT EXISTS issuer.trust_overrides (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
issuer_id UUID NOT NULL REFERENCES issuer.issuers(id) ON DELETE CASCADE,
|
||
tenant_id UUID NOT NULL, -- consumer tenant applying the override
|
||
weight NUMERIC(5,2) NOT NULL CHECK (weight >= 0 AND weight <= 1),
|
||
rationale TEXT,
|
||
expires_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
created_by TEXT,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_by TEXT,
|
||
UNIQUE (issuer_id, tenant_id)
|
||
);
|
||
|
||
-- Audit log (issuer-domain specific)
|
||
CREATE TABLE IF NOT EXISTS issuer.audit (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
tenant_id UUID NOT NULL,
|
||
actor TEXT,
|
||
action TEXT NOT NULL, -- create_issuer, update_issuer, delete_issuer, add_key, rotate_key, revoke_key, set_trust, delete_trust, seed_csaf
|
||
issuer_id UUID,
|
||
key_id TEXT,
|
||
trust_override_id UUID,
|
||
reason TEXT,
|
||
details JSONB DEFAULT '{}'::jsonb,
|
||
correlation_id TEXT,
|
||
occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- Indexes
|
||
CREATE INDEX IF NOT EXISTS idx_issuers_tenant ON issuer.issuers(tenant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_issuers_status ON issuer.issuers(status);
|
||
CREATE INDEX IF NOT EXISTS idx_keys_issuer ON issuer.issuer_keys(issuer_id);
|
||
CREATE INDEX IF NOT EXISTS idx_keys_status ON issuer.issuer_keys(status);
|
||
CREATE INDEX IF NOT EXISTS idx_trust_tenant ON issuer.trust_overrides(tenant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_audit_tenant_time ON issuer.audit(tenant_id, occurred_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_audit_issuer ON issuer.audit(issuer_id);
|
||
|
||
-- Updated-at trigger for issuers/trust overrides
|
||
CREATE OR REPLACE FUNCTION issuer.update_updated_at()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER trg_issuers_updated_at
|
||
BEFORE UPDATE ON issuer.issuers
|
||
FOR EACH ROW EXECUTE FUNCTION issuer.update_updated_at();
|
||
|
||
CREATE TRIGGER trg_trust_updated_at
|
||
BEFORE UPDATE ON issuer.trust_overrides
|
||
FOR EACH ROW EXECUTE FUNCTION issuer.update_updated_at();
|