

COMMENT ON ROLE pg36_ch23_owner IS
    'pg36 chapter 23 security lab: object owner; synthetic fixture only';
COMMENT ON ROLE pg36_ch23_runtime IS
    'pg36 chapter 23 security lab: application runtime; synthetic fixture only';
COMMENT ON ROLE pg36_ch23_readonly IS
    'pg36 chapter 23 security lab: read-only workload; synthetic fixture only';
COMMENT ON ROLE pg36_ch23_migrate IS
    'pg36 chapter 23 security lab: migration authority; synthetic fixture only';
COMMENT ON ROLE pg36_ch23_rotate IS
    'pg36 chapter 23 security lab: direct-only rotation probe; synthetic fixture only';

ALTER ROLE pg36_ch23_rotate NOLOGIN PASSWORD NULL;

GRANT pg36_ch23_runtime, pg36_ch23_readonly
   TO test
 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE;

GRANT pg36_ch23_owner
   TO pg36_ch23_migrate
 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE;

GRANT dbrole_readonly
   TO pg36_ch23_rotate
 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE;

CREATE SCHEMA IF NOT EXISTS pg36_ch23
    AUTHORIZATION pg36_ch23_owner;

COMMENT ON SCHEMA pg36_ch23 IS
    'pg36 chapter 23 security lab; synthetic two-tenant data only';

SET ROLE pg36_ch23_owner;

CREATE OR REPLACE FUNCTION pg36_ch23.current_tenant()
RETURNS uuid
LANGUAGE sql
STABLE
PARALLEL SAFE
RETURN NULLIF(
    pg_catalog.current_setting('app.tenant_id', true),
    ''
)::uuid;

COMMENT ON FUNCTION pg36_ch23.current_tenant() IS
    'fail-closed transaction tenant context for the chapter 23 lab';

RESET ROLE;

DROP POLICY IF EXISTS account_runtime_select
    ON pg36_ch23.account;
DROP POLICY IF EXISTS account_readonly_select
    ON pg36_ch23.account;
DROP POLICY IF EXISTS account_runtime_insert
    ON pg36_ch23.account;
DROP POLICY IF EXISTS account_runtime_update
    ON pg36_ch23.account;
DROP POLICY IF EXISTS account_owner_all
    ON pg36_ch23.account;

CREATE POLICY account_runtime_select
    ON pg36_ch23.account
    FOR SELECT
    TO pg36_ch23_runtime
    USING (
        tenant_id = pg36_ch23.current_tenant()
    );

CREATE POLICY account_readonly_select
    ON pg36_ch23.account
    FOR SELECT
    TO pg36_ch23_readonly
    USING (
        tenant_id = pg36_ch23.current_tenant()
    );

CREATE POLICY account_runtime_insert
    ON pg36_ch23.account
    FOR INSERT
    TO pg36_ch23_runtime
    WITH CHECK (
        tenant_id = pg36_ch23.current_tenant()
    );

CREATE POLICY account_runtime_update
    ON pg36_ch23.account
    FOR UPDATE
    TO pg36_ch23_runtime
    USING (
        tenant_id = pg36_ch23.current_tenant()
    )
    WITH CHECK (
        tenant_id = pg36_ch23.current_tenant()
    );

CREATE POLICY account_owner_all
    ON pg36_ch23.account
    FOR ALL
    TO pg36_ch23_owner
    USING (
        tenant_id = pg36_ch23.current_tenant()
    )
    WITH CHECK (
        tenant_id = pg36_ch23.current_tenant()
    );

RESET ROLE;

REVOKE ALL ON SCHEMA pg36_ch23
   FROM PUBLIC,
        test,
        dbrole_readonly,
        dbrole_readwrite,
        dbrole_admin,
        dbrole_offline,
        pg36_ch23_runtime,
        pg36_ch23_readonly,
        pg36_ch23_migrate;

REVOKE ALL ON TABLE pg36_ch23.account
   FROM PUBLIC,
        test,
        dbrole_readonly,
        dbrole_readwrite,
        dbrole_admin,
        dbrole_offline,
        pg36_ch23_runtime,
        pg36_ch23_readonly,
        pg36_ch23_migrate;

REVOKE ALL ON FUNCTION pg36_ch23.current_tenant()
   FROM PUBLIC,
        test,
        dbrole_readonly,
        dbrole_readwrite,
        dbrole_admin,
        dbrole_offline,
        pg36_ch23_runtime,
        pg36_ch23_readonly,
        pg36_ch23_migrate;

GRANT USAGE ON SCHEMA pg36_ch23
   TO pg36_ch23_runtime,
      pg36_ch23_readonly;

GRANT SELECT, INSERT, UPDATE ON TABLE pg36_ch23.account
   TO pg36_ch23_runtime;

GRANT SELECT ON TABLE pg36_ch23.account
   TO pg36_ch23_readonly;

GRANT EXECUTE ON FUNCTION pg36_ch23.current_tenant()
   TO pg36_ch23_runtime,
      pg36_ch23_readonly;

ALTER DEFAULT PRIVILEGES
    FOR ROLE pg36_ch23_owner
    IN SCHEMA pg36_ch23
    REVOKE ALL ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES
    FOR ROLE pg36_ch23_owner
    IN SCHEMA pg36_ch23
    GRANT SELECT, INSERT, UPDATE ON TABLES
    TO pg36_ch23_runtime;
ALTER DEFAULT PRIVILEGES
    FOR ROLE pg36_ch23_owner
    IN SCHEMA pg36_ch23
    GRANT SELECT ON TABLES
    TO pg36_ch23_readonly;

ALTER DEFAULT PRIVILEGES
    FOR ROLE pg36_ch23_owner
    IN SCHEMA pg36_ch23
    REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES
    FOR ROLE pg36_ch23_owner
    IN SCHEMA pg36_ch23
    GRANT EXECUTE ON FUNCTIONS
    TO pg36_ch23_runtime,
       pg36_ch23_readonly;
