48 lines
3.0 KiB
PL/PgSQL
48 lines
3.0 KiB
PL/PgSQL
-- 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));
|