AI & Agent Dev Bug Sandbox logo
AI & Agent Dev Bug Sandbox
Back to Radar

[Studio] Default Privileges State Misses Grants Made TO PUBLIC

Studio's default-privileges state query uses an inner join to pg_roles, excluding PUBLIC grants (grantee OID 0) from aclexplode, causing grants TO PUBLIC to be invisible and grant_count to be 0.

mediumConfidence 95%SupabaseAffected Vmaster

Origin Analysis

aclexplode(d.defaclacl) returns grantee = 0 for PUBLIC, but pg_roles has no row with oid = 0. The inner join in the EXISTS subquery drops these rows, so default privileges granted directly to PUBLIC do not count toward anon/authenticated/service_role.
1. Execute `alter default privileges for role postgres in schema public grant select on tables to public;` 2. Run the state query from `getDefaultPrivilegesStateSql({ schema: 'public' })` in `packages/pg-meta/src/sql/studio/database/privileges.ts`. 3. Observe `grant_count` is 0 despite the effective grant to anon/authenticated/service_role via PUBLIC.

Fixing Code Block

--- a/packages/pg-meta/src/sql/studio/database/privileges.ts +++ b/packages/pg-meta/src/sql/studio/database/privileges.ts @@ -... @@ and exists ( select 1 from aclexplode(d.defaclacl) acl - join pg_roles gr on gr.oid = acl.grantee - where gr.rolname in ('anon', 'authenticated', 'service_role') + left join pg_roles gr on gr.oid = acl.grantee + where (gr.rolname in ('anon', 'authenticated', 'service_role') or acl.grantee = 0) )
Switching to LEFT JOIN and adding `or acl.grantee = 0` preserves rows where grantee is PUBLIC (OID 0), while still filtering for the three Data API roles for regular grants. This matches PostgreSQL ACL semantics.

Edge Case Audit

The fix only changes the EXISTS predicate and does not introduce extra rows for non-matching roles because `gr.rolname` is null for non-public non-data roles and `acl.grantee != 0` filters them out. However, cached Studio state may still show stale values until refresh after upgrade. In concurrent or mixed grant environments, ensure object type and privilege filters still apply to PUBLIC grants. Rollback by reverting to the original inner join if unexpected PUBLIC grants appear in the UI.

Ecosystem Topology