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

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

Answer 2

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