SupabaseS
Supabase•4mo ago
chrisb2244

Self-hosted roles: supabase_admin and postgres (and local dev supabase cli...), non-public schema

I'm hitting issues with permissions on my deployed, self-hosted database when using a non-public schema.

I created the schema with migrations like the following (referencing https://supabase.com/docs/guides/api/using-custom-schemas):

CREATE SCHEMA IF NOT EXISTS myschema;

-- Allow API access
GRANT USAGE ON SCHEMA myschema TO anon, authenticated, service_role;
GRANT ALL ON ALL TABLES IN SCHEMA myschema TO anon, authenticated, service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA myschema TO anon, authenticated, service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA myschema TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON TABLES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON ROUTINES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;


Seemingly, this works ok for my local development cases.
However, my deployed database has current_user as supabase_admin (whereas in my local dev studio, running select current_user returns postgres).

Since the tables are owned by supabase_admin in my deployed database, the ALTER DEFAULT PRIVILEGES command seems to be irrelevant (as I understand, it only grants privileges for objects created by the role that is specified, i.e. postgres).

(continued in comment)
Was this page helpful?