Janady13/missionmatch / db/migrations/0092_mirror_function_grants.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0092_mirror_function_grants.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0092_mirror_function_grants.sql
Original SHA-256	10d9da63ea01737502a4752bf8e43091ea275fe4e36d48640d116f11da00a6f6
Git blob SHA	f24ac086c053dabc01c59e9c547431f3203a0cf9
Reading-copy SHA-256	10d9da63ea01737502a4752bf8e43091ea275fe4e36d48640d116f11da00a6f6

Original source ↗

Recovered source transcript

Download complete source text ↓82 lines · 4.4 KB

-- +goose Up
-- THE STAGING PARITY HELPER MIRRORED TABLES AND COLUMNS AND NOT FUNCTIONS, so every SECURITY DEFINER function
-- this schema has ever granted to missionmatch_app was unreachable from missionmatch_staging_app.
--
-- This is migration 0085's lesson recurring in a new place. That one was about POLICIES: a policy naming only
-- the production role did not apply to the staging role, and with no applicable policy row level security
-- denied everything. mm_mirror_staging_role_privileges was written to close that class, and it closed it for
-- tables and columns only. The first function grant to arrive afterwards, the public careers seam in
-- migration 0091, was silently missing on staging: the tables were there, the policies were right, the
-- function existed, and the app role simply could not call it.
--
-- IT FAILED IN THE MOST EXPENSIVE WAY, which is why this migration exists rather than a one line GRANT. The
-- public careers page returned "that job posting is not available" for a posting that was genuinely
-- published, so the symptom pointed at the data and not at a permission. A walkthrough found it; a code
-- review would not have.
--
-- The fix is to the HELPER, not to the one function. Every future SECURITY DEFINER function is now mirrored
-- automatically, so this cannot be rediscovered a third time.

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION mm_mirror_staging_role_privileges() RETURNS void AS $$
DECLARE
  staging_app CONSTANT text := 'missionmatch_staging_app';
  prod_app    CONSTANT text := 'missionmatch_app';
  g record;
BEGIN
  -- Roles are CLUSTER wide while privileges are per database, so without this guard the block would grant the
  -- staging role privileges on PRODUCTION tables. Inert today because the staging roles cannot connect to the
  -- production database, and defence in depth is worth more than relying on that.
  IF current_database() <> 'missionmatch_staging' THEN RETURN; END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = staging_app) THEN RETURN; END IF;

  FOR g IN
    SELECT a.table_name, a.privilege_type FROM information_schema.role_table_grants a
    WHERE a.grantee = prod_app AND a.table_schema = 'public'
      AND NOT EXISTS (SELECT 1 FROM information_schema.role_table_grants b
                      WHERE b.grantee = staging_app AND b.table_schema='public'
                        AND b.table_name = a.table_name AND b.privilege_type = a.privilege_type)
  LOOP
    EXECUTE format('GRANT %s ON public.%I TO %I', g.privilege_type, g.table_name, staging_app);
  END LOOP;

  FOR g IN
    SELECT a.table_name, a.column_name, a.privilege_type FROM information_schema.column_privileges a
    WHERE a.grantee = prod_app AND a.table_schema = 'public'
      AND NOT EXISTS (SELECT 1 FROM information_schema.column_privileges b
                      WHERE b.grantee = staging_app AND b.table_schema='public'
                        AND b.table_name = a.table_name AND b.column_name = a.column_name
                        AND b.privilege_type = a.privilege_type)
  LOOP
    EXECUTE format('GRANT %s (%I) ON public.%I TO %I',
                   g.privilege_type, g.column_name, g.table_name, staging_app);
  END LOOP;

  -- FUNCTIONS, the half that was missing. Identified by their full argument signature rather than by name,
  -- because a name alone is ambiguous under overloading and granting the wrong overload is worse than
  -- granting nothing: it looks like it worked.
  FOR g IN
    SELECT p.oid::regprocedure AS sig
    FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = 'public'
      AND has_function_privilege(prod_app, p.oid, 'EXECUTE')
      AND NOT has_function_privilege(staging_app, p.oid, 'EXECUTE')
  LOOP
    EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO %I', g.sig, staging_app);
  END LOOP;

  -- CONTAINMENT RE ASSERTED rather than trusted. Nothing above touches a database privilege; this proves it.
  IF has_database_privilege(staging_app, 'missionmatch', 'CONNECT') THEN
    RAISE EXCEPTION 'staging parity: % gained CONNECT on the production database', staging_app;
  END IF;
END $$ LANGUAGE plpgsql;
-- +goose StatementEnd

SELECT mm_mirror_staging_role_privileges();

-- +goose Down
-- The helper is left in its extended form. Reverting it would reintroduce the gap this migration exists to
-- close, and a down migration whose effect is to restore a defect is not a rollback, it is a regression.
SELECT 1;