How to Implement Row-Level Security in Postgres (2026)

Row-Level Security (RLS) is Postgres's built-in authorization system that lets you enforce data access rules at the database level — not in your application code. Users can only see and modify rows they're allowed to touch, even if they somehow bypass your API.

This guide covers RLS from zero to production-ready, including multi-tenant patterns and Supabase-specific tips.

Why Row-Level Security?

Traditional approach: Check permissions in application code:

```typescript // ❌ Permission logic scattered across routes app.get('/api/documents', async (req, res) => { const userId = req.user.id const docs = await db.query( 'SELECT * FROM documents WHERE user_id = $1', [userId] ) res.json(docs) }) ```

Every endpoint needs permission checks. Miss one and you've got a data leak.

RLS approach: Database enforces permissions:

```sql -- ✅ Users automatically only see their own documents CREATE POLICY user_documents ON documents FOR SELECT USING (user_id = current_user_id()); ```

Now SELECT * FROM documents only returns rows the current user owns. Forget permission checks and the database protects you.

Enabling RLS

Step 1: Enable RLS on a Table

```sql ALTER TABLE documents ENABLE ROW LEVEL SECURITY; ```

Important: Once enabled, the table becomes invisible to everyone (including superusers in some contexts) until you create policies.

Step 2: Create Your First Policy

```sql -- Users can see their own documents CREATE POLICY user_select_own_documents ON documents FOR SELECT USING (user_id = auth.uid()); ```

Breaking it down:

Policy name: user_select_own_documents (descriptive name)
Table: documents
Operation: FOR SELECT (read access)
Condition: USING (user_id = auth.uid()) (only rows where user_id matches)

Step 3: Set the User Context

RLS needs to know who the current user is. Two patterns:

Supabase: Automatically sets auth.uid() from JWT tokens Custom apps: Use SET LOCAL:

```sql BEGIN; SET LOCAL app.current_user_id = '123'; SELECT * FROM documents; -- Only sees user 123's documents COMMIT; ```

Common RLS Patterns

Pattern 1: User Owns Row

```sql -- Users can read/write their own todos CREATE POLICY user_crud_own_todos ON todos FOR ALL USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid()); ```

USING: Read permission (SELECT, UPDATE, DELETE)
WITH CHECK: Write permission (INSERT, UPDATE)

Pattern 2: Multi-Tenant (Organization-Based)

```sql -- Users can access data from their organization CREATE POLICY org_members_access ON documents FOR SELECT USING ( org_id IN ( SELECT org_id FROM user_orgs WHERE user_id = auth.uid() ) ); ```

Pattern 3: Role-Based Access

```sql -- Admins see everything, users see their own CREATE POLICY documents_access ON documents FOR SELECT USING ( user_role() = 'admin' OR user_id = auth.uid() );

-- Helper function CREATE FUNCTION user_role() RETURNS TEXT AS $$ SELECT role FROM users WHERE id = auth.uid(); $$ LANGUAGE SQL STABLE; ```

Pattern 4: Public + Private Rows

```sql -- Everyone sees public posts, only owner sees private CREATE POLICY posts_select ON posts FOR SELECT USING ( is_public = true OR user_id = auth.uid() ); ```

Pattern 5: Join-Based Permissions

```sql -- Users see documents shared with them CREATE POLICY shared_documents ON documents FOR SELECT USING ( EXISTS ( SELECT 1 FROM document_shares WHERE document_id = documents.id AND user_id = auth.uid() ) ); ```

Operation-Specific Policies

Create different rules for different operations:

```sql -- Anyone can view published articles CREATE POLICY articles_select ON articles FOR SELECT USING (status = 'published' OR author_id = auth.uid());

-- Only authors can insert CREATE POLICY articles_insert ON articles FOR INSERT WITH CHECK (author_id = auth.uid());

-- Only authors can update their own CREATE POLICY articles_update ON articles FOR UPDATE USING (author_id = auth.uid()) WITH CHECK (author_id = auth.uid());

-- Only authors can delete CREATE POLICY articles_delete ON articles FOR DELETE USING (author_id = auth.uid()); ```

Performance Considerations

Index Your RLS Columns

```sql -- If policies filter by user_id, index it CREATE INDEX idx_documents_user_id ON documents(user_id); ```

Without this index, RLS policies do full table scans.

Use STABLE/IMMUTABLE Functions

```sql -- ❌ VOLATILE (default) — recalculated for every row CREATE FUNCTION auth.uid() RETURNS UUID AS $$ -- ... $$ LANGUAGE SQL;

-- ✅ STABLE — calculated once per query CREATE FUNCTION auth.uid() RETURNS UUID AS $$ -- ... $$ LANGUAGE SQL STABLE; ```

Supabase's auth.uid() is already STABLE.

Avoid Complex Subqueries

Complex RLS policies can slow down queries significantly:

```sql -- ❌ Slow: Subquery runs for every row CREATE POLICY slow_policy ON documents USING ( id IN ( SELECT document_id FROM permissions WHERE user_id = auth.uid() ) );

-- ✅ Fast: Join-based with proper indexes CREATE POLICY fast_policy ON documents USING ( EXISTS ( SELECT 1 FROM permissions WHERE permissions.document_id = documents.id AND permissions.user_id = auth.uid() ) ); ```

Ensure permissions.document_id and permissions.user_id are indexed.

Debugging RLS

Check Active Policies

```sql SELECT * FROM pg_policies WHERE tablename = 'documents'; ```

Test as Different Users

```sql -- Simulate user 123 SET LOCAL app.current_user_id = '123'; SELECT * FROM documents; -- See what user 123 sees

-- Simulate user 456 SET LOCAL app.current_user_id = '456'; SELECT * FROM documents; -- See what user 456 sees ```

Bypass RLS (Admin Access)

```sql -- Temporarily disable RLS for debugging SET LOCAL row_security = off; SELECT * FROM documents; -- See ALL rows ```

Warning: Only use this in development or admin tools with explicit authorization.

Supabase-Specific Tips

Use auth.uid()

Supabase automatically extracts user ID from JWT:

```sql CREATE POLICY supabase_policy ON todos FOR ALL USING (user_id = auth.uid()); ```

Use auth.jwt()

Access custom JWT claims:

```sql CREATE POLICY org_policy ON documents FOR SELECT USING ( org_id = (auth.jwt() -> 'app_metadata' ->> 'org_id')::uuid ); ```

Realtime Requires RLS

Supabase Realtime only broadcasts rows users can see via RLS. Enable RLS on all Realtime-enabled tables.

Common Pitfalls

Forgetting WITH CHECK: Policies without WITH CHECK allow reads but block writes
No policy = no access: After enabling RLS, create at least one policy or the table is invisible
Superuser bypass: postgres role typically bypasses RLS. Use service roles carefully.
Missing indexes: RLS policies that filter unindexed columns kill performance
Complex logic in policies: Keep policies simple. Move complex authorization to views/functions.

The Bottom Line

RLS moves security from application code (where it's easy to forget) to the database (where it's enforced automatically). For multi-tenant SaaS apps, it's the difference between "we check permissions in code" and "it's impossible to access other users' data."

Use RLS for authorization. Use application code for business logic.

FAQ

Does RLS work with ORMs like Prisma?

Yes. Prisma executes SQL, and Postgres enforces RLS. Just set the user context before queries (Supabase does this automatically).

Can I use RLS for multi-tenant apps?

Absolutely. RLS is perfect for multi-tenant apps. Filter by org_id or use a join to a user_orgs table.

Does RLS impact performance?

Yes, if policies aren't indexed or involve complex subqueries. Properly indexed RLS policies have minimal overhead.

Can I test RLS locally?

Yes. Use Docker to run Postgres locally. Supabase offers a local development CLI that includes RLS support.