Custom role inherits privileges that were not explicitly granted

A role you create can use objects without an explicit grant. For example, it can read tables in the net schema, create temporary tables, or create large objects. Privilege checks such as has_table_privilege return true for these objects. When you run some revokes as postgres, they print WARNING: no privileges could be revoked and change nothing.

The role receives these privileges from PUBLIC, which stands for every role in the database, including roles you create later.

A role you create to limit a service or a person can therefore do more than you intended. When pg_net is enabled, any role that can connect to your database can read the request headers that pg_net queues, including API keys. It can also queue HTTP requests of its own. Any role can use disk space by creating temporary tables and large objects.

Find privileges you didn't grant#

To find privileges you didn't grant, list what the role can access and compare it with your own grants. The following queries check schema usage and the table and sequence privileges they name. They don't check column-level grants or execute on functions. To list the schemas a role can use, replace app_svc and run the following in the SQL Editor:

1
select nspname as schema
2
from pg_namespace
3
where
4
  has_schema_privilege('app_svc', oid, 'usage')
5
  and nspname not in ('pg_catalog', 'information_schema')
6
  and nspname not like 'pg\_%'
7
order by 1;

To list the tables, views, and sequences the role can read or change in those schemas, run:

1
select
2
  n.nspname as schema,
3
  c.relname as object,
4
  string_agg(p.privilege, ', ' order by p.privilege) as privileges
5
from pg_class c
6
join pg_namespace n on n.oid = c.relnamespace
7
cross join unnest(
8
  array['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'USAGE']
9
) as p(privilege)
10
where c.relkind in ('r', 'p', 'v', 'm', 'f', 'S')
11
  and n.nspname not in ('pg_catalog', 'information_schema')
12
  and has_schema_privilege('app_svc', n.oid, 'usage')
13
  and case
14
    when c.relkind = 'S' then
15
      case
16
        when p.privilege in ('USAGE', 'SELECT', 'UPDATE')
17
          then has_sequence_privilege('app_svc', c.oid, p.privilege)
18
        else false
19
      end
20
    else
21
      case
22
        when p.privilege = 'USAGE' then false
23
        else has_table_privilege('app_svc', c.oid, p.privilege)
24
      end
25
  end
26
group by 1, 2
27
order by 1, 2;

The results are the role's effective privileges. Access you didn't grant directly can come from PUBLIC, from a role it's a member of, or from owning the object. To see who holds privileges on an object, check its access control list:

1
select relacl from pg_class where oid = 'net.http_request_queue'::regclass;

An entry that starts with =, such as =arwdDxtm/supabase_admin, is a grant to PUBLIC.

What every role receives from PUBLIC#

A role holds everything granted to PUBLIC in addition to its own grants. The noinherit attribute doesn't affect privileges granted to PUBLIC, because PUBLIC isn't a role membership.

On a Supabase project, a role you create can use the public schema, the system catalogs, and the net schema when the pg_net extension is enabled. Other Supabase schemas, such as auth, storage, extensions, vault, and cron, don't grant usage to PUBLIC.

The following table lists what PUBLIC holds and whether the postgres role can revoke it.

The postgres role owns the postgres database, and through it the public schema, so it can revoke the PUBLIC grants on those. Supabase roles own the other objects, so a revoke on them prints the no privileges could be revoked warning. For the pg_net grants, see Revoking access to pg_net objects has no effect.

Extensions you enable can grant more privileges to PUBLIC. After you enable an extension, check the role again.

Revoke PUBLIC privileges on objects you own#

You can revoke the PUBLIC grants on the public schema, on the postgres database, and on functions you create.

Functions you create#

To stop PUBLIC from running functions that postgres creates after the change, update the default privileges without naming a schema:

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

A default privilege set with in schema can't remove the PUBLIC default for functions, because Postgres adds schema defaults to the global ones.

The Supabase defaults also grant execute to anon, authenticated, and service_role on functions that postgres creates in public. Those roles keep their access. If you changed those defaults, or create functions as another role, grant execute to the API roles that need it.

To check the default privileges for functions, run:

1
select defaclrole::regrole as owner, defaclnamespace::regnamespace as schema, defaclacl
2
from pg_default_acl
3
where defaclobjtype = 'f';

Functions in other schemas don't get those grants. If a Row Level Security policy calls a helper function outside public, grant execute on it to the roles the policy applies to:

1
grant execute on function private.is_admin() to authenticated;

To revoke execute from PUBLIC on a function that already exists, run:

1
revoke execute on function public.my_function() from public;

The public schema#

To stop roles you create from using the public schema without a grant, run:

1
revoke usage on schema public from public;

The postgres, anon, authenticated, and service_role roles keep their own usage grants, so the Data API keeps working. Other roles lose access to the schema unless they have their own grant, so grant usage to each role you create that needs it. Test the change on a preview branch before you apply it to production.

Temporary tables#

To allow temporary tables only for roles that you grant temporary to, run:

1
revoke temporary on database postgres from public;

Grant temporary back to each role that needs it. If your Data API functions create temporary tables, also grant it to authenticated. Test this change on a preview branch too.

Confirm the change#

To confirm that the role lost the privileges you revoked, replace app_svc and public.my_function() and run:

1
select
2
  has_schema_privilege('app_svc', 'public', 'usage') as can_use_public_schema,
3
  has_database_privilege('app_svc', 'postgres', 'temporary') as can_create_temp_tables,
4
  has_function_privilege('app_svc', 'public.my_function()', 'execute') as can_run_my_function;

Each column returns false unless the role still receives the privilege from its own grant or from a role it's a member of.

Grant only what a service needs#

Give each service its own role, and grant only the schemas and tables it uses. The following example creates a role that can read and add orders in an app schema:

1
create role app_svc login password '<strong-password>';
2
grant usage on schema app to app_svc;
3
grant select, insert on app.orders to app_svc;
4
grant usage on sequence app.orders_id_seq to app_svc;

A role you create is subject to Row Level Security on tables it doesn't own, unless you give it the bypassrls attribute. A table's owner bypasses its policies unless you run alter table <table_name> force row level security.

Don't grant the role membership in postgres or in other Supabase roles, because a member role inherits all of their privileges. For more information about roles, see Postgres Roles.

Settings that don't restrict a role#

Some settings look like restrictions, but a session can change them. Use grants to restrict a role.

• default_transaction_read_only: a session can run set default_transaction_read_only = off and then write. Use it to prevent accidental writes only.
• search_path: a session can set its own search path or use schema-qualified names.