The power of Row-Level Security

Row Level Security (RLS) is a database feature that lets you restrict access to specific rows in a table based on the current user's context. Instead of adding filters in your app logic, the database enforces access control, which means less risk, less mess, and more peace of mind.

💡 Why I started using It

In one of our SaaS projects, we had a typical scenario: users should only see their own data. Writing WHERE user_id = ... in every query or GraphQL resolver quickly became painful and risky.

So we tried enabling RLS. Result? Less boilerplate, safer queries, and no regrets.

🧱 Basic example: documents table

Let’s say we have a simple documents table where each row belongs to a specific user:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    owner_id UUID NOT NULL,
    content TEXT NOT NULL
);

✅ Step 1: Enable RLS

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

✅ Step 2: Add a Policy

CREATE POLICY user_is_owner ON documents
  USING (owner_id = current_setting('app.current_user_id')::uuid);

✅ Step 3: Set Session Context

SET app.current_user_id = 'user-uuid-from-auth';

From now on, any query like SELECT * FROM documents will automatically return only rows owned by the current user.

🔐 Supabase Example (Even Cleaner)

Using Supabase? It’s even easier, thanks to built-in auth context and JWT integration:

CREATE POLICY "Users can access their own documents"
  ON documents
  FOR SELECT
  USING (owner_id = auth.uid());

No need to SET anything manually Supabase injects auth.uid() based on the authenticated user.

🛠️ Full CRUD Policy Set

Here’s what a complete RLS setup looks like for secure SELECT, INSERT, UPDATE, and DELETE:

CREATE POLICY select_policy ON documents
  FOR SELECT
  USING (owner_id = current_setting('app.current_user_id')::uuid);

CREATE POLICY insert_policy ON documents
  FOR INSERT
  WITH CHECK (owner_id = current_setting('app.current_user_id')::uuid);

CREATE POLICY update_policy ON documents
  FOR UPDATE
  USING (owner_id = current_setting('app.current_user_id')::uuid)
  WITH CHECK (owner_id = current_setting('app.current_user_id')::uuid);

CREATE POLICY delete_policy ON documents
  FOR DELETE
  USING (owner_id = current_setting('app.current_user_id')::uuid);

🧠 Lessons learned (so you don’t suffer)

• Enable RLS early, ideally before launch, so you don’t risk exposing data.
• Test with multiple roles and users to verify policies actually work.
• Keep policies simple, avoid expensive joins or subqueries in USING clauses.
• Log and inspect with EXPLAIN or Postgres logs when debugging.

And if you’re using an ORM like Prisma, double-check your queries! Some ORMs can unintentionally bypass RLS if you're not careful.

🎯 Why It Matters

RLS isn’t just a security feature, it’s a developer productivity booster:

• Less filtering logic in your app
• Less room for mistakes
• Consistent access rules across all queries
• Great for multi-tenant apps

🧩 Bonus: What About Performance?

A common concern is whether RLS slows things down. From what I’ve seen:

• For simple policies (user_id = ...), the overhead is negligible.
• Just make sure you have proper indexes on the columns used in your policies.

🏁 Wrapping Up

Row Level Security is one of those features that once you try, you wonder how you ever lived without it. It makes your DB smarter, your app cleaner, and your team safer.