-- Global PREAUTH storage, managed by the migration identity, not application DDL. CREATE SCHEMA rmm_oidc_auth; REVOKE ALL ON SCHEMA rmm_oidc_auth FROM PUBLIC; CREATE TABLE rmm_oidc_auth.login_states ( state_hash bytea PRIMARY KEY CHECK(octet_length(state_hash)=32), binding_hash bytea NOT NULL CHECK(octet_length(binding_hash)=32), expires_at bigint NOT NULL CHECK(expires_at>0), created_at bigint NOT NULL DEFAULT floor(extract(epoch FROM clock_timestamp()))::bigint, encryption_key_ref text NOT NULL CHECK(length(encryption_key_ref) BETWEEN 1 AND 256), encryption_nonce bytea NOT NULL CHECK(octet_length(encryption_nonce)=12), ciphertext bytea NOT NULL CHECK(octet_length(ciphertext) BETWEEN 16 AND 4096), CHECK(expires_at>created_at AND expires_at<=created_at+600) ); CREATE INDEX login_states_expiry ON rmm_oidc_auth.login_states(expires_at,state_hash); REVOKE ALL ON rmm_oidc_auth.login_states FROM PUBLIC; -- The administrator grants these privileges to a SEPARATE login identity later. -- No automatic role/password creation or grants to the tenant application role. CREATE TABLE public.oidc_principals ( principal text PRIMARY KEY CHECK(principal ~ '^oidc:[0-9a-f]{64}$'), issuer text NOT NULL, subject text NOT NULL CHECK(length(subject) BETWEEN 1 AND 1024), tenant_ids uuid[] NOT NULL CHECK(cardinality(tenant_ids) BETWEEN 1 AND 100), disabled boolean NOT NULL DEFAULT false, UNIQUE(issuer,subject) ); REVOKE ALL ON public.oidc_principals FROM PUBLIC; -- This index is administrator-provisioned routing metadata, NOT membership/roles. -- Current membership must still be checked in each selected tenant transaction. CREATE FUNCTION public.oidc_identity_binding(issuer_value text, subject_value text) RETURNS TABLE(principal text,tenant_ids uuid[]) LANGUAGE sql SECURITY DEFINER SET search_path=pg_catalog,public AS $$ SELECT p.principal,p.tenant_ids FROM public.oidc_principals p WHERE p.issuer=issuer_value AND p.subject=subject_value AND NOT p.disabled; $$; CREATE FUNCTION public.oidc_principal_enabled(principal_value text) RETURNS boolean LANGUAGE sql SECURITY DEFINER SET search_path=pg_catalog,public AS $$ SELECT EXISTS(SELECT 1 FROM public.oidc_principals p WHERE p.principal=principal_value AND NOT p.disabled); $$; REVOKE ALL ON FUNCTION public.oidc_identity_binding(text,text),public.oidc_principal_enabled(text) FROM PUBLIC; GRANT EXECUTE ON FUNCTION public.oidc_identity_binding(text,text),public.oidc_principal_enabled(text) TO rmm_app; ALTER TABLE memberships DROP CONSTRAINT memberships_kind_check; ALTER TABLE memberships ADD CONSTRAINT memberships_kind_check CHECK(kind IN ('viewer','automation','test_human','human')); ALTER TABLE authorizations DROP CONSTRAINT authorizations_approval_origin_check; ALTER TABLE authorizations DROP CONSTRAINT authorizations_testsimulation_check; ALTER TABLE authorizations ADD CONSTRAINT authorization_interactive_origin CHECK( (approval_origin='dev_test_browser_simulation' AND testsimulation) OR (approval_origin='human_webui' AND NOT testsimulation));