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.

This page explains where these privileges come from, how to find them, and which ones you can safely revoke.

Overview

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. You can’t remove this without affecting Supabase services, so limit who can connect and set a connection limit on each role.

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 and the system catalogs. When the pg_net extension is enabled, it can also use the net schema. 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 you can revoke it without affecting Supabase services.

The postgres role owns the postgres database and the public schema, so Postgres lets it revoke the PUBLIC grants on both. The roles that Supabase services connect as receive those privileges only through PUBLIC, so leave them in place. Supabase roles own the other objects, so a revoke on them removes nothing. Depending on the object, Postgres prints the no privileges could be revoked warning or no message at all. For more information about objects that Supabase roles own, see Why the revoke has no effect. For more information about the pg_net grants, see Revoking access to pg_net objects has no effect.

Settings that don’t restrict a role

Some settings look like restrictions, but a session can change them:

• 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.

Use grants to restrict a role.

Diagnose the problem

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.

List the schemas the role can use

Replace app_svc with the name of your role.

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.

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;

List the tables, views, and sequences the role can use

To list the tables, views, and sequences the role can read or change in those schemas, replace app_svc.

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.

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;

Check who holds privileges on an object

To see who holds privileges on an object, check its access control list. Replace net.http_request_queue with the object.

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.

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

Read the output

The results of the first two queries are the role’s effective privileges. Access you didn’t grant directly comes from one of these sources:

• PUBLIC
• a role it’s a member of
• owning the object

In the access control list, an entry that starts with =, such as =arwdDxtm/supabase_admin, is a grant to PUBLIC. To see whether postgres can revoke it, find the object in What every role receives from PUBLIC.

Solve the problem

To limit what a role can do, grant it only what its service needs. The only PUBLIC privilege you can also revoke is execute on functions you create.

Revoke execute on functions you create

To stop PUBLIC from running functions that postgres creates after the change, update the default privileges without naming a 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.

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';

The default execute grants to the API roles don’t cover functions in schemas that you create.

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;

Supabase services lose execute on these functions too. If a function is an Auth hook, Auth calls it as the supabase_auth_admin role, so grant that role execute:

1
grant execute on function public.my_function() to supabase_auth_admin;

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.

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.

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.

Confirm the problem is solved

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

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.

1
select
2
  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.

Prevent the problem

Extensions you enable can grant more privileges to PUBLIC. After you enable an extension, run the queries in Diagnose the problem for the role again.

Related topics

• Database roles can read request headers queued by pg_net
• Can’t revoke a role membership granted by supabase_admin
• Revoking privileges on Storage, pg_cron, Vault, and extension objects has no effect
• Postgres roles

Go to the GitHub discussion