supabase-postgres.git / summary / log / commit / refs
commit 4486cced0ead19f82b4cf7e95b6b02161ba228d1
Author: Utkarash Kumar Singh <utkarash2991@users.noreply.github.com>
Commit: GitHub <noreply@github.com>
Date: Thu Aug 27 20:00:25 2026 +0000
fix(cron): revoke TRIGGER on cron.job_run_details with CASCADE (#2399)
Follow-up to #2334. The grant on the cron tables is made WITH GRANT
OPTION, so postgres may have re-granted TRIGGER on cron.job_run_details
to another role. In that case a plain REVOKE fails with "dependent
privileges exist", which breaks the one-time migration do-block (and the
grant_pg_cron_access() re-run on a later CREATE EXTENSION).
Switch the TRIGGER revoke to REVOKE ... CASCADE in the migration,
grant_pg_cron_access() (migration + schema-{15,17,orioledb-17}), and
pg_cron/after-create.sql. Add a pg_regress case that reproduces the
delegated-grant state and asserts CASCADE clears TRIGGER from postgres
and the delegated role.
.../pg_cron/after-create.sql | 2 +-
...revoke_trigger_on_cron_tables_from_postgres.sql | 8 ++-
migrations/schema-15.sql | 2 +-
migrations/schema-17.sql | 2 +-
migrations/schema-orioledb-17.sql | 2 +-
nix/tests/expected/pg_cron_trigger_privileges.out | 45 +++++++++++++++++
nix/tests/sql/pg_cron_trigger_privileges.sql | 58 ++++++++++++++++++++++
7 files changed, 113 insertions(+), 6 deletions(-)
diff --git a/ansible/files/postgresql_extension_custom_scripts/pg_cron/after-create.sql b/ansible/files/postgresql_extension_custom_scripts/pg_cron/after-create.sql
index 4f188676..9370f915 100644
--- a/ansible/files/postgresql_extension_custom_scripts/pg_cron/after-create.sql
+++ b/ansible/files/postgresql_extension_custom_scripts/pg_cron/after-create.sql
@@ -11,4 +11,4 @@ alter default privileges for user supabase_admin in schema cron grant all
grant all privileges on all tables in schema cron to postgres with grant option;
revoke all on table cron.job from postgres;
grant select on table cron.job to postgres with grant option;
-revoke trigger on cron.job_run_details from postgres;
+revoke trigger on cron.job_run_details from postgres cascade;
diff --git a/migrations/db/migrations/20260730000000_revoke_trigger_on_cron_tables_from_postgres.sql b/migrations/db/migrations/20260730000000_revoke_trigger_on_cron_tables_from_postgres.sql
index 97dcb414..e4d7fb6c 100644
--- a/migrations/db/migrations/20260730000000_revoke_trigger_on_cron_tables_from_postgres.sql
+++ b/migrations/db/migrations/20260730000000_revoke_trigger_on_cron_tables_from_postgres.sql
@@ -5,11 +5,15 @@
-- create triggers on that table, so revoke TRIGGER there and stop grant_pg_cron_access()
-- from re-granting it on a later CREATE EXTENSION (which re-runs `grant all`). cron.job is
-- already SELECT-only for postgres.
+--
+-- The grant is made WITH GRANT OPTION, so postgres may have re-granted TRIGGER onward to
+-- another role; a plain REVOKE then fails with "dependent privileges exist". REVOKE ...
+-- CASCADE handles that case (and removes the re-granted privilege too).
do $$
begin
if exists (select from pg_extension where extname = 'pg_cron') then
- revoke trigger on cron.job_run_details from postgres;
+ revoke trigger on cron.job_run_details from postgres cascade;
end if;
end $$;
@@ -41,7 +45,7 @@ BEGIN
grant all privileges on all tables in schema cron to postgres with grant option;
revoke all on table cron.job from postgres;
grant select on table cron.job to postgres with grant option;
- revoke trigger on cron.job_run_details from postgres;
+ revoke trigger on cron.job_run_details from postgres cascade;
END IF;
END;
$$;
diff --git a/migrations/schema-15.sql b/migrations/schema-15.sql
index a8ee3918..322f4740 100644
--- a/migrations/schema-15.sql
+++ b/migrations/schema-15.sql
@@ -195,7 +195,7 @@ BEGIN
grant all privileges on all tables in schema cron to postgres with grant option;
revoke all on table cron.job from postgres;
grant select on table cron.job to postgres with grant option;
- revoke trigger on cron.job_run_details from postgres;
+ revoke trigger on cron.job_run_details from postgres cascade;
END IF;
END;
$$;
diff --git a/migrations/schema-17.sql b/migrations/schema-17.sql
index 80f552d7..2cbfa1b1 100644
--- a/migrations/schema-17.sql
+++ b/migrations/schema-17.sql
@@ -196,7 +196,7 @@ BEGIN
grant all privileges on all tables in schema cron to postgres with grant option;
revoke all on table cron.job from postgres;
grant select on table cron.job to postgres with grant option;
- revoke trigger on cron.job_run_details from postgres;
+ revoke trigger on cron.job_run_details from postgres cascade;
END IF;
END;
$$;
diff --git a/migrations/schema-orioledb-17.sql b/migrations/schema-orioledb-17.sql
index 9b649dc1..c1617fdc 100644
--- a/migrations/schema-orioledb-17.sql
+++ b/migrations/schema-orioledb-17.sql
@@ -210,7 +210,7 @@ BEGIN
grant all privileges on all tables in schema cron to postgres with grant option;
revoke all on table cron.job from postgres;
grant select on table cron.job to postgres with grant option;
- revoke trigger on cron.job_run_details from postgres;
+ revoke trigger on cron.job_run_details from postgres cascade;
END IF;
END;
$$;
diff --git a/nix/tests/expected/pg_cron_trigger_privileges.out b/nix/tests/expected/pg_cron_trigger_privileges.out
index f1f77a0b..5f9a3cc4 100644
--- a/nix/tests/expected/pg_cron_trigger_privileges.out
+++ b/nix/tests/expected/pg_cron_trigger_privileges.out
@@ -25,3 +25,48 @@ begin
end
$$;
rollback;
+-- A delegated (dependent) TRIGGER grant must not break the revoke. postgres holds TRIGGER
+-- WITH GRANT OPTION, so it can re-grant TRIGGER on cron.job_run_details to another role. A
+-- plain REVOKE then fails with a "dependent privileges exist" (class 2B) error, so the
+-- revoke uses CASCADE. This reproduces that state and asserts CASCADE clears TRIGGER from
+-- postgres and the delegated role. Rolled back; silent on success.
+begin;
+set local client_min_messages = warning;
+create extension if not exists pg_cron;
+grant trigger on cron.job_run_details to postgres with grant option;
+create role cron_trigger_dep_test;
+set local role postgres;
+grant trigger on cron.job_run_details to cron_trigger_dep_test;
+reset role;
+do $$
+begin
+ begin
+ revoke trigger on cron.job_run_details from postgres;
+ raise exception 'plain REVOKE unexpectedly succeeded despite a dependent grant';
+ exception when others then
+ if sqlstate not like '2B%' then raise; end if;
+ end;
+end
+$$;
+revoke trigger on cron.job_run_details from postgres cascade;
+do $$
+begin
+ if has_table_privilege('postgres', 'cron.job_run_details', 'TRIGGER') then
+ raise exception 'postgres still has TRIGGER after CASCADE revoke';
+ end if;
+ if has_table_privilege('cron_trigger_dep_test', 'cron.job_run_details', 'TRIGGER') then
+ raise exception 'delegated role still has TRIGGER after CASCADE revoke';
+ end if;
+end
+$$;
+rollback;
+-- The shipped grant_pg_cron_access() must revoke TRIGGER with CASCADE, so a re-granted
+-- privilege cannot block it on a future CREATE EXTENSION.
+do $$
+begin
+ if pg_get_functiondef('extensions.grant_pg_cron_access()'::regprocedure)
+ not ilike '%job_run_details from postgres cascade%' then
+ raise exception 'grant_pg_cron_access() no longer revokes TRIGGER with CASCADE';
+ end if;
+end
+$$;
diff --git a/nix/tests/sql/pg_cron_trigger_privileges.sql b/nix/tests/sql/pg_cron_trigger_privileges.sql
index 57fef02a..e86e8b95 100644
--- a/nix/tests/sql/pg_cron_trigger_privileges.sql
+++ b/nix/tests/sql/pg_cron_trigger_privileges.sql
@@ -29,3 +29,61 @@ end
$$;
rollback;
+
+-- A delegated (dependent) TRIGGER grant must not break the revoke. postgres holds TRIGGER
+-- WITH GRANT OPTION, so it can re-grant TRIGGER on cron.job_run_details to another role. A
+-- plain REVOKE then fails with a "dependent privileges exist" (class 2B) error, so the
+-- revoke uses CASCADE. This reproduces that state and asserts CASCADE clears TRIGGER from
+-- postgres and the delegated role. Rolled back; silent on success.
+begin;
+
+set local client_min_messages = warning;
+
+create extension if not exists pg_cron;
+
+grant trigger on cron.job_run_details to postgres with grant option;
+
+create role cron_trigger_dep_test;
+
+set local role postgres;
+
+grant trigger on cron.job_run_details to cron_trigger_dep_test;
+
+reset role;
+
+do $$
+begin
+ begin
+ revoke trigger on cron.job_run_details from postgres;
+ raise exception 'plain REVOKE unexpectedly succeeded despite a dependent grant';
+ exception when others then
+ if sqlstate not like '2B%' then raise; end if;
+ end;
+end
+$$;
+
+revoke trigger on cron.job_run_details from postgres cascade;
+
+do $$
+begin
+ if has_table_privilege('postgres', 'cron.job_run_details', 'TRIGGER') then
+ raise exception 'postgres still has TRIGGER after CASCADE revoke';
+ end if;
+ if has_table_privilege('cron_trigger_dep_test', 'cron.job_run_details', 'TRIGGER') then
+ raise exception 'delegated role still has TRIGGER after CASCADE revoke';
+ end if;
+end
+$$;
+
+rollback;
+
+-- The shipped grant_pg_cron_access() must revoke TRIGGER with CASCADE, so a re-granted
+-- privilege cannot block it on a future CREATE EXTENSION.
+do $$
+begin
+ if pg_get_functiondef('extensions.grant_pg_cron_access()'::regprocedure)
+ not ilike '%job_run_details from postgres cascade%' then
+ raise exception 'grant_pg_cron_access() no longer revokes TRIGGER with CASCADE';
+ end if;
+end
+$$;