Most Supabase RLS examples are missing the hard parts: multi-tenant org checks, helper functions to prevent policy drift, and the performance traps that kill queries in production.
Supabase Row-Level Security is Postgres access control baked into the database itself. Write a policy once, and every query — whether from your app, a webhook, or a third-party integration — respects it. But 90% of tutorials show toy policies: `auth.uid() = user_id`. In production, you have multi-tenant SaaS, team members with different roles, service-role bypass for admin tasks, and queries that break if the policy logic lives in WHERE clauses instead of subqueries. Velocity's multi-product SaaS uses RLS heavily across users, teams, projects, and API keys. The difference between a secure, performant RLS setup and one that leaks data or times out is structural. Here's the real pattern: multi-tenant `organization_id` checks with helper functions, service-role override for admin flows, 4 policy templates for select/insert/update/delete, and how to avoid the performance cliffs that catch most teams after launch.
Why Naive RLS Breaks in Production
The classic toy policy: `create policy "users can see their own rows" on users for select using (auth.uid() = id);`. This works for single-tenant apps where each user is isolated. The moment you add teams — where User A and User B both belong to Organization X and need to see the same records — the policy collapses. You write `(auth.uid() in (select user_id from team_members where org_id = ...))` and the policy now runs a subquery on every row. If users table has 100k rows and the subquery joins three tables, you've just made every SELECT a 30-second timeout. The second break: admin workflows. You need service role to backfill data, fix corruption, or run exports without hitting RLS. Third: most tutorials don't show policy versioning. You change a policy, but the old one is still active, creating security gaps. Fourth: people put logic in WHERE clauses instead of inline SQL, duplicating the same check across five different policies and drifting as the org grows.
Supabase RLS is powerful because it's transparent — every query is subject to it, even dashboard admin queries, even CLI exports. But that also means it's dangerous: one wrong policy and you've locked yourself out of your own data, or worse, leaked customer data to the wrong user. The patterns below prevent that.
The Multi-Tenant Pattern: organization_id + Helper Functions
Every production SaaS table needs an `organization_id` column and a helper function that returns the current user's org ID. The helper function is your single source of truth for "what org does this request belong to?" Every policy references the helper, so if org logic changes, you update one place.
-- Helper function: return current user's organization ID
create or replace function auth.org_id() returns uuid as $$
select org_id from users where id = auth.uid()
$$ language sql stable security definer;
-- Grant execute permission to authenticated users
grant execute on function auth.org_id() to authenticated;
-- Every table that's org-scoped includes org_id
alter table projects add column organization_id uuid not null references organizations(id) on delete cascade;
alter table api_keys add column organization_id uuid not null references organizations(id) on delete cascade;
alter table team_members add column organization_id uuid not null references organizations(id) on delete cascade;Now every policy starts with the same check: `organization_id = auth.org_id()`. This single condition, evaluated once per request, is far faster than a subquery and becomes the baseline for all access control.
Four RLS Policy Templates: Select / Insert / Update / Delete
Here are the four policies every org-scoped table needs. Each one prevents different kinds of abuse.
SELECT Policy: Users Can See Their Org's Data
create policy "org members can select their org's rows"
on projects
for select
using (organization_id = auth.org_id());
-- For tables with additional row-level checks (e.g., team members can see their projects):
create policy "users can select projects in their org and assigned teams"
on projects
for select
using (
organization_id = auth.org_id()
and (
created_by = auth.uid()
or id in (
select project_id from project_assignments
where user_id = auth.uid()
)
)
);INSERT Policy: Only Org Members Can Create
create policy "org members can insert rows in their org"
on projects
for insert
with check (
organization_id = auth.org_id()
and auth.uid() in (
select user_id from team_members where org_id = auth.org_id()
)
);
-- Restrict to specific roles (e.g., only admins can create high-level records):
create policy "only org admins can insert projects"
on projects
for insert
with check (
organization_id = auth.org_id()
and (
select role from team_members
where org_id = auth.org_id() and user_id = auth.uid()
) = 'admin'
);UPDATE Policy: Only Creators or Assigned Users
create policy "users can update their own rows"
on projects
for update
using (organization_id = auth.org_id() and created_by = auth.uid())
with check (
organization_id = auth.org_id()
and created_by = auth.uid()
);
-- Allow team members to update shared projects:
create policy "project owners and assignees can update"
on projects
for update
using (
organization_id = auth.org_id()
and (
created_by = auth.uid()
or id in (
select project_id from project_assignments where user_id = auth.uid()
)
)
)
with check (
organization_id = auth.org_id()
and (
created_by = auth.uid()
or id in (
select project_id from project_assignments where user_id = auth.uid()
)
)
);DELETE Policy: Only Admins or Creators
create policy "only org admins or creators can delete"
on projects
for delete
using (
organization_id = auth.org_id()
and (
created_by = auth.uid()
or (
select role from team_members
where org_id = auth.org_id() and user_id = auth.uid()
) = 'admin'
)
);Service Role Bypass for Admin Tasks
You need RLS to not apply to admin flows: backfills, data migrations, exports, or support tickets where staff needs to access customer data. Use the service-role key in your backend only, never expose it client-side.
// netlify/functions/admin-export.ts
import { createClient } from '@supabase/supabase-js';
// Service role bypasses RLS
const supabase = createClient(
process.env.SUPABASE_URL!,
process.env.SUPABASE_SERVICE_KEY! // Never expose this
);
export default async (event: any) => {
// Check request is authenticated as staff via JWT or IP allowlist
const authHeader = event.headers.authorization;
if (!authHeader?.startsWith('Bearer ')) {
return { statusCode: 403, body: 'Forbidden' };
}
// Service role can access any org's data
const { data, error } = await supabase
.from('projects')
.select('*')
.eq('organization_id', 'org-uuid-here');
if (error) {
return { statusCode: 500, body: JSON.stringify(error) };
}
return {
statusCode: 200,
headers: {
'Content-Type': 'text/csv',
'Content-Disposition': 'attachment; filename=export.csv'
},
body: convertToCsv(data)
};
};The service-role key does NOT respect RLS. Use it only for: - Admin backfills and migrations - Scheduled jobs (Postgres cron) that need cross-org access - Support tooling (on a separate, IP-protected endpoint) Never use service role in client-side code. Never commit it to git (use `.env` and Netlify secrets).
Performance Traps That Kill Queries in Production
Trap 1: Subqueries in SELECT Policies
Bad: `id in (select project_id from project_assignments where user_id = auth.uid())` evaluated for every row. On a table with 100k rows, this is 100k subqueries.
Better: Join in your SELECT query, not the policy. Let Postgres optimize the join. Keep policies simple: `organization_id = auth.org_id()`. If you need role-based logic, move it to application code or use a materialized view that pre-joins the data.
-- Slow: policy has subquery
create policy "slow_policy" on projects for select
using (id in (select project_id from project_assignments where user_id = auth.uid()));
-- Fast: join in the app query
-- In your app:
const { data } = await supabase
.from('projects')
.select(`
id, name,
project_assignments(user_id)
`)
.eq('organization_id', orgId)
.contains('project_assignments.user_id', [userId]);Trap 2: WHERE Clauses in Policies
Avoid `using` clauses that check `status = 'active'` or `created_at > now() - interval '30 days'`. These execute on every row. Use inline SQL or a single function call instead.
Trap 3: Recursive Org Checks Without Indexing
If `auth.org_id()` queries the users table without an index on `id`, every policy evaluation is a table scan. Index everything the helper function touches.
-- Make sure auth.org_id() is fast
create index idx_users_id_org_id on users(id, org_id);Trap 4: Policies That Change Based on Request Context
RLS is evaluated once per request. If your policy depends on a Postgres setting that changes mid-request, you'll have inconsistent behavior. Use `auth.uid()` and `auth.jwt()` (claims from your JWT), not custom settings that could be modified.
Disabling RLS Safely When You Need To
Sometimes you need to run a one-off query without RLS. Use session-level disable, never table-level disable in production.
-- One-off export: disable RLS for this session only
set local row security off;
select * from projects where organization_id = 'org-uuid';
set local row security on;Never run `alter table projects disable row security;` in production. That disables it for all users. Once you close the connection, it's still off for the next request.
Six FAQs
Can I preview what data a user will see before rolling out a policy?
Yes. Log in as that user (or create a test user in their org), then run the SELECT. Postgres will apply RLS as that user and show you exactly what they can access. In production, you'd use a support ticket interface that logs in as the user to verify their view. Never assume a policy works — test it as the user.
What if I need to update a policy but can't redeploy?
You can't. RLS policies are in the database schema. Update them with a Postgres migration in Supabase. Go to the SQL editor in the dashboard, create and test the migration, then deploy. Use Supabase CLI for local testing before pushing to production.
How do I know if a policy is slowing my queries?
Use Supabase's query performance dashboard or Postgres' `explain analyze`. Run your query as the user and check the plan. If you see "Seq Scan" across a large table, the policy doesn't have an index backing it. Add an index and retry.
Can I test RLS policies in local development?
Yes. Supabase CLI includes Postgres locally. Run `supabase start`, then create test users and policies in your local database. They work exactly the same as production. Always test RLS locally before deploying.
What if a user shouldn't be able to see a column in a row they have access to?
RLS is row-level, not column-level. For column-level security, use a view that selects only the columns the user should see, then apply RLS to the view. Or return the data and redact columns in application code. For sensitive data (like passwords), never store them in Postgres — hash and store only the hash.
How do I revoke access without deleting the user?
Remove them from the `team_members` table or update their `organization_id` to null. Postgres will then apply the RLS policy: they can't see rows for their old org because `organization_id = auth.org_id()` will be false. They're instantly locked out, no policy changes needed.
The Bottom Line
Supabase RLS is your most powerful security lever. Toy policies are fine for side projects; production SaaS needs the structure here: one helper function for org access, policies that use it, service role for admin tasks only, and performance testing on real data. The difference between "this query took 2 seconds" and "this query took 200 milliseconds" is often whether a subquery lives in the policy or in application code. Get the architecture right once — org_id in every table, a single `auth.org_id()` function, and policies that delegate heavy lifting to application queries — and you can scale to millions of rows without re-architecting RLS. Ready to build multi-tenant SaaS that's secure from day one? Check Aidxn Design pricing for backend partnerships. For more on webhook security and user provisioning, see Stripe webhooks on Netlify.