When you build an application with Supabase, your frontend code talks straight to your PostgreSQL database using an anonymous public key. There is no middleman API server intercepting requests to check permissions. Instead, database security relies entirely on Row Level Security (RLS). If you don't configure your rules correctly, anyone can read, update, or delete records across your entire database.
At its core, an RLS policy acts as an implicit WHERE clause that PostgreSQL appends to every query hitting a table. When a user requests data, the database evaluates these rules against the incoming session. If a row fails the condition, it vanishes from the result set as if it never existed. Mastering how these rules execute prevents data leaks and keeps your application secure without adding bloat to your server code.
Understanding the Two-Layer Access Model
Many developers stumble because they confuse table-level permissions with row-level policies. Securing a table requires navigating two distinct authorization checkpoints: Grants and Policies. Both must allow access before any data moves.
Grants decide whether a database role can touch a table at all. By default, when you create a new table through the Supabase Dashboard, the system often automatically grants select, insert, update, and delete privileges to the anon and authenticated roles. BBC has provided coverage on this fascinating subject in extensive detail.
Policies step in afterward to filter which specific rows that role can interact with. Turning on RLS makes a table default-deny, but it doesn't strip away those underlying table grants. Clean up your permissions by revoking default access first, then granting only what your application needs:
alter table public.notes enable row level security;
-- Revoke blanket permissions
revoke all on table public.notes from anon, authenticated;
-- Grant explicit base table permissions
grant select, insert on table public.notes to authenticated;
Once grants are in place, policies control the granular row filtering.
Writing USING and WITH CHECK Expressions
Every policy uses conditional expressions to evaluate data access. Depending on the operation you want to restrict, you will write a USING clause, a WITH CHECK clause, or both.
The USING expression filters existing rows. It dictates which records a user can see during a SELECT, modify during an UPDATE, or target during a DELETE. If USING evaluates to false for a given row, it remains hidden or untouchable.
The WITH CHECK expression validates new or modified data. It runs during INSERT statements and the final validation pass of UPDATE statements. If a user tries to insert a row where the user_id does not match their own session ID, WITH CHECK catches the violation and rejects the transaction.
Here is how you apply these clauses to let users manage only their own notes:
create policy "Users manage own notes"
on public.notes
as restrictive
for all
to authenticated
using (auth.uid() = user_id)
with check (auth.uid() = user_id);
When building update policies, remember that PostgreSQL evaluates both clauses. An update statement requires a USING clause to find the target row and a WITH CHECK clause to ensure the newly modified row doesn't break ownership rules. If you omit the WITH CHECK clause on an update policy, PostgreSQL defaults to reusing the USING expression.
Mapping Requests to Postgres Roles
Supabase maps incoming API requests to specific database roles based on the JSON Web Token (JWT) provided in the request header. Understanding these roles helps you scope your rules accurately:
anon: Used when no user is signed in. Requests carry the public anonymous API key. Use this role to restrict public tables, such as reading published blog posts.authenticated: Used when a valid user session exists. The request includes the user's access token, giving you access to helper functions likeauth.uid().service_role: Used exclusively by backend environments (like serverless functions or webhooks) using your secret service key. This role bypasses RLS entirely, making it ideal for administrative operations or payment processing logic.
Always specify the target role using the TO clause in your policy definition. Omitting it applies the rule to every role, including anonymous visitors, which can accidentally expose sensitive data.
Common Security Mistakes That Leak Data
Even experienced developers make configuration errors that expose private records. Review these common pitfalls to keep your database airtight.
Allowing Privilege Escalation via Columns
Policies protect entire rows, not individual columns. If you write a policy allowing users to update their own profile row, and your profile table contains a column named is_admin or role, a user can send an update payload changing their own role from user to administrator.
To prevent this, never store security roles in columns that users can directly update. Move sensitive attributes into a separate table that only server-side logic can write to, or store authorization data inside Supabase's secure app_metadata JSON object instead of raw_user_meta_data.
Trusting User Metadata Blindly
Never use raw_user_meta_data for authorization decisions. Users can manipulate metadata fields during sign-up or via client-side updates. Always rely on auth.uid() or server-validated claims housed in secure schema tables.
Forgetting to Index Policy Columns
PostgreSQL evaluates row-level policies for every candidate row matching your query. If your policy filters by user_id, team_id, or organization_id, and those columns lack indexes, your queries will trigger sequential table scans. As your database grows, performance will degrade rapidly. Add explicit database indexes to every foreign key or identifier referenced in your policies.
Optimizing Policy Performance
Slow database queries often trace back to poorly optimized policy functions. When you call auth.uid() inside a policy without wrapping it, PostgreSQL may re-evaluate that function for every single candidate row.
You can optimize performance by wrapping stable functions in a subquery or parentheses so PostgreSQL evaluates them only once per statement:
create policy "Fast team access"
on public.documents
for select
to authenticated
using (team_id in (
select team_id
from public.memberships
where user_id = (select auth.uid())
));
Enclosing auth.uid() inside (select auth.uid()) tells the query planner that the result doesn't change per row, turning a heavy row-by-row execution into a fast, cached lookup.
Frequently Asked Questions
Why does my query work in the Supabase Dashboard SQL Editor but fail in my app?
The SQL Editor runs queries under the postgres database role, which bypasses RLS by default. A query succeeding in the dashboard proves nothing about what an authenticated or anonymous user can do. Always test your application logic using standard client tokens or by impersonating specific users.
Can I restrict access to specific columns using RLS?
No. RLS operates strictly at the row level. If you need to hide specific columns from certain users, revoke column privileges using GRANT and REVOKE, isolate sensitive data into a separate child table, or expose a secure view with security_invoker enabled.
How do I write a policy for public read access?
To let anyone view specific rows without logging in, target the anon role and use a condition that evaluates to true:
create policy "Public read published posts"
on public.posts
for select
to anon, authenticated
using (is_published = true);
What is the best way to test my security policies?
You can write automated integration tests using the official Supabase CLI testing tools and pgTAP. Alternatively, perform a manual security audit by opening your frontend application in a browser privacy window, signing up as a fresh user, and attempting to fetch resources or update records belonging to other accounts.
:format(jpeg):mode_rgb():quality(90)/discogs-images/R-10138138-1638401487-1709.jpeg.jpg)