db diff omits default privileges and function EXECUTE changes from declarative schemas

[aydogandagidir]

Describe the bug

supabase db diff does not emit changes to pg_default_acl or existing function EXECUTE privileges when those changes are declared in supabase/schemas/*.sql.

This leaves declarative schema state and replayable migration state different. It is especially risky during the Data API deny-by-default rollout because a project can declare revoked defaults while generated migrations silently preserve legacy auto-exposure.

Minimal reproduction

supabase/config.toml:

[db]
major_version = 17

[db.migrations]
schema_paths = ["./schemas/*.sql"]

Assume existing migration state grants legacy defaults and function access. Add this declarative file:

alter default privileges for role postgres in schema public
  revoke all on tables from public, anon, authenticated;

alter default privileges for role postgres in schema public
  revoke all on sequences from public, anon, authenticated;

alter default privileges for role postgres in schema public
  revoke execute on functions from public, anon, authenticated;

revoke execute on all functions in schema public
  from public, anon, authenticated;

grant execute on function public.allowed_helper() to authenticated;

Then run on Ubuntu:

supabase start
supabase db diff --use-migra -f harden_acl

Actual behavior

CLI v2.109.1 completes successfully but the generated migration contains none of:

• ALTER DEFAULT PRIVILEGES
• default table/sequence/function revokes
• existing function EXECUTE revokes

The output may contain unrelated policy normalization while silently omitting the ACL changes.

Also verified:

• v2.31.8 migra: same ACL omission
• v2.109.1 pg-delta: partial object ACL output, but no complete default/function ACL coverage
• pg-schema-diff: its CLI warning says grants may be omitted
• pgAdmin engine: exits without usable output in the same isolated Linux workflow

Expected behavior

Either:

1. generate a complete migration that reproduces pg_default_acl and per-function EXECUTE state, or
2. fail clearly when declarative ACL changes cannot be represented.

A successful command that silently omits security-relevant declarative changes is unsafe.

Environment

• Supabase CLI: 2.109.1 (also reproduced on 2.31.8)
• OS: GitHub Actions ubuntu-latest
• PostgreSQL: 17
• Diff target: local isolated Supabase stack
• Declarative schemas configured through [db.migrations].schema_paths

Suggested regression coverage

After applying the generated migration to a clean reset, assert:

select not has_table_privilege('anon', 'public.probe', 'select');
select not has_sequence_privilege('authenticated', 'public.probe_id_seq', 'usage');
select not has_function_privilege('anon', 'public.probe_fn()', 'execute');

Also compare pg_default_acl between the migrations shadow database and declarative target.

[7ttp]

thanks @aydogandagidir for reporting this! 💚
pg-delta fix is being worked on in:

• fix(pg-delta): stop dropping REVOKE ... FROM PUBLIC in privilege diffs pg-toolbelt#357.