Files
2026-11-14 21:48:21 +01:00

63 lines
4.1 KiB
PL/PgSQL

-- 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';