Supabase•3w ago

"Permission denied for table" despite having RLS policies. Requires explicit GRANT

So this has never been a problem until today. I just created a table with RLS and all the appropriate policies as I have done before, but kept getting "permission denied for table" when trying to select from it. I find I can only select when I do so under the "postgres" role, and upon running this:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = <table_schema> AND table_name = <table_name>;

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = <table_schema> AND table_name = <table_name>;

I see that only the "postgres" role has privileges on the table, regardless of the RLS policies. I can "fix" this by doing

GRANT SELECT ON TABLE <table_name> TO <role_name>;

GRANT SELECT ON TABLE <table_name> TO <role_name>;

but I've never had to do this before and it seems odd that I would need to run it on every table. My old tables work just fine without this, so I'm wondering if Supabase changed something? I also should clarify that the schema of these tables are exposed, and in my case I'm trying to make it so that only authenticated users can select from a given table. Happy to provide more details on this, and thanks in advance. EDIT: Forgot to mention but this occurs even if I make a brand new table. EDIT: Following up myself, I assume doing

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "<schema>" TO authenticated;

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "<schema>" TO authenticated;

would solve my problem from here on out (though I can't figure out when or why I managed to disable permissions for all non-postgres roles). That said, even if I did do this, am I correct in assuming an RLS policy would override this? (i.e. making an RLS policy that explicitly allowed only the postgres role to read from a specific table in that schema would make the "authenticated" role to not read from the table?

5 Replies

garyaustin•3w ago

Is this in the public schema? Public schema is setup with grants for anon/authenticated/service_role. Custom schemas you have to take care of that. https://supabase.com/docs/guides/api/using-custom-schemas

Tenkai 2.0OP•3w ago

I just tested it and this only happens on tables in my non-public schema. The public schema doesn't run into this problem. I must have disabled this at some point on the schema I'm working with.

garyaustin•3w ago

Not sure. The guide shows what you need to run for it to work like public. You can certainly limit it by removing or adding roles.

Tenkai 2.0OP•3w ago

I have my schemas exposed but I suppose I need to re-run step 2 in your link. I also just tested it with RLS policies and it seems the RLS policy overrides the GRANT operator (e.g. only allowing the "anon" role to select from a table means non-anon roles can select from it, despite what permissions were granted). Thank you, I'll mark it as solved.

garyaustin•3w ago

RLS policies can limit more but never allow access if a grant does not .

Gaming

Programming

"Permission denied for table" despite having RLS policies. Requires explicit GRANT

Did you find this page helpful?