"""Put WP06 state behind a dedicated database owner and write gateway. The runtime login is deliberately not a member of either role established here. Role creation is performed only by the one-shot role-init process; this migration therefore fails closed if a deployment skipped that step. """ from alembic import op revision = "20260811_486" down_revision = "20260811_485" branch_labels = None depends_on = None _TABLES = ( "trusted_delivery_policy_versions", "trusted_delivery_policy_transitions", "trusted_delivery_grants", "trusted_delivery_deliveries", "trusted_delivery_receipts", "trusted_delivery_legal_holds", "trusted_delivery_hold_releases", "trusted_delivery_evidence", "trusted_delivery_subscriptions", "trusted_delivery_subscription_deliveries", "trusted_delivery_control_profile_versions", "trusted_delivery_control_profile_transitions", "trusted_delivery_capability_approvals", "trusted_delivery_supply_chain_gates", "trusted_delivery_control_evidence", "trusted_delivery_destruction_approvals", ) def upgrade() -> None: op.execute( """ DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='dataops_trusted_delivery_owner') OR NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='dataops_trusted_delivery_writer') THEN RAISE EXCEPTION 'trusted-delivery role-init is required before migration 20260811_486'; END IF; IF NOT pg_has_role(current_user, 'dataops_trusted_delivery_owner', 'MEMBER') OR NOT pg_has_role(current_user, 'dataops_trusted_delivery_writer', 'MEMBER') THEN RAISE EXCEPTION 'migrator must be pre-granted trusted-delivery owner and writer roles'; END IF; IF (SELECT rolcreaterole OR rolsuper FROM pg_roles WHERE rolname=current_user) THEN RAISE EXCEPTION 'migration identity must not retain CREATEROLE or superuser'; END IF; END $$; CREATE TABLE public.trusted_delivery_approval_facts ( approval_ref VARCHAR(200) PRIMARY KEY, approval_digest CHAR(64) NOT NULL CHECK(approval_digest ~ '^[0-9a-f]{64}$'), actor_uid UUID NOT NULL REFERENCES public.users(id), operation VARCHAR(40) NOT NULL CHECK(operation IN ('activate_policy','rollback_policy','activate_control_profile','rollback_control_profile','grant','release_hold','destruction')), status VARCHAR(20) NOT NULL CHECK(status IN ('approved','revoked','expired')), expires_at TIMESTAMPTZ NOT NULL, signed_receipt_digest CHAR(64) NOT NULL CHECK(signed_receipt_digest ~ '^[0-9a-f]{64}$'), created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp() ); CREATE OR REPLACE FUNCTION public.trusted_delivery_write(p_operation text, p_payload jsonb) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $trusted_delivery_write$ DECLARE v_uid uuid; v_policy uuid; v_existing record; v_approval text; v_digest text; v_actor uuid; BEGIN IF p_operation NOT IN ('create_policy','transition_policy','create_grant','create_subscription') OR jsonb_typeof(p_payload) <> 'object' THEN RAISE EXCEPTION 'trusted-delivery write is not permitted'; END IF; IF p_operation='create_policy' THEN IF NOT (p_payload ?& ARRAY['uid','code','version','selector','resource_rules','created_by']) OR p_payload - ARRAY['uid','code','version','selector','resource_rules','created_by','current_version'] <> '{}'::jsonb OR jsonb_typeof(p_payload->'selector')<>'object' OR jsonb_typeof(p_payload->'resource_rules')<>'object' THEN RAISE EXCEPTION 'trusted-delivery policy payload is closed'; END IF; v_uid := (p_payload->>'uid')::uuid; v_actor := (p_payload->>'created_by')::uuid; IF NOT EXISTS (SELECT 1 FROM public.users WHERE id=v_actor AND status='active') THEN RAISE EXCEPTION 'trusted-delivery actor is unavailable'; END IF; INSERT INTO public.trusted_delivery_policy_versions(uid,code,version,status,selector,resource_rules,created_by,current_version) VALUES(v_uid,p_payload->>'code',p_payload->>'version','draft',p_payload->'selector',p_payload->'resource_rules',v_actor,COALESCE((p_payload->>'current_version')::int,1)); RETURN jsonb_build_object('uid',v_uid::text,'code',p_payload->>'code','version',p_payload->>'version','status','draft','current_version',COALESCE((p_payload->>'current_version')::int,1)); END IF; IF p_operation='transition_policy' THEN IF NOT (p_payload ?& ARRAY['code','version','operation','idempotency_key','request_digest','actor_uid','approval_ref','approval_digest']) OR p_payload - ARRAY['code','version','operation','idempotency_key','request_digest','actor_uid','approval_ref','approval_digest'] <> '{}'::jsonb THEN RAISE EXCEPTION 'trusted-delivery transition payload is closed'; END IF; v_approval:=p_payload->>'approval_ref'; v_digest:=p_payload->>'approval_digest'; v_actor:=(p_payload->>'actor_uid')::uuid; IF NOT EXISTS (SELECT 1 FROM public.trusted_delivery_approval_facts WHERE approval_ref=v_approval AND approval_digest=v_digest AND actor_uid=v_actor AND operation=CASE WHEN p_payload->>'operation'='activate' THEN 'activate_policy' ELSE 'rollback_policy' END AND status='approved' AND expires_at>clock_timestamp()) THEN RAISE EXCEPTION 'trusted-delivery transition requires protected approval fact'; END IF; SELECT policy_uid,request_digest INTO v_existing FROM public.trusted_delivery_policy_transitions WHERE idempotency_key=p_payload->>'idempotency_key'; IF FOUND THEN IF v_existing.request_digest <> p_payload->>'request_digest' THEN RAISE EXCEPTION 'trusted delivery idempotency conflict'; END IF; RETURN (SELECT jsonb_build_object('uid',uid::text,'code',code,'version',version,'status',status,'current_version',current_version) FROM public.trusted_delivery_policy_versions WHERE uid=v_existing.policy_uid); END IF; SELECT uid INTO v_policy FROM public.trusted_delivery_policy_versions WHERE code=p_payload->>'code' AND version=p_payload->>'version' FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 'trusted delivery policy version was not found'; END IF; PERFORM 1 FROM public.trusted_delivery_policy_versions WHERE code=p_payload->>'code' AND status='active' FOR UPDATE; UPDATE public.trusted_delivery_policy_versions SET status='retired',current_version=current_version+1 WHERE code=p_payload->>'code' AND status='active' AND uid<>v_policy; UPDATE public.trusted_delivery_policy_versions SET status='active',current_version=current_version+CASE WHEN status='active' THEN 0 ELSE 1 END WHERE uid=v_policy; INSERT INTO public.trusted_delivery_policy_transitions(uid,policy_uid,operation,idempotency_key,request_digest,approval_ref,approval_digest,actor_uid) VALUES(gen_random_uuid(),v_policy,p_payload->>'operation',p_payload->>'idempotency_key',p_payload->>'request_digest',v_approval,v_digest,v_actor); RETURN (SELECT jsonb_build_object('uid',uid::text,'code',code,'version',version,'status',status,'current_version',current_version) FROM public.trusted_delivery_policy_versions WHERE uid=v_policy); END IF; IF p_operation='create_grant' THEN IF NOT (p_payload ?& ARRAY['uid','policy_uid','subject_uid','asset_uid','provider','idempotency_key','request_digest','expires_at','status','reason_code','created_by','approval_ref','approval_digest','purpose','environment','target_capability']) OR p_payload - ARRAY['uid','policy_uid','subject_uid','asset_uid','provider','idempotency_key','request_digest','expires_at','status','reason_code','current_version','created_by','approval_ref','approval_digest','purpose','environment','target_capability'] <> '{}'::jsonb THEN RAISE EXCEPTION 'trusted-delivery grant payload is closed'; END IF; v_approval:=p_payload->>'approval_ref'; v_digest:=p_payload->>'approval_digest'; v_actor:=(p_payload->>'created_by')::uuid; IF NOT EXISTS (SELECT 1 FROM public.trusted_delivery_approval_facts WHERE approval_ref=v_approval AND approval_digest=v_digest AND actor_uid=v_actor AND operation='grant' AND status='approved' AND expires_at>clock_timestamp()) THEN RAISE EXCEPTION 'trusted-delivery grant requires protected approval fact'; END IF; INSERT INTO public.trusted_delivery_grants(uid,policy_uid,subject_uid,asset_uid,provider,idempotency_key,request_digest,expires_at,status,reason_code,current_version,created_by,approval_ref,approval_digest,purpose,environment,target_capability) VALUES((p_payload->>'uid')::uuid,(p_payload->>'policy_uid')::uuid,(p_payload->>'subject_uid')::uuid,(p_payload->>'asset_uid')::uuid,p_payload->>'provider',p_payload->>'idempotency_key',p_payload->>'request_digest',(p_payload->>'expires_at')::timestamptz,p_payload->>'status',p_payload->>'reason_code',COALESCE((p_payload->>'current_version')::int,1),v_actor,v_approval,v_digest,p_payload->>'purpose',p_payload->>'environment',p_payload->'target_capability'); RETURN jsonb_build_object('uid', p_payload->>'uid', 'status', p_payload->>'status'); END IF; IF p_operation='create_subscription' THEN IF NOT (p_payload ?& ARRAY['uid','grant_uid','asset_uid','trigger','purpose','expires_at','idempotency_key','request_digest','status','created_by']) OR p_payload - ARRAY['uid','grant_uid','asset_uid','trigger','purpose','expires_at','idempotency_key','request_digest','status','current_version','created_by'] <> '{}'::jsonb OR jsonb_typeof(p_payload->'trigger')<>'object' THEN RAISE EXCEPTION 'trusted-delivery subscription payload is closed'; END IF; INSERT INTO public.trusted_delivery_subscriptions(uid,grant_uid,asset_uid,trigger,purpose,expires_at,idempotency_key,request_digest,status,current_version,created_by) VALUES((p_payload->>'uid')::uuid,(p_payload->>'grant_uid')::uuid,(p_payload->>'asset_uid')::uuid,p_payload->'trigger',p_payload->>'purpose',(p_payload->>'expires_at')::timestamptz,p_payload->>'idempotency_key',p_payload->>'request_digest',p_payload->>'status',COALESCE((p_payload->>'current_version')::int,1),(p_payload->>'created_by')::uuid); RETURN jsonb_build_object('uid',p_payload->>'uid','grant_uid',p_payload->>'grant_uid','asset_uid',p_payload->>'asset_uid','trigger',p_payload->'trigger','purpose',p_payload->>'purpose','expires_at',p_payload->>'expires_at','status',p_payload->>'status','current_version',COALESCE((p_payload->>'current_version')::int,1)); END IF; RAISE EXCEPTION 'trusted-delivery write operation is not implemented'; END $trusted_delivery_write$; REVOKE ALL ON FUNCTION public.trusted_delivery_write(text,jsonb) FROM PUBLIC; GRANT EXECUTE ON FUNCTION public.trusted_delivery_write(text,jsonb) TO dataops_app_runtime; """ ) for table in _TABLES: op.execute(f"ALTER TABLE public.{table} OWNER TO dataops_trusted_delivery_owner") op.execute( f"REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON TABLE public.{table} FROM dataops_app_runtime" ) op.execute(f"GRANT SELECT ON TABLE public.{table} TO dataops_app_runtime") op.execute(f"GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.{table} TO dataops_trusted_delivery_writer") op.execute("ALTER TABLE public.trusted_delivery_approval_facts OWNER TO dataops_trusted_delivery_owner") op.execute("REVOKE ALL ON TABLE public.trusted_delivery_approval_facts FROM PUBLIC, dataops_app_runtime") op.execute("GRANT SELECT ON TABLE public.trusted_delivery_approval_facts TO dataops_trusted_delivery_writer") op.execute("ALTER FUNCTION public.trusted_delivery_write(text,jsonb) OWNER TO dataops_trusted_delivery_writer") def downgrade() -> None: op.execute( """ DO $$ BEGIN IF EXISTS (SELECT 1 FROM public.trusted_delivery_approval_facts) THEN RAISE EXCEPTION 'downgrade requires approved trusted-delivery approval-fact archival migration'; END IF; END $$; REVOKE EXECUTE ON FUNCTION public.trusted_delivery_write(text,jsonb) FROM dataops_app_runtime; DROP FUNCTION public.trusted_delivery_write(text,jsonb); DROP TABLE public.trusted_delivery_approval_facts; """ )