How to Grant Access to Specific Tables in a Schema in PostgreSQL (Supabase Fix)

I recently ran into a permission error when trying to access a table in a schema on Supabase:

Error 42501: "Permission denied for [TABLENAME]"

Resulting in a 403 Forbidden error.

After some troubleshooting, I found that granting access to specific tables in the schema solved the issue. I'm sharing my solution here in case it helps you too!

Why Grant Access to Specific Tables?

By default, PostgreSQL lets you grant access to an entire schema, but that might not be ideal if you want to limit access to only certain tables.

I prefer a more fine-grained approach, where users or roles only get access to the tables they actually need.

Step 1: Check Current Table Permissions

Before making any changes, you can check the current privileges on a specific table using this SQL query:

SELECT grantee, privilege_type  
FROM information_schema.role_table_grants  
WHERE table_name = 'mytable';

This helps ensure you're not granting unnecessary permissions or overriding existing ones.

Step 2: Grant Access to a Specific Table

To grant SELECT, INSERT, UPDATE, and DELETE privileges on a specific table, use the following SQL command:

GRANT SELECT, INSERT, UPDATE, DELETE ON mytable TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON mytable TO anon;

This allows only the specified users or roles to access the table while keeping other tables in the schema restricted.

Problem Solved!

After running this command, my issue was resolved. If you're facing the same error in Supabase (or PostgreSQL in general), this should work for you too!

Your Thoughts?

Have you encountered similar permission issues in PostgreSQL or Supabase? Let me know how you handle table-level access in the comments!