Supabase client permission denied for schema public

user_7431447 0
Viewed 3409

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.

supabase prisma-graphql prisma postgresql
4 Answers

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
user_6682450

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.

user_9245970

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!

user_138104230

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