Can't change the default privileges of supabase_admin in the public schema

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

When you try to remove the default privileges that the supabase_admin role has in the public schema, Postgres rejects the statement. For example:

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

ERROR:  42501: permission denied to change default privileges

This page explains why the statement fails, and how to control which objects get these grants.

Overview

The pg_default_acl catalog of a Supabase project has default privileges that belong to the supabase_admin role in the public schema. They give anon, authenticated, and service_role all privileges on tables, sequences, and functions that supabase_admin creates in public.

These default privileges apply only to objects that supabase_admin creates. Tables and functions that you create get the default privileges of postgres.

You can't change the supabase_admin default privileges, but you can control which objects they apply to:

• To stop your new tables and functions from getting Data API grants, control the default privileges of your own objects.
• To keep supabase_admin from creating objects in public, move extensions out of the public schema.

Why postgres can't change them

In Postgres, a role can change default privileges only for itself and for roles that it's a member of. On Postgres 15, the statement fails with must be member of role "supabase_admin" instead. For more information about which roles can change default privileges, see the Postgres documentation on ALTER DEFAULT PRIVILEGES.

The postgres role isn't a member of supabase_admin and can't become one, because Supabase reserves memberships in its internal roles. Supabase sets the supabase_admin default privileges when it creates the database. They give the Data API roles grants on the objects that supabase_admin creates in public.

The following approaches don't work:

• grant supabase_admin to postgres fails with "supabase_admin" role memberships are reserved, only superusers can grant them.
• set role supabase_admin fails with permission denied to set role "supabase_admin".
• Revoking privileges on objects that supabase_admin owns completes, and the grants stay. Postgres prints a warning such as WARNING: no privileges could be revoked for "spatial_ref_sys", because a role can revoke only the privileges that it granted. The SQL Editor doesn't show warnings, so the statement looks successful there. For more information, see the Postgres documentation on REVOKE.

Which objects supabase_admin creates in public

supabase_admin is the internal role that Supabase uses for administrative tasks, such as upgrades. The objects that it owns in public come from extensions that you create there:

• Supabase services don't create tables, views, or functions in public as supabase_admin. Auth, Storage, and Realtime keep their objects in their own schemas.
• Supabase creates most extensions as supabase_admin, even when you run create extension as postgres. When one of those extensions is in public, its objects are owned by supabase_admin and get its default privileges.
• Upgrades don't change the owner or grants of objects in public, or the default privileges of postgres and supabase_admin.

create extension without a schema puts the extension in public, unless the extension sets its own schema. public is the first schema in the default search path that exists. The Database Extensions page in the Supabase Dashboard installs extensions in the extensions schema, unless the extension requires its own schema. To use another schema, run create extension with a schema clause in the SQL Editor or with psql.

The Automatically enable RLS for new tables option in the Supabase Dashboard, and other event triggers that enable Row Level Security (RLS) on new tables, don't enable RLS on tables that an extension creates. Only a table's owner can enable RLS on it, so alter table public.spatial_ref_sys enable row level security fails with must be owner of table spatial_ref_sys. For more information about who can enable RLS on a table, see the Postgres documentation on ALTER TABLE.

What the Data API setting changes

The Automatically expose new tables setting in the Data API settings changes only the default privileges of postgres in public. It doesn't change the supabase_admin rows.

Turning the setting off revokes select, insert, update, and delete on tables, usage and select on sequences, and execute on functions. The following grants stay:

• truncate, references, trigger, and maintain on tables
• update on sequences
• execute on functions for PUBLIC, which lets anon and authenticated call new functions

The setting also revokes execute from PUBLIC in the public schema, but that revoke has no effect. A default privilege for one schema can't remove the PUBLIC default for functions.

Creating a project with Automatically expose new tables disabled has the same scope, and also revokes update on sequences. For more information about the Data API setting, see Revoke default privileges.

What the extra privileges allow

The supabase_admin default privileges grant more than the Data API uses:

• Through the Data API, clients read, insert, update, and delete rows and call functions.
• truncate, references, trigger, and maintain need a SQL session that runs as the role. RLS policies don't apply to truncate.
• The anon and authenticated roles can't connect to the database directly.

Diagnose the problem

List the default privileges

The following query lists the default privileges that postgres and supabase_admin grant to other roles in public.

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
  d.defaclrole::regrole as creator,
  case d.defaclobjtype
    when 'r' then 'tables'
    when 'S' then 'sequences'
    when 'f' then 'functions'
  end as object_type,
  case acl.grantee when 0 then 'PUBLIC' else acl.grantee::regrole::text end as grantee,
  string_agg(acl.privilege_type, ', ' order by acl.privilege_type) as privileges
from pg_default_acl d
cross join lateral aclexplode(d.defaclacl) as acl
where d.defaclnamespace = 'public'::regnamespace
  and d.defaclrole in ('postgres'::regrole, 'supabase_admin'::regrole)
  and acl.grantee <> d.defaclrole
group by 1, 2, 3
order by 1, 2, 3;

Read the default privileges

The creator column names the role whose new objects get the grants. The rows for postgres apply to the objects that you create, and they change when you follow Control the default privileges of your own objects.

On most projects, the rows for supabase_admin look like this:

Object type	Grantees	Privileges
tables	postgres, anon, authenticated, service_role	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, MAINTAIN
sequences	postgres, anon, authenticated, service_role	USAGE, SELECT, UPDATE
functions	postgres, anon, authenticated, service_role	EXECUTE

• MAINTAIN exists only on Postgres 17 or later.
• Functions that supabase_admin creates are also executable by PUBLIC, because that's the Postgres default for functions. supabase_admin has no default privilege that removes it.
• The graphql and graphql_public schemas, which the pg_graphql extension uses, have the same supabase_admin entries.
• If some of these rows are missing on your project, the objects that supabase_admin creates in public don't get the grants in those rows.

Find objects in public that supabase_admin owns

The supabase_admin default privileges matter only if supabase_admin creates objects in public. The following query counts the objects in public that supabase_admin owns, grouped by the extension they belong to.

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.

with objects as (
  select 'pg_class'::regclass as classid, c.oid as objid,
    case c.relkind
      when 'S' then 'sequence'
      when 'v' then 'view'
      when 'm' then 'materialized view'
      else 'table'
    end as kind
  from pg_class c
  where c.relnamespace = 'public'::regnamespace
    and c.relowner = 'supabase_admin'::regrole
    and c.relkind in ('r', 'p', 'v', 'm', 'f', 'S')
  union all
  select 'pg_proc'::regclass, p.oid, 'function'
  from pg_proc p
  where p.pronamespace = 'public'::regnamespace
    and p.proowner = 'supabase_admin'::regrole
)
select
  coalesce(e.extname, '(none)') as extension,
  o.kind,
  count(*) as objects
from objects o
left join pg_depend d
  on d.classid = o.classid and d.objid = o.objid and d.deptype = 'e'
left join pg_extension e on e.oid = d.refobjid
group by 1, 2
order by 1, 2;

Read the object list

• No rows: supabase_admin owns no tables, views, sequences, or functions in public.
• Rows with an extension name: these are objects of an extension that was created in public. To move them, see Move extensions out of the public schema.
• Rows with (none) as the extension: contact Supabase Support with the query result.

Solve the problem

Control the default privileges of your own objects

The default privileges of postgres decide which grants your new tables, sequences, and functions get.

The revoke execute on functions from public statement in the following SQL doesn't name a schema, so it applies to functions that postgres creates in every schema. After you run it, grant execute on new functions to the roles that call them, including helper functions in other schemas that RLS policies call.

The following SQL removes all default grants to the API roles for objects that postgres creates in public.

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.

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

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

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

alter default privileges for role postgres
  revoke execute on functions from public;

These statements change only objects that postgres creates after you run them. Existing objects keep their grants.

After the change, the API roles can't access a new table until you grant access to it. For the grants and RLS steps, see Grant access explicitly. If you create objects as another role, such as a role your migration tool uses, change the default privileges for that role too.

To give new objects grants again, grant the privileges that you want them to get. For example, to give new tables select, insert, update, and delete for the API roles, and to let PUBLIC execute new functions again, run:

alter default privileges for role postgres in schema public
  grant select, insert, update, delete on tables to anon, authenticated, service_role;

alter default privileges for role postgres
  grant execute on functions to public;

Move extensions out of the public schema

The search path of the postgres role and the default extra search path of the Data API both include extensions, so queries that call extension functions without a schema name keep working after the move. Roles that you create don't get extensions in their search path unless you set it.

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.

To move the extensions that are already in public:

1. List the extensions in public, and whether each one can move to another schema:

   select extname, extversion, extrelocatable as can_move
   from pg_extension
   where extnamespace = 'public'::regnamespace;

2. List your functions that set their own search_path without extensions:

   select p.oid::regprocedure as function_name, p.proconfig as settings
   from pg_proc p
   where p.proowner = 'postgres'::regrole
     and p.prorettype <> 'event_trigger'::regtype
     and not exists (
       select 1 from pg_depend d
       where d.classid = 'pg_proc'::regclass and d.objid = p.oid and d.deptype = 'e'
     )
     and exists (
       select 1
       from unnest(p.proconfig) as setting
       where setting like 'search_path=%'
         and setting not like '%extensions%'
     );

   If a function in the result calls the extension's functions without a schema name, it can't find them after the move. Functions that don't call the extension need no change. For each function that does, add extensions to the end of its existing search path. For example, for a function whose settings value is {search_path=public}, run:

   alter function public.my_function(integer)
     set search_path = public, extensions;

   In code that calls an extension function with the public. prefix, such as public.similarity(a, b), replace the prefix with extensions..

3. For each extension where can_move is true, run:

   alter extension pg_trgm set schema extensions;

The extension's objects keep their grants after the move. The Data API doesn't expose extensions by default, and the Dashboard doesn't let you add it to the exposed schemas.

An extension where can_move is false, such as PostGIS, can't move with alter extension. Dropping and re-creating it also drops the columns, indexes, and functions that depend on it. To move PostGIS without dropping it, contact Supabase Support.

Confirm the problem is solved

If PostGIS is still in public, the object query and the Security Advisor still show its objects and its Extension in Public finding.

1. Run the query in Find objects in public that supabase_admin owns. It returns no rows.

2. Open the Security Advisor and click Rerun linter. From a terminal, run supabase db advisors --linked --type security with the Supabase CLI. The Extension in Public finding no longer appears.

3. Run the query in List the default privileges. If you removed all default grants for postgres, the query returns rows only for supabase_admin.

4. If you ran the revoke execute statement in Control the default privileges of your own objects, check it. 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. It returns {postgres=X/postgres}, which means that only postgres can execute new functions by default:

   select defaclacl
   from pg_default_acl
   where defaclrole = 'postgres'::regrole
     and defaclnamespace = 0
     and defaclobjtype = 'f';

Prevent the problem

To keep supabase_admin from creating objects in public, create extensions in the extensions schema. In your migrations, always name the schema:

create extension if not exists pg_trgm with schema extensions;

Run the queries in Diagnose the problem again after you enable an extension, and after a restore or an upgrade.

Related topics

• Can't revoke a role membership granted by supabase_admin
• Custom role inherits privileges that were not explicitly granted
• Database extensions
• Securing your API