-- Isolated fresh PostgreSQL contract only; no product job/approval or signature -- claim. Run as the migration identity after migrations 1..15 on a test DB. SET ROLE rmm_app; BEGIN; SELECT set_config('app.tenant_id','11111111-1111-4111-8111-111111111111',true); INSERT INTO agent_update_artifacts(tenant_id,artifact_id,sha256,size_bytes) VALUES ('11111111-1111-4111-8111-111111111111','aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa1',repeat('a',64),300001), ('11111111-1111-4111-8111-111111111111','aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa2',repeat('b',64),300003); INSERT INTO agent_update_releases(tenant_id,release_id,platform,release_sequence,manifest_sha256,envelope_sha256,envelope,agent_artifact_id,agent_sha256,agent_bytes,helper_artifact_id,helper_sha256,helper_bytes) VALUES('11111111-1111-4111-8111-111111111111','aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa3','x86_64-unknown-linux-gnu',1,repeat('c',64),repeat('d',64),'{}','aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa1',repeat('a',64),300001,'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa2',repeat('b',64),300003); COMMIT; BEGIN; SELECT set_config('app.tenant_id','22222222-2222-4222-8222-222222222222',true); DO $$ BEGIN IF (SELECT count(*) FROM agent_update_artifacts)<>0 OR (SELECT count(*) FROM agent_update_releases)<>0 THEN RAISE EXCEPTION 'foreign tenant visible'; END IF; BEGIN INSERT INTO agent_update_artifacts(tenant_id,artifact_id,sha256,size_bytes) VALUES('11111111-1111-4111-8111-111111111111','bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbb1',repeat('e',64),12); RAISE EXCEPTION 'foreign tenant insert accepted'; EXCEPTION WHEN insufficient_privilege THEN NULL; END; BEGIN INSERT INTO agent_update_releases(tenant_id,release_id,platform,release_sequence,manifest_sha256,envelope_sha256,envelope,agent_artifact_id,agent_sha256,agent_bytes,helper_artifact_id,helper_sha256,helper_bytes) VALUES('22222222-2222-4222-8222-222222222222','bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbb3','x86_64-unknown-linux-gnu',1,repeat('c',64),repeat('d',64),'{}','aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa1',repeat('a',64),300001,'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaa2',repeat('b',64),300003); RAISE EXCEPTION 'cross tenant artifact foreign key accepted'; EXCEPTION WHEN foreign_key_violation THEN NULL; END; END $$; COMMIT; -- No tenant setting survived the preceding transaction on this same connection. DO $$ BEGIN IF (SELECT count(*) FROM agent_update_artifacts)<>0 OR (SELECT count(*) FROM agent_update_releases)<>0 THEN RAISE EXCEPTION 'tenant context leaked after commit'; END IF; IF EXISTS(SELECT 1 FROM pg_roles WHERE rolname=current_user AND (rolsuper OR rolbypassrls)) OR EXISTS(SELECT 1 FROM pg_tables WHERE schemaname='public' AND tableowner=current_user) THEN RAISE EXCEPTION 'unsafe application role'; END IF; END $$; BEGIN; SELECT set_config('app.tenant_id','11111111-1111-4111-8111-111111111111',true); DO $$ BEGIN IF (SELECT count(*) FROM agent_update_artifacts)<>2 OR (SELECT count(*) FROM agent_update_releases)<>1 THEN RAISE EXCEPTION 'own immutable catalog missing'; END IF; BEGIN UPDATE agent_update_artifacts SET sha256=repeat('f',64); RAISE EXCEPTION 'application update accepted'; EXCEPTION WHEN insufficient_privilege THEN NULL; END; BEGIN DELETE FROM agent_update_releases; RAISE EXCEPTION 'application delete accepted'; EXCEPTION WHEN insufficient_privilege THEN NULL; END; IF (SELECT count(*) FROM jobs)<>0 OR (SELECT count(*) FROM outbox)<>0 OR (SELECT count(*) FROM execution_permits)<>0 OR (SELECT count(*) FROM authorizations)<>0 THEN RAISE EXCEPTION 'catalog created execution authority'; END IF; END $$; COMMIT; RESET ROLE; SET ROLE rmm_migrator; BEGIN; SELECT set_config('app.tenant_id','11111111-1111-4111-8111-111111111111',true); DO $$ BEGIN BEGIN UPDATE agent_update_artifacts SET sha256=repeat('f',64); RAISE EXCEPTION USING ERRCODE='XX000',MESSAGE='migration owner bypassed immutable object trigger'; EXCEPTION WHEN raise_exception THEN NULL; END; BEGIN DELETE FROM agent_update_releases; RAISE EXCEPTION USING ERRCODE='XX000',MESSAGE='migration owner bypassed immutable release trigger'; EXCEPTION WHEN raise_exception THEN NULL; END; END $$; COMMIT; SELECT 'agent-update-catalog-rls-immutable-no-execution: PASS';