Permission errors when granting custom roles access to the auth schema

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

A custom role gets ERROR: 42501: permission denied for schema auth when it calls auth.uid(), even after you grant it usage on the auth schema. On hosted Supabase, that grant has no effect. This page shows how to check the role's access and how to give it the Auth data it needs.

Overview

The grant completes without an error, so it looks successful. Postgres reports it as a warning, WARNING: no privileges were granted for "auth", in your Postgres logs. A grant of select on an Auth table such as auth.users succeeds, but the role still can't reach the table without usage on the schema.

In Postgres, a role can grant a privilege only if it holds the privilege with the grant option. The supabase_admin role owns the auth schema, and supabase_auth_admin owns the tables and functions in it. The postgres role holds usage on the schema without the grant option, so its grant to another role changes nothing. For more information about the grant option, see the Postgres documentation on GRANT.

The postgres role isn't a superuser. It can't become supabase_admin or supabase_auth_admin, because only a superuser can grant membership in them.

The following statements don't give a custom role access to auth:

• grant usage on schema auth to my_role and grant execute on function auth.uid() to my_role complete without changing anything. The role already has execute on auth.uid(). If your migrations contain these statements, remove them.
• grant supabase_auth_admin to postgres fails with "supabase_auth_admin" role memberships are reserved, only superusers can grant them.
• set role supabase_auth_admin fails with permission denied to set role "supabase_auth_admin".

If a revoke of a role membership has no effect, or drop role fails with permission denied, see Can't revoke a role membership granted by supabase_admin.

Diagnose the problem

Check whether the role can use the auth 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.

Replace my_role with the name of your role:

select
  has_schema_privilege('my_role', 'auth', 'usage') as auth_schema_usage,
  has_function_privilege('my_role', 'auth.uid()', 'execute') as can_execute_uid;

Read the output

Column	Value	Meaning
can_execute_uid	true	Expected. execute on auth.uid() is granted to every role.
auth_schema_usage	false	The role can't reach anything in auth, and calls to auth.uid() fail. Use one of the options in Solve the problem.

Solve the problem

Choose the option that matches what the role needs.

Option 1: Read Auth data through a security definer function

A security definer function runs with the privileges of the role that created it. Create it as the postgres role, in a schema you control. postgres already has permission to perform the underlying Auth operation, and the custom role can execute the function without access to the auth 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;

create or replace function private.current_user_id()
returns uuid
language sql
security definer
set search_path = ''
stable as $$
  select auth.uid();
$$;

revoke all on function private.current_user_id() from public;
grant usage on schema private to my_role;
grant execute on function private.current_user_id() to my_role;

The same pattern works for narrow reads from auth.users or auth.sessions. For each function:

• Keep it small, and return only the value the caller needs.
• Fully qualify Auth objects.
• Restrict execute.
• Keep it in a schema that isn't exposed through the Data API.

For example, to let a custom role check whether a user has a session:

create or replace function private.user_has_session(p_user_id uuid)
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select exists (
    select 1 from auth.sessions where user_id = p_user_id
  )
$$;

revoke execute on function private.user_has_session(uuid)
  from public, anon, authenticated, service_role;
grant execute on function private.user_has_session(uuid) to my_role;

Option 2: Read the user ID from the JWT claims

auth.uid() reads the user ID from the request settings that the Data API sets from the user's JWT. A function you create can read the same settings without access to the auth schema, and without security definer.

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 or replace function private.request_user_id()
returns uuid
language sql
stable
set search_path = ''
as $$
  select coalesce(
    nullif(current_setting('request.jwt.claim.sub', true), ''),
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
  )::uuid
$$;

grant execute on function private.request_user_id() to my_role;

auth.jwt() fails for a custom role in the same way as auth.uid(). To read another claim, such as role, use nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'role'.

Option 3: Give the role the access of signed-in users

If the role needs the same access as signed-in users, make it a member of authenticated.

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 authenticated to my_role;

The role gets usage on auth and every privilege that authenticated has, including access to your tables in public. Grant this membership only if the role needs all of that access. A membership granted with inherit false doesn't give usage on auth.

Handle foreign keys, row locks, and backup roles

For other cases, the workaround depends on what the privilege is being used for:

• If you need references on auth.users(id), create your own user or profile table in a schema you control and point your application foreign keys to that table instead. A foreign key to auth.users needs access to the auth schema, so a table that a custom role owns can't have one. You can also create the table as postgres and grant the custom role the privileges it needs on the table.
• If you need to lock rows in auth.users or auth.sessions, use your own tables or advisory locks to coordinate application operations. Locking Auth-managed rows can interfere with Supabase Auth. If you have to lock them, use a security definer function, as in Option 1.
• If you need a dedicated backup role, use the supported backup-role pattern from pg_dump fails with 'query would be affected by row-level security policy' instead of granting custom access to managed Auth tables.

Warning

A role created with the backup-role pattern can also run the functions that every role can execute, such as cron.schedule when pg_cron is enabled. pg_read_all_data gives the role usage on every schema, including cron.

Confirm the problem is solved

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.

This query checks that the role can run the functions from Option 1:

select
  has_function_privilege('my_role', 'private.current_user_id()', 'execute') as can_read_user_id,
  has_function_privilege(
    'my_role',
    'private.user_has_session(uuid)',
    'execute'
  ) as can_check_session;

Both values are true. auth_schema_usage from Check whether the role can use the auth schema stays false.

Related topics

• Database roles
• Row Level Security
• Database API 42501 errors
• Custom role inherits privileges that were not explicitly granted