59 lines
1.7 KiB
PL/PgSQL
59 lines
1.7 KiB
PL/PgSQL
-- Gen2 owner-role provisioning for the dedicated bootstrap/migration principal.
|
|
-- This is intentionally separate from feature migrations and may be run repeatedly.
|
|
BEGIN;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'world_role') THEN
|
|
CREATE ROLE world_role LOGIN;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'configuration_role') THEN
|
|
CREATE ROLE configuration_role LOGIN;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'self_role') THEN
|
|
CREATE ROLE self_role LOGIN;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
DO $$
|
|
DECLARE
|
|
role_name TEXT;
|
|
can_login BOOLEAN;
|
|
BEGIN
|
|
FOREACH role_name IN ARRAY ARRAY['world_role', 'configuration_role', 'self_role'] LOOP
|
|
SELECT rolcanlogin INTO can_login FROM pg_roles WHERE rolname = role_name;
|
|
IF can_login IS DISTINCT FROM TRUE THEN
|
|
RAISE EXCEPTION 'Gen2 owner role % exists but is not a V1-compatible LOGIN role', role_name;
|
|
END IF;
|
|
END LOOP;
|
|
END
|
|
$$;
|
|
|
|
CREATE SCHEMA IF NOT EXISTS world AUTHORIZATION world_role;
|
|
CREATE SCHEMA IF NOT EXISTS configuration AUTHORIZATION configuration_role;
|
|
CREATE SCHEMA IF NOT EXISTS self AUTHORIZATION self_role;
|
|
|
|
REVOKE ALL ON SCHEMA world, configuration, self FROM PUBLIC;
|
|
|
|
DO $$
|
|
DECLARE
|
|
schema_name TEXT;
|
|
expected_owner TEXT;
|
|
actual_owner TEXT;
|
|
BEGIN
|
|
FOREACH schema_name IN ARRAY ARRAY['world', 'configuration', 'self'] LOOP
|
|
expected_owner := schema_name || '_role';
|
|
SELECT pg_get_userbyid(nspowner)
|
|
INTO actual_owner
|
|
FROM pg_namespace
|
|
WHERE nspname = schema_name;
|
|
IF actual_owner IS DISTINCT FROM expected_owner THEN
|
|
RAISE EXCEPTION 'Gen2 schema % is owned by %, expected %', schema_name, actual_owner, expected_owner;
|
|
END IF;
|
|
END LOOP;
|
|
END
|
|
$$;
|
|
|
|
COMMIT;
|