Revoking privileges on Storage, pg_cron, Vault, and extension objects has no effect

[supabase-troubleshoot-docs-app[bot]]

When you run revoke as the postgres role on objects that Supabase creates for you, the statement completes but access stays the same. This behavior applies to attempts such as the following:

• Revoking truncate, references, trigger, or maintain on storage.objects and storage.buckets from anon and authenticated.
• Revoking the PUBLIC grants on cron.job, cron.job_run_details, and cron.schedule.
• Revoking access to vault.decrypted_secrets, vault.secrets, and vault.create_secret from service_role.
• Revoking PUBLIC access to the functions and views of extensions such as http, pg_stat_statements, and pgtap, and to Realtime functions such as realtime.cast.

For most of these objects, Postgres prints no message at all. For some, such as storage.buckets_analytics and realtime.cast, it prints a warning that the SQL Editor doesn't display:

WARNING:  no privileges could be revoked for "buckets_analytics"

This page explains why the revoke has no effect, and how to control which roles can reach these objects. By default, clients of your app can't use these grants. For the reasons and the exceptions, see Who can use the grants.

Overview

Why the revoke has no effect

Supabase services and extensions create these objects with their own roles. Those roles own the objects and made the grants:

Objects	Owner and grantor
Tables in the storage schema	supabase_storage_admin
Tables and functions in the cron schema	supabase_admin
Tables, views, and functions in vault	supabase_admin
Most tables and functions in the realtime schema	supabase_realtime_admin
Objects of most other extensions	supabase_admin. For the exceptions, see Limit access to extension objects.

In Postgres, a role can revoke only the privileges that it granted. The postgres role made none of these grants, so its revoke finds nothing to remove. For the rules on who can revoke a privilege, see the Postgres documentation on REVOKE.

On many of these objects, postgres holds the privilege with the grant option. Postgres then removes only grants that postgres made, finds none, and prints nothing. Where postgres holds the privilege without the grant option, Postgres prints no privileges could be revoked.

The postgres role isn't a superuser, and it can't become the owner of these objects or a member of the owning roles. A grant to PUBLIC applies to every role, including roles you create later, and Postgres has no way to exclude a single role from it.

Who can use the grants

A grant has an effect only for a role that can run SQL against the object. Two limits apply to most of the grants on this page:

• anon, authenticated, and service_role can't connect to the database directly. Supabase services such as the Data API and Storage switch to these roles for each request. Roles that are members of them, such as postgres, can also switch to them with set role.
• A role needs usage on a schema to use any object in it. PUBLIC has no usage on cron, vault, extensions, or a schema you create.

The Data API serves only the schemas in your Data API settings, by default public and graphql_public. The Dashboard doesn't let you add storage, cron, vault, realtime, or extensions. Storage and Realtime have their own APIs, which query their tables as anon or authenticated, so Row Level Security (RLS) policies control what clients can reach there.

A role you create with login can still reach these objects in the following cases:

• You grant it anon, authenticated, service_role, or postgres.
• You grant it pg_read_all_data or pg_write_all_data. Both give usage on every schema, including cron and vault.
• You grant it usage on the schema directly.

To check what one of your roles can do, see Custom role inherits privileges that were not explicitly granted.

Approaches that don't work

• Adding granted by supabase_admin or granted by supabase_storage_admin to the revoke fails with grantor must be current user.
• Changing the owner, for example with alter table storage.objects owner to postgres, fails with must be owner of table objects.
• Granting the owning role to postgres, for example with grant supabase_storage_admin to postgres, fails with "supabase_storage_admin" role memberships are reserved, only superusers can grant them.

Diagnose the problem

List the grants on tables and views

This query lists the grants on tables and views in the storage, cron, vault, and realtime schemas, and the role that made each grant.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

select
  c.oid::regclass as object,
  c.relowner::regrole as owner,
  case acl.grantee when 0 then 'PUBLIC' else acl.grantee::regrole::text end as grantee,
  acl.grantor::regrole as grantor,
  string_agg(acl.privilege_type, ', ' order by acl.privilege_type) as privileges
from pg_class c
cross join lateral aclexplode(c.relacl) as acl
where c.relnamespace::regnamespace::text in ('storage', 'cron', 'vault', 'realtime')
  and acl.grantee <> c.relowner
group by 1, 2, 3, 4
order by 1, 3;

List the roles that can run functions

This query lists the roles that can run the functions in the cron, vault, and realtime schemas.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

select
  p.oid::regprocedure as function,
  case acl.grantee when 0 then 'PUBLIC' else acl.grantee::regrole::text end as grantee,
  acl.grantor::regrole as grantor
from pg_proc p
cross join lateral aclexplode(coalesce(p.proacl, acldefault('f', p.proowner))) as acl
where p.pronamespace::regnamespace::text in ('cron', 'vault', 'realtime')
  and acl.grantee <> p.proowner
order by 1, 2;

List the schema of each extension

This query shows which schema each extension is installed in, and whether the anon role can use that schema.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

select
  e.extname as extension,
  e.extnamespace::regnamespace as schema,
  has_schema_privilege('anon', e.extnamespace, 'usage') as anon_can_use_schema
from pg_extension e
where e.extnamespace <> 'pg_catalog'::regnamespace
order by 2, 1;

List the owner of each extension's functions

This query shows which role owns the functions of each extension.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

select
  e.extname as extension,
  p.proowner::regrole as function_owner,
  count(*) as functions
from pg_extension e
join pg_depend d
  on d.refobjid = e.oid
  and d.refclassid = 'pg_extension'::regclass
  and d.classid = 'pg_proc'::regclass
  and d.deptype = 'e'
join pg_proc p on p.oid = d.objid
group by 1, 2
order by 1, 2;

Where function_owner is postgres, a revoke from postgres takes effect.

Check for extra privileges on Storage tables

This query checks whether anon and authenticated have truncate on storage.objects.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

select
  has_table_privilege('anon', 'storage.objects', 'truncate') as anon_can_truncate,
  has_table_privilege('authenticated', 'storage.objects', 'truncate') as authenticated_can_truncate;

If both values are false, your project doesn't have the extra privileges on storage.objects that Limit access to Storage tables describes.

List the roles that can use cron

This query lists the roles that can use the cron schema.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

select rolname
from pg_roles
where has_schema_privilege(oid, 'cron', 'usage') and rolname not like 'pg\_%'
order by 1;

The result also includes Supabase roles, such as supabase_admin, supabase_etl_admin, and supabase_read_only_user.

To list every job and the role that runs it, run:

select jobid, jobname, username, schedule, active
from cron.job
order by username, jobid;

Solve the problem

You can't change these grants, and Supabase doesn't support changing them for individual projects. Instead, control which roles can reach the objects. Follow the subsection for each type of object. For pg_net, see Revoking access to pg_net objects has no effect.

Limit access to Storage tables

anon and authenticated have select, insert, update, and delete on storage.objects and storage.buckets, because Storage runs each request as the role of the caller. Your RLS policies decide what those privileges allow. service_role has all privileges on these tables. It's an administrative role that also bypasses RLS, so keep the service role key on your servers.

On some projects, anon and authenticated also have truncate, references, and trigger on storage.objects, storage.buckets, and storage.buckets_analytics. On Postgres 17 and later, they also have maintain. Storage removes these extra privileges when it updates the storage schema of your project. Storage rolls out schema updates to projects over time, so until your project gets the update, the extra privileges stay, and revoking them as postgres has no effect. To check your project, see Check for extra privileges on Storage tables.

While your project has the extra privileges, RLS doesn't apply to truncate, so a session running as anon or authenticated can empty these tables in SQL. The Storage API and the Data API don't run truncate themselves. A session runs truncate as anon or authenticated only through SQL that someone wrote, for example:

• A role that can connect to the database and is, or inherits, anon or authenticated.
• A function in an exposed schema that runs truncate on a Storage table, or runs SQL text passed in by the caller. By default, a function runs as the role that calls it, so anon can run such a function and empty a Storage table. The Data API lets clients call functions in exposed schemas.

To limit access to Storage tables:

• Write RLS policies for storage.objects and storage.buckets. The postgres role can create, change, and drop policies on these tables. For examples, see Storage access control.
• Don't grant anon, authenticated, or service_role to roles that can connect to the database.
• Don't create functions in exposed schemas that run truncate on Storage tables, or that run SQL text passed in by the caller.
• Don't change the tables in the storage schema. For details, see Modifying the schema.

Limit access to pg_cron

The pg_cron extension makes the following grants to PUBLIC:

• select on cron.job.
• select and delete on cron.job_run_details.
• execute on cron.schedule and cron.unschedule.

RLS policies on both tables limit each role to rows where username is the role itself. A role with the bypassrls attribute skips these policies, so it can read and delete the run history of every job. PUBLIC can't run cron.alter_job or cron.schedule_in_database.

PUBLIC has no usage on the cron schema, so a role without it fails with permission denied for schema cron. Of the roles you use, only postgres has usage on cron by default. A role you create gets it, for example, through pg_read_all_data.

A role with usage on cron can do the following through the PUBLIC grants:

• Read its own jobs and their run history.
• Delete rows from cron.job_run_details. With bypassrls, this includes the history of jobs that other roles own.
• Schedule jobs with cron.schedule. These jobs run as the role that scheduled them, so they can do only what the role can do.
• Remove its own jobs with cron.unschedule.

To check which roles have usage on cron, see List the roles that can use cron.

Keep backup and export roles out of cron

A backup or export role often gets pg_read_all_data, which includes usage on cron. The role can then schedule jobs and delete the run history of its own jobs. If it also has bypassrls, which pg_dump needs to read tables with RLS, it can delete the run history of every job. The same role can also reach pg_net, as described in Revoking access to pg_net objects has no effect.

To avoid this, grant the role usage and select on the schemas it needs, instead of pg_read_all_data. Replace backup_role in the following SQL.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

grant usage on schema public to backup_role;
grant select on all tables in schema public to backup_role;
alter default privileges in schema public grant select on tables to backup_role;

The alter default privileges statement covers tables that postgres creates later. Repeat these statements for each schema the role reads. Don't grant usage on cron.

If the role must read every schema, for example to dump the whole database, it needs pg_read_all_data. In that case, check cron.job regularly for jobs that the role scheduled.

Remove a job that another role scheduled

The postgres role can see every job, but it can't remove a job that another role scheduled. cron.unschedule fails with could not find valid entry for job. To remove the job, act as the role that owns it. Replace backup_role and job_name in the following SQL.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

grant backup_role to postgres;
set role backup_role;
select cron.unschedule('job_name');
reset role;
revoke backup_role from postgres;

Change jobs with the pg_cron functions

The postgres role can't write to cron.job. Statements such as lock table cron.job in share row exclusive mode fail with permission denied for table job. To create, change, and remove jobs, use the pg_cron functions. For examples, see pg_cron.

Drop pg_cron if you don't use it

Dropping pg_cron removes the cron schema with its tables, functions, and grants.

Caution

Dropping pg_cron deletes all jobs and their run history. Before you drop it, list your jobs with the cron.job query in List the roles that can use cron.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

drop extension pg_cron;

Limit access to Vault secrets

When Supabase creates the Vault extension, it grants service_role the following privileges:

• usage on the vault schema.
• select and delete on vault.secrets and vault.decrypted_secrets.
• execute on vault.create_secret, vault.update_secret, and vault._crypto_aead_det_decrypt.

It grants postgres the same privileges with the grant option. The grants on your project can differ, depending on the Vault version. To check them, run the queries in Diagnose the problem.

The service_role grants have an effect only where SQL runs as service_role and reaches the vault schema:

• The Data API reaches Vault when vault is in the exposed schemas, or when service_role can run a function in an exposed schema that reads Vault.
• A database connection reaches Vault as service_role only with a role that is, or inherits, service_role. The postgres role is a member of service_role, and it also has its own Vault grants.

By default, functions that postgres creates in public are executable by anon, authenticated, and service_role. Don't create a function in an exposed schema that returns decrypted secrets.

Give a dedicated role access to one secret

To let one role read a secret without granting it access to Vault:

1. Create a security definer function that reads the secret, in a schema that isn't exposed.
2. Grant execute on the function only to that role.

Replace worker_role and my_secret in the following SQL.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

create schema if not exists private;

create function private.get_my_secret()
returns text
language sql
security definer
set search_path = ''
as $$
  select decrypted_secret
  from vault.decrypted_secrets
  where name = 'my_secret';
$$;

revoke execute on function private.get_my_secret() from public;
grant usage on schema private to worker_role;
grant execute on function private.get_my_secret() to worker_role;

The function runs as postgres, so worker_role can read the secret through it but can't query vault.decrypted_secrets. service_role can't run the function, because it has no usage on private.

service_role still has its own Vault grants. If service_role must not be able to read a credential at all, don't store that credential in Vault.

Limit access to extension objects

Supabase creates most extensions as supabase_admin, so supabase_admin owns their functions, tables, and views, and the PUBLIC grants that the extensions make stay in place. You can't revoke them, but you can control who can use the schema that the extension is installed in.

Some extensions, such as pg_partman and pgmq, are created as the role that runs create extension. When postgres creates them, postgres owns their objects and can revoke their PUBLIC grants. To check the owner, see List the owner of each extension's functions.

Install extensions in a schema that you control

The Data API doesn't expose the extensions schema by default, but anon, authenticated, and service_role have usage on it. A schema you create has no usage for PUBLIC. In that schema, only the roles you grant usage to, and roles with pg_read_all_data or pg_write_all_data, can call the extension's functions.

The following SQL moves an extension that supports it, such as pg_trgm, vector, dblink, pgaudit, or pgtap, to another schema.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

create schema if not exists private_extensions;
alter extension pg_trgm set schema private_extensions;

After the move, code that calls the extension without a schema name needs private_extensions in its search path.

Other extensions, such as http and pgroonga, fail with an error such as extension "http" does not support SET SCHEMA. To move one of them, drop it and create it again in the other schema. For PostGIS, see Moving PostGIS to a different schema.

Caution

Dropping an extension with cascade deletes your columns and indexes that use its types or operators. For example, drop extension pg_trgm cascade deletes your indexes that use gin_trgm_ops. Without cascade, drop extension fails while such objects exist. Back up your data, and re-create the dropped objects after you create the extension again.

The following SQL moves http.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

drop extension http;
create extension http with schema private_extensions;

If your project doesn't use an extension, such as pgtap or http, drop it. Dropping an extension removes its objects and their grants.

Handle specific extensions

• For pg_stat_statements, PUBLIC can select from the pg_stat_statements view. A role without pg_read_all_stats sees the query text only for statements it ran itself, and <insufficient privilege> for the rest. The postgres role has pg_read_all_stats, so it sees every query.
• For pgsodium, Supabase doesn't recommend the extension. For alternatives, see pgsodium. supabase_admin and pgsodium_keymaker own most of its functions, so you can't change their grants.
• If postgres owns the pgsodium schema on your project, postgres can revoke usage on it from PUBLIC. To check the owner, run select nspowner::regrole from pg_namespace where nspname = 'pgsodium';. Before you revoke usage, check that nothing in your project uses pgsodium.
• For dblink, the postgres role can't use the dblink_fdw foreign-data wrapper, so create server ... foreign data wrapper dblink_fdw fails with permission denied for foreign-data wrapper dblink_fdw. To store connection details for a remote Postgres database, use postgres_fdw. Other roles, such as authenticated, can't read the password in a user mapping that you create for postgres.

Limit access to Realtime objects

supabase_realtime_admin owns most objects in the realtime schema, such as realtime.cast and realtime.messages, so you can't change their grants. To see them, run the queries in Diagnose the problem. The Data API doesn't expose realtime by default.

To control who can receive Broadcast and Presence messages, write RLS policies on realtime.messages. For examples, see Realtime Authorization.

Confirm the problem is solved

Run the queries in Diagnose the problem again. The grants that Supabase made are still listed, because you can't change them. Check that the access you limited took effect:

• The roles you removed from cron no longer appear in List the roles that can use cron.
• Extensions you moved show the new schema in List the schema of each extension, and anon_can_use_schema is false for a schema you created.

Prevent the problem

The following events apply the default grants again. This list doesn't cover other events, such as Postgres upgrades and restores.

• Creating any extension applies the grants in the extension's install script. This includes dropping an extension and creating it again.
• Creating pg_cron or Vault also runs Supabase setup scripts. They grant access to postgres, and for Vault also to service_role.

After any of these events, or after an upgrade or a restore, run the queries in Diagnose the problem again.

You can't add steps to the scripts that Supabase runs when it creates an extension. To keep your access rules in place, put them on the objects you control, such as your own schemas, roles, functions, and RLS policies.

Related topics

• Supabase Vault
• Postgres roles
• Row Level Security
• Can't revoke a role membership granted by supabase_admin
• Can't change the default privileges of supabase_admin in the public schema