Supabase client permission denied for schema public

Question

Whenever I've try to use @supabase/supabase-js to query the db, I get an error.

error: {
    hint: null,
    details: null,
    code: '42501',
    message: 'permission denied for schema public'
}

I think it has something to do with Prisma, which I use to handle migrations. The client worked fine when I was just clicking around with a prototype, but after setting up Prisma, it doesn't work anymore.

Any suggestions on how to fix this? I would really like to be able to use the Supabase REST API and Prisma together.

Answer 1

Cause: When you exec prisma migrate reset or prisma migrate dev in the remote Supabase database you may need to reset it. Resetting the database drops the whole database and recreates it. Source

This clears all the GRANTs that are required for PostgREST to work You can find the error in the PostgREST documentation at:

https://postgrest.org/en/latest/tutorials/tut0.html?highlight=42501#step-5-run-postgrest

Solution 1: Create a brand new project and use only prisma migrate deploy on the remote database.

Solution 2: Fix the grants manually by granting access to the tables

You can check the difference when you run the following SQL on a brand new Supabase project and your damaged database to observe the differences.

SELECT *
FROM information_schema.role_table_grants 
WHERE table_schema='public' and table_name='members'

The following default grants are taken from the supabase github repo at https://github.com/supabase/supabase/blob/a2fc6d592cb4ea50fd518b99db199a31912040b9/docker/volumes/db/init/00-initial-schema.sql#L26-L29

grant usage on schema public to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

alter default privileges for user supabase_admin in schema public grant all
    on sequences to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema public grant all
    on tables to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema public grant all
    on functions to postgres, anon, authenticated, service_role;

Here's a list of default permissions for a sample table members

grantor	grantee	table_catalog	table_schema	table_name	privilege_type	is_grantable	with_hierarchy
postgres	postgres	postgres	public	members	INSERT	YES	NO
postgres	postgres	postgres	public	members	SELECT	YES	YES
postgres	postgres	postgres	public	members	UPDATE	YES	NO
postgres	postgres	postgres	public	members	DELETE	YES	NO
postgres	postgres	postgres	public	members	TRUNCATE	YES	NO
postgres	postgres	postgres	public	members	REFERENCES	YES	NO
postgres	postgres	postgres	public	members	TRIGGER	YES	NO
postgres	anon	postgres	public	members	INSERT	NO	NO
postgres	anon	postgres	public	members	SELECT	NO	YES
postgres	anon	postgres	public	members	UPDATE	NO	NO
postgres	anon	postgres	public	members	DELETE	NO	NO
postgres	anon	postgres	public	members	TRUNCATE	NO	NO
postgres	anon	postgres	public	members	REFERENCES	NO	NO
postgres	anon	postgres	public	members	TRIGGER	NO	NO
postgres	authenticated	postgres	public	members	INSERT	NO	NO
postgres	authenticated	postgres	public	members	SELECT	NO	YES
postgres	authenticated	postgres	public	members	UPDATE	NO	NO
postgres	authenticated	postgres	public	members	DELETE	NO	NO
postgres	authenticated	postgres	public	members	TRUNCATE	NO	NO
postgres	authenticated	postgres	public	members	REFERENCES	NO	NO
postgres	authenticated	postgres	public	members	TRIGGER	NO	NO
postgres	service_role	postgres	public	members	INSERT	NO	NO
postgres	service_role	postgres	public	members	SELECT	NO	YES
postgres	service_role	postgres	public	members	UPDATE	NO	NO
postgres	service_role	postgres	public	members	DELETE	NO	NO
postgres	service_role	postgres	public	members	TRUNCATE	NO	NO
postgres	service_role	postgres	public	members	REFERENCES	NO	NO
postgres	service_role	postgres	public	members	TRIGGER	NO	NO

Answer 2

Run this SQL script in Supabase SQL editor.

source: https://supabase.com/docs/guides/integrations/prisma#troubleshooting

CREATE SCHEMA IF NOT EXISTS "auth";
CREATE SCHEMA IF NOT EXISTS "extensions";
create extension if not exists "uuid-ossp"      with schema extensions;
create extension if not exists pgcrypto         with schema extensions;
create extension if not exists pgjwt            with schema extensions;

grant usage on schema public to postgres, anon, authenticated, service_role;
grant usage on schema extensions to postgres, anon, authenticated, service_role;
alter user supabase_admin SET search_path TO public, extensions; -- don't include the "auth" schema

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role, supabase_admin;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role, supabase_admin;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role, supabase_admin;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

alter default privileges for user supabase_admin in schema public grant all on sequences to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema public grant all on functions to postgres, anon, authenticated, service_role;

alter role anon set statement_timeout = '3s';
alter role authenticated set statement_timeout = '8s';

Answer 3

@kachar's answer almost worked for me, below let me share the grants SQL that worked for me today

grant usage on schema local to postgres, anon, authenticated, service_role;
alter user supabase_admin SET search_path TO public, local, extensions; -- add all schemas to search path but don't include the "auth" schema

grant all privileges on all tables in schema local to postgres, anon, authenticated, service_role, supabase_admin;
grant all privileges on all functions in schema local to postgres, anon, authenticated, service_role, supabase_admin;
grant all privileges on all sequences in schema local to postgres, anon, authenticated, service_role, supabase_admin;

alter default privileges in schema local grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema local grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema local grant all on sequences to postgres, anon, authenticated, service_role;

alter default privileges for user supabase_admin in schema local grant all
    on sequences to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema local grant all
    on tables to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema local grant all
    on functions to postgres, anon, authenticated, service_role;

Answer 4

I've previously mangled my Supabase project by using the Flyway "clean" functionality ( which deletes all the stuff in a schema so you can run your migrations again).

You can see the discussion of it here: https://github.com/supabase/supabase/discussions/344#discussioncomment-182886

It seems the Supabase project schema has a bunch of Supabase-specific configuration in it and if you mess with it - weird things will happen.

You might want to look into if Prisma does any stuff to the schema that you didn't realise it was doing.

Answer 5

I was facing the same problem, and i just create an entire new project on Supabase and then "npx prisma migrate dev" to re generate my tables, and it works like a charm! I dont know when i broke my Supabase project. If you cannot re generate your tables because you have data on it, consider it migrate data to new project because it really works out!

Answer 6

Please don't just run a Grant All Privileges to every type of possible user access to your database.

The access needed depends on what the sql is doing. Ie, If only need to select and insert then only provide those privileges, concerning if giving everyone access to truncate, and delete tables in your schema...

I was having the issue of using supabase.rpc() to run some postgres functions. This function would select some data from a table, update a table, then insert into a new table.

First error I had was

{
 code: '42501',
  details: null,
  hint: null,
  message: 'permission denied for schema public'
}

Which was resolved with

grant usage on schema "public" to anon;
grant usage on schema "public" to authenticated;

That then lead to this error

   {
  code: '42501',
 details: null,
  hint: null,
  message: 'permission denied for table xxx'
 }

Which that was resolved with

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "public" TO authenticated;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "public" TO anon;

See below for some sources (because thats what 9th grade english class taught me to do)

Github issue

Docs about Grants