Hosted Supabase: supported way to remove unnecessary TRUNCATE privileges from managed Storage tables and default privileges

[yasumatsucoating-hash]

Title:
Hosted Supabase: supported way to remove unnecessary TRUNCATE privileges from managed Storage tables and default privileges

Hi,

We are performing a security hardening review on a hosted Supabase project and would like guidance on the supported way to handle unnecessary TRUNCATE privileges.

We found two separate issues.

1. Existing table privileges
2. Creator-specific default privileges

Existing Storage-managed tables with TRUNCATE granted to both anon and authenticated:

• storage.buckets
• storage.objects
• storage.buckets_analytics

These tables are owned by supabase_storage_admin.

Across the project, anon / authenticated have TRUNCATE on 50 tables total:

• 47 tables in public
• 3 tables in storage

We also found TRUNCATE in default privileges for:

• postgres / public
• postgres / storage
• supabase_admin / public
• supabase_admin / graphql
• supabase_admin / graphql_public

Important:
We did NOT find a supabase_storage_admin / storage default TRUNCATE grant.

What we verified:

• PostgREST does not expose a normal direct TRUNCATE table operation.
• We found no exposed RPC or SECURITY DEFINER function that performs TRUNCATE or arbitrary SQL.
• No effective destructive path was found from normal anon/authenticated sessions.
• anon/authenticated are not LOGIN roles and do not inherit higher privileged roles.
• TRUNCATE is outside normal RLS row filtering, so we still want to remove unnecessary privileges as defense in depth.

Local validation:

• removing only TRUNCATE preserved SELECT / INSERT / UPDATE / DELETE
• RLS remained unchanged
• rollback was validated
• future-table default privilege cases were tested
• Storage CRUD worked in a local file-backend environment

However, the local Storage test is NOT proven fully equivalent to hosted Supabase Storage or S3-backed behavior.

Hosted limitation:

• normal postgres cannot revoke some Storage-owner-derived grants
• default privileges owned by Supabase-managed roles such as supabase_admin cannot be altered by our normal project role
• we do not want to change ownership or use unsupported privilege escalation

Questions:

1. Is it supported on hosted Supabase to revoke TRUNCATE from anon / authenticated on:

   • storage.buckets
   • storage.objects
   • storage.buckets_analytics

2. If yes, what is the supported execution path or role?

3. Could this interfere with Storage runtime, future Storage migrations, hosted maintenance, or S3-backed behavior?

4. If customers should not modify these managed Storage grants, should we leave them unchanged and harden only application-owned public tables?

5. Is there a supported way to remove TRUNCATE from the creator-specific default privileges listed above?

6. Is there a supported way to prevent future application-owned tables from receiving TRUNCATE while leaving Supabase-managed defaults untouched?

Our goal is to:

• harden application-owned objects,
• avoid unsupported changes to managed schemas,
• preserve Storage functionality,
• and keep the change auditable in migration/security history.

We have also opened Support ticket:
SU-498042

Thanks.

[H1manshu01]

@yasumatsucoating-hash , Your analysis looks right to me, so mostly I'll just confirm the mechanics and flag the one part that isn't really a community-answerable thing.

First, where these grants even come from: they're not deliberate TRUNCATE grants, they're fallout from the blanket grant all the project bootstrap does for convenience. It runs something like grant all privileges on all tables in schema public to anon, authenticated, service_role plus the matching alter default privileges ... grant all on tables. all on a table includes TRUNCATE, TRIGGER and REFERENCES, so those three tag along everywhere. The storage.* ones are the same thing, just applied by the Storage service's own migrations (which run as supabase_storage_admin).

And yeah, you already worked out the important bit: it's not reachable. anon/authenticated are NOLOGIN, you only get to them via authenticator doing a SET ROLE through PostgREST/Storage, and PostgREST only ever turns HTTP verbs into SELECT/INSERT/UPDATE/DELETE. Nothing emits a TRUNCATE. So stripping it is fine as defense-in-depth, it's just not closing an actual hole.

On your actual questions:

1 & 2 : whether you can revoke it off the storage tables comes down to one thing: is your postgres a member of supabase_storage_admin? To revoke you need to own the object, be a member of the owner, or be superuser, and on hosted projects postgres isn't superuser. Check with:

select pg_has_role('postgres', 'supabase_storage_admin', 'member');

If that's true:

set role supabase_storage_admin;
revoke truncate on storage.objects, storage.buckets, storage.buckets_analytics from anon, authenticated;
reset role;

If it's false, leave it — don't go changing owners or escalating, that's what the support ticket is for.

3. revoking only TRUNCATE off anon/authenticated won't break anything at runtime. Storage connects as supabase_storage_admin (the owner, which keeps everything implicitly anyway), never as anon/authenticated, and no storage op truncates. The catch isn't breakage, it's that it probably won't stick — storage upgrades re-run their migrations and re-apply these grants, so a future release can quietly put TRUNCATE back.

4. honestly, for the managed storage.* tables I'd just leave them. It's unreachable, and it'll get reverted on the next storage upgrade anyway, so you'd be maintaining a revoke that fights the platform for no real gain. I'd document why it's safe (unreachable from the API, RLS on storage.objects controls the real access) and spend the effort on your own public tables instead. If you need that as an official statement for the audit, that's a support-ticket answer, not something I can promise for you.

5. you can't touch the supabase_admin default privileges, and shouldn't. Default privileges are tied to whoever created them; only that role or a superuser can alter them. You're postgres, so you can change the postgres-owned ones, but the supabase_admin / graphql ones aren't yours to change by design.

6. for your own future tables, just change your role's defaults:

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

Two gotchas: it only applies to tables created after you run it, and only ones created by postgres — anything created by another role follows that role's defaults. Existing tables need a one-off revoke truncate on all tables in schema public from anon, authenticated. And if you actually want least privilege, I'd revoke the whole all default and grant back just select/insert/update/delete, otherwise TRIGGER and REFERENCES are still coming through the exact same way TRUNCATE was — it's just a bigger departure from the defaults, so your call.

harden your own public objects (you own them, it sticks, totally fine), and leave the managed storage + supabase_admin stuff alone. Let the ticket give you the official line for your compliance record.

[BryceEWatson]

@H1manshu01's steps for your public tables are right. Two things change the Storage side, though.

First, Supabase Storage is already removing these grants. A migration merged on Sept 16 revokes TRUNCATE, REFERENCES and TRIGGER from anon and authenticated on exactly storage.objects, storage.buckets and storage.buckets_analytics (supabase/storage#1395, first released in v1.78.0). So a Storage upgrade takes TRUNCATE away rather than putting it back. If your project still has the grants, that PR is worth pointing to in SU-498042.

Second, the set role supabase_storage_admin route shouldn't be open on a current project. In April 2025 Supabase revoked postgres's membership in supabase_storage_admin (migration), and its notice at the time (discussion 34270) lists revoking privileges on storage tables from API roles as something projects can no longer do. So pg_has_role should come back false, and leaving those three tables to Storage and support is the route Supabase's own notice points to.

Two small additions for your audit trail:

• Before the one-off revoke ... on all tables in schema public, check that postgres owns them all: select tablename, tableowner from pg_tables where schemaname = 'public';. A table owned by another role keeps its grant.
• The postgres / storage default only affects tables postgres creates in storage, which it can no longer do. If you want it gone for a clean audit, alter default privileges for role postgres in schema storage revoke truncate on tables from anon, authenticated; should work.

To confirm the end state:

select n.nspname, c.relname
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('r', 'p')
  and n.nspname not in ('pg_catalog', 'information_schema')
  and (has_table_privilege('anon', c.oid, 'TRUNCATE')
    or has_table_privilege('authenticated', c.oid, 'TRUNCATE'));

Anything that still shows up after that is worth adding to SU-498042.

[H1manshu01]

Thanks @BryceEWatson the #1395 pointer is the key piece here, and the verification query is exactly what this thread needed. Two corrections and one caveat before @yasumatsucoating-hash takes this to SU-498042, so the ticket cites the right facts:

1. The release version looks off. Add “Supabase in 6 minutes” video link #1395 merged Sept 16, and the storage releases are already at v1.79.10 by Sept 21 (climbing to v1.80.0). v1.78.0 predates the merge, so the fix shipped in a v1.79.x release, not v1.78.0. Safest to cite the PR / migration 0073-revoke-grants-to-unused-operations.sql directly and let support map it to the exact tag.

2. The April 2025 claim should be verified, not asserted. The April 21, 2025 changelog ("Restricting Access on Auth, Storage, and Realtime Schemas") restricts DDL and privilege operations on those schemas it doesn't mention postgres role membership in supabase_storage_admin. The conclusion may well be right, but better to confirm empirically than cite that changelog as the cause:

select pg_has_role('postgres', 'supabase_storage_admin', 'member') as is_member;
-- or inspect directly:
select m.* from pg_auth_members m
  join pg_roles r on r.oid = m.roleid
 where r.rolname = 'supabase_storage_admin';

If that returns false, the set role approach is out and the grants have to go through Supabase support anyway.

3. The migration doesn't cover every storage table. I pulled the merged 0073 SQL to check. It revokes TRUNCATE/REFERENCES/TRIGGER from anon/authenticated on exactly storage.objects, storage.buckets, and storage.buckets_analytics, revokes ALL (then re-grants SELECT) on storage.buckets_vectors and storage.vector_indexes, and sets a default-privilege rule for future storage tables. Notably storage.prefixes is not named and since the default-privilege change only affects tables created after it runs, a pre-existing storage.prefixes can still carry TRUNCATE after the upgrade. Your verification query catches this, so it's worth running post-upgrade rather than assuming Add “Supabase in 6 minutes” video link #1395 clears the whole schema.

[BryceEWatson]

Thanks for checking these. I went back to the sources on all three:

1. I had the version wrong, but in the other direction. fix: revoke grants to unused table operations storage#1395 merged on Sept 16 at 21:05 UTC and shipped about a minute later in v1.77.7, whose release notes list it. Every release since includes it too. That's when the code shipped, though, not when it reaches a given project: the audit in the original post still showed TRUNCATE on exactly the three tables 0073 strips it from, so it may not have reached that project yet.
2. That point comes from the migration itself, not the changelog. Line 2 of the migration I linked (20250421084701_revoke_admin_roles_from_postgres.sql) is revoke supabase_storage_admin from postgres;, and the PR that added it (supabase/postgres#994) links that changelog as its context. Your pg_has_role query is a good way to confirm it on the project itself.
3. storage.prefixes was dropped by Storage's migration 0052 in January (0052), and Storage migrations run in order, so a project that has 0073 no longer has that table. That also fits the original post, whose audit found TRUNCATE on exactly objects, buckets and buckets_analytics. Running the verification query after the upgrade is still the right way to close it out.

@yasumatsucoating-hash, for SU-498042 I'd cite supabase/storage#1395 (migration 0073, first released in v1.77.7), ask when it reaches your project, and include your pg_has_role result and the verification query's output.

[H1manshu01]

You're right on the version. it's v1.77.7 (#1395, merged Sept 16). My v1.78.x was wrong.

On storage.prefixes: the "it was dropped later, so it's moot" argument hits your own shipped-vs-applied point. A tenant with 0073 but not the later prefixes-removal migration still has the table and still carries the TRUNCATE/REFERENCES/TRIGGER grants that 0073's REVOKE list never names. So I'd test for it rather than assume it:

select to_regclass('storage.prefixes') as prefixes_exists;

select grantee, privilege_type
from information_schema.role_table_grants
where table_schema = 'storage'
  and table_name = 'prefixes'
  and grantee in ('anon', 'authenticated');

Non-null + rows returned = a real residual-grant case for SU-498042 alongside #1395. Null = your read's confirmed and we drop it.

[aniketatgithub]

Short split on this one. Your own tables in public are yours to harden. The three storage tables and the supabase_admin default rows are not, and for the storage tables you do not need to win that fight.

On storage.buckets, storage.objects and storage.buckets_analytics, Supabase Storage already removes exactly this. Migration 0073 in supabase/storage (PR 1395, first released in v1.77.7) revokes TRUNCATE, REFERENCES and TRIGGER from anon and authenticated on those three tables, and it also adds a default privilege rule for future storage tables. So the supported path is that Storage upgrade reaching your project, not a manual revoke from postgres. For SU-498042 I would cite PR 1395 and migration 0073 and ask when it lands on your project, with your pg_has_role result and the check query below attached.

Why postgres cannot just revoke them: the tables are owned by supabase_storage_admin, and Postgres only lets the owner, a member of the owner role, a grant option holder, or a superuser revoke. An April 2025 platform migration removed postgres membership in supabase_storage_admin, so on a current project this should come back false:

select pg_has_role('postgres', 'supabase_storage_admin', 'member');

If it is false, do not change ownership or look for a side door. Leave those three tables to Storage and support.

Removing TRUNCATE alone cannot change SELECT, INSERT, UPDATE or DELETE behavior, because Postgres checks each command against its own privilege, and the owner keeps its privileges as owner. That is also why this is defense in depth rather than a live hole: PostgREST has no TRUNCATE operation, so there is nothing for anon or authenticated to call.

For default privileges, Postgres lets you change only your own defaults and the defaults of roles you are a member of. So the postgres rows for public and storage are yours to change, and the supabase_admin rows for public, graphql and graphql_public are platform managed. Leave those alone.

For your future tables, this is the self serve part:

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

That only affects tables created later by postgres. Existing tables still need a one off revoke, and tables created by another role follow that role defaults instead. The same ALL grant is also where TRIGGER and REFERENCES came from, so decide if you want to trim those too.

To confirm the end state:

select n.nspname, c.relname from pg_class c join pg_namespace n on n.oid = c.relnamespace where c.relkind in ('r', 'p') and n.nspname not in ('pg_catalog', 'information_schema') and (has_table_privilege('anon', c.oid, 'TRUNCATE') or has_table_privilege('authenticated', c.oid, 'TRUNCATE'));

Anything still listed there after the revoke and after the Storage upgrade is worth adding to the ticket.