DataOps-platform/migrations/versions/20260818_552_metering_definer_owner_fence.py

"""Transfer every WP12 SECURITY DEFINER function to the fixed NOLOGIN owner."""

from alembic import op

revision = "20260818_552"
down_revision = "20260818_551"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # A real restricted migrator owns the functions it created at 551 and is a
    # member (not the login identity) of the fixed NOLOGIN role.  Keep that
    # identity while transferring ownership: SET ROLE would lose ownership of
    # the temporary functions before they are fenced.
    op.execute(r'''
    DO $owner$
    DECLARE fn record;
    BEGIN
      -- Includes the 551 metering_showback_allocation_total_guard trigger
      -- function and every renamed legacy gateway still present in pg_proc.
      IF NOT EXISTS(SELECT 1 FROM pg_roles WHERE rolname='dataops_tenant_foundation_owner' AND rolcanlogin=false AND rolsuper=false AND rolcreaterole=false) THEN
        RAISE EXCEPTION 'WP12 fixed NOLOGIN definer owner must be provisioned by role-init';
      END IF;
      IF NOT has_schema_privilege('dataops_tenant_foundation_owner','public','CREATE') THEN
        RAISE EXCEPTION 'WP12 fixed NOLOGIN definer owner requires public CREATE from role-init';
      END IF;
      FOR fn IN
        SELECT p.proname,pg_get_function_identity_arguments(p.oid) AS args
        FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
        WHERE n.nspname='public' AND p.prosecdef AND p.proname LIKE 'metering_showback_%'
      LOOP
        EXECUTE format('ALTER FUNCTION public.%I(%s) OWNER TO dataops_tenant_foundation_owner',fn.proname,fn.args);
        EXECUTE format('REVOKE ALL ON FUNCTION public.%I(%s) FROM PUBLIC,dataops_app,dataops_app_runtime,dataops_bi_ai_catalog_control',fn.proname,fn.args);
      END LOOP;
    END; $owner$;
    GRANT EXECUTE ON FUNCTION public.metering_showback_issue_claim(text,text,jsonb),public.metering_showback_control_write(text,text,jsonb) TO dataops_bi_ai_catalog_control;
    GRANT EXECUTE ON FUNCTION public.metering_showback_runtime_write(text,jsonb),public.metering_showback_runtime_read(text,jsonb),public.metering_showback_rollup(jsonb),public.metering_showback_allocation_replay(jsonb) TO dataops_app_runtime;
    ''')


def downgrade() -> None:
    # Ownership remains fixed on downgrade: restoring a transient migrator as a
    # SECURITY DEFINER owner would reintroduce the privilege-retention defect.
    return None