Janady13/missionmatch / db/migrations/0085_staging_role_privilege_parity.sql

1-- +goose Up 2-- GATE A REGRESSION FIX. Seven integration tests failed and four of them traced to one systemic cause that 3-- had never been named: THE STAGING DATABASE'S APP ROLE DOES NOT RECEIVE WHAT THE MIGRATIONS GRANT. 4-- 5-- 51 of the 84 migrations name missionmatch_app or missionmatch_migrator literally in GRANT statements and in 6-- RLS policy role lists. ZERO of them mention missionmatch_staging_app or missionmatch_staging_migrator, which 7-- are the roles the staging environment actually connects as, established deliberately for containment. So 8-- every grant and every role scoped policy a migration adds lands on the production named role and the staging 9-- app role silently misses it. 10-- 11-- MEASURED, rather than assumed, before writing a line of this: 12-- table level grants missionmatch_app 195, missionmatch_staging_app 195. Already mirrored. 13-- column level grants missionmatch_app 2055, missionmatch_staging_app 2048. SEVEN MISSING, and all seven 14-- are the organizations UPDATE columns that migration 0057 granted for the time zone 15-- feature. Someone mirrored the grants by hand once and 0057 landed afterwards. 16-- RLS policies 57 total, 6 name missionmatch_app, 0 name missionmatch_staging_app. 17-- 18-- WHY THE POLICY HALF IS WORSE THAN THE GRANT HALF. An RLS policy with a TO clause applies only to the named 19-- roles and their members. With no applicable policy, RLS DENIES EVERYTHING, so the staging app role was 20-- refused on outcome_events even though it holds INSERT and SELECT. The error reads "new row violates row 21-- level security policy", which sounds like a policy bug and is actually a policy that was never addressed to 22-- the caller. That is what broke TestBackfillConvertsSlateDecisionsAndDedups. 23-- 24-- WHY NOT ROLE MEMBERSHIP, WHICH WOULD BE ONE LINE. GRANT missionmatch_app TO missionmatch_staging_app would 25-- fix both halves at once, and the PostgreSQL documentation confirms normal role membership and inheritance 26-- rules apply to a policy's TO clause. IT IS REFUSED HERE BECAUSE IT WOULD BREAK CONTAINMENT. Membership 27-- conveys ALL privileges of the granted role, INCLUDING database level CONNECT, and missionmatch_app has 28-- CONNECT on the production database while the staging roles deliberately have none. Measured: 29-- missionmatch app CONNECT t, staging_app CONNECT f 30-- missionmatch_staging app CONNECT t, staging_app CONNECT t 31-- That f is the containment boundary the D6 work established, and it is enforced at connection time, which is 32-- the strongest place to enforce it. Trading it for a shorter migration would be a bad trade. 33-- 34-- SO THIS MIRRORS PRIVILEGES WITHOUT MIRRORING IDENTITY. It copies table privileges, column privileges, and 35-- policy applicability, and it never touches a database level privilege, so CONNECT stays exactly as it is. 36-- 37-- IT IS WRITTEN GENERICALLY RATHER THAN AS SEVEN GRANTS AND SIX ALTERS, because a hand written patch fixes 38-- today and drifts again on the next migration that adds a grant. Re running this block after any future 39-- migration re establishes parity. 40 41-- +goose StatementBegin 42DO $$ 43DECLARE 44 staging_app CONSTANT text := 'missionmatch_staging_app'; 45 prod_app CONSTANT text := 'missionmatch_app'; 46 g record; 47 pol record; 48 n_tab int := 0; 49 n_col int := 0; 50 n_pol int := 0; 51BEGIN 52 -- ONLY IN THE STAGING DATABASE. Roles are CLUSTER wide in PostgreSQL while privileges are per database, so 53 -- without this guard the block would grant the staging role privileges on PRODUCTION tables. Those grants 54 -- would be inert today because the staging roles cannot connect to the production database, and relying on 55 -- one boundary when a second is free is how the first one eventually gets removed by someone in a hurry. 56 IF current_database() <> 'missionmatch_staging' THEN 57 RAISE NOTICE 'staging role parity: skipped, this is %, not missionmatch_staging', current_database(); 58 RETURN; 59 END IF; 60 IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = staging_app) THEN 61 RAISE NOTICE 'staging role parity: skipped, role % does not exist', staging_app; 62 RETURN; 63 END IF; 64 65 -- 1. TABLE LEVEL PRIVILEGES the production app role holds and the staging app role does not. 66 FOR g IN 67 SELECT a.table_name, a.privilege_type 68 FROM information_schema.role_table_grants a 69 WHERE a.grantee = prod_app AND a.table_schema = 'public' 70 AND NOT EXISTS ( 71 SELECT 1 FROM information_schema.role_table_grants b 72 WHERE b.grantee = staging_app AND b.table_schema = 'public' 73 AND b.table_name = a.table_name AND b.privilege_type = a.privilege_type) 74 LOOP 75 EXECUTE format('GRANT %s ON public.%I TO %I', g.privilege_type, g.table_name, staging_app); 76 n_tab := n_tab + 1; 77 END LOOP; 78 79 -- 2. COLUMN LEVEL PRIVILEGES, which is where the seven organizations UPDATE grants live. These do NOT appear 80 -- in role_table_grants, which is why the first diagnosis of this failure read the app role as SELECT only 81 -- and called it a grant drift. A column privilege is invisible to the table privilege view. 82 FOR g IN 83 SELECT a.table_name, a.column_name, a.privilege_type 84 FROM information_schema.column_privileges a 85 WHERE a.grantee = prod_app AND a.table_schema = 'public' 86 AND NOT EXISTS ( 87 SELECT 1 FROM information_schema.column_privileges b 88 WHERE b.grantee = staging_app AND b.table_schema = 'public' 89 AND b.table_name = a.table_name AND b.column_name = a.column_name 90 AND b.privilege_type = a.privilege_type) 91 LOOP 92 EXECUTE format('GRANT %s (%I) ON public.%I TO %I', 93 g.privilege_type, g.column_name, g.table_name, staging_app); 94 n_col := n_col + 1; 95 END LOOP; 96 97 -- 3. RLS POLICY APPLICABILITY. The staging role is ADDED to the existing role list, never substituted for it, 98 -- so production behaviour is untouched. polroles is read from pg_policy rather than pg_policies so the 99 -- existing roles are rebuilt with quote_ident and a role name needing quoting cannot corrupt the statement. 100 FOR pol IN 101 SELECT p.polname, c.relname, 102 (SELECT string_agg(quote_ident(r.rolname), ', ' ORDER BY r.rolname) 103 FROM pg_roles r WHERE r.oid = ANY (p.polroles)) AS role_list 104 FROM pg_policy p 105 JOIN pg_class c ON c.oid = p.polrelid 106 JOIN pg_namespace n ON n.oid = c.relnamespace 107 WHERE n.nspname = 'public' 108 AND EXISTS (SELECT 1 FROM pg_roles r WHERE r.oid = ANY (p.polroles) AND r.rolname = prod_app) 109 AND NOT EXISTS (SELECT 1 FROM pg_roles r WHERE r.oid = ANY (p.polroles) AND r.rolname = staging_app) 110 LOOP 111 EXECUTE format('ALTER POLICY %I ON public.%I TO %s, %I', 112 pol.polname, pol.relname, pol.role_list, staging_app); 113 n_pol := n_pol + 1; 114 END LOOP; 115 116 RAISE NOTICE 'staging role parity: % table grants, % column grants, % policies updated', n_tab, n_col, n_pol; 117 118 -- THE ASSERTION, because a DO block that granted nothing and a DO block that had nothing to grant look 119 -- identical in a migration log. After this runs, parity must be EXACT in both directions that matter. 120 IF EXISTS ( 121 SELECT 1 FROM information_schema.column_privileges a 122 WHERE a.grantee = prod_app AND a.table_schema = 'public' 123 AND NOT EXISTS ( 124 SELECT 1 FROM information_schema.column_privileges b 125 WHERE b.grantee = staging_app AND b.table_schema = 'public' 126 AND b.table_name = a.table_name AND b.column_name = a.column_name 127 AND b.privilege_type = a.privilege_type)) 128 THEN 129 RAISE EXCEPTION 'staging role parity FAILED: column privileges still differ after mirroring'; 130 END IF; 131 IF EXISTS ( 132 SELECT 1 FROM pg_policy p 133 JOIN pg_class c ON c.oid = p.polrelid 134 JOIN pg_namespace n ON n.oid = c.relnamespace 135 WHERE n.nspname = 'public' 136 AND EXISTS (SELECT 1 FROM pg_roles r WHERE r.oid = ANY (p.polroles) AND r.rolname = prod_app) 137 AND NOT EXISTS (SELECT 1 FROM pg_roles r WHERE r.oid = ANY (p.polroles) AND r.rolname = staging_app)) 138 THEN 139 RAISE EXCEPTION 'staging role parity FAILED: a policy naming % still does not name %', prod_app, staging_app; 140 END IF; 141 142 -- CONTAINMENT IS RE ASSERTED RATHER THAN TRUSTED. Nothing above touches a database privilege, and this 143 -- proves it rather than claiming it. If a future edit ever reaches for role membership, this fails. 144 IF has_database_privilege(staging_app, 'missionmatch', 'CONNECT') THEN 145 RAISE EXCEPTION 'staging role parity FAILED: % gained CONNECT on the production database', staging_app; 146 END IF; 147END $$; 148-- +goose StatementEnd 149 150-- +goose Down 151-- +goose StatementBegin 152-- NOT REVERSED, DELIBERATELY, and this is a considered choice rather than laziness. 153-- 154-- Removing the mirrored privileges would return the staging database to the state where its app role is 155-- refused by RLS on four tables and cannot write the organizations columns the product's own time zone 156-- endpoint updates. That is a broken environment, not a previous version of a schema. A down migration whose 157-- effect is to reintroduce a Gate A failure is not a rollback, it is a regression with a version number. 158-- 159-- Nothing here changes schema shape, so there is no structure to unwind: only privileges and policy role 160-- lists were touched, both additively. 161SELECT 1; 162-- +goose StatementEnd 163