How to Set Up Row-Level Security (RLS) for Database Access in Your App

Mar 21, 2026

Problem

When I built a multi-user application, I got a scary thought: What if my application code has a bug that lets users see each other’s data?

I was filtering by user_id in every query. But then I realized something:

If I forget ONE user_id filter in ONE endpoint, users can see ALL data.

This is a real problem. Application-level security has critical weaknesses:

A single bug can expose all data
Direct database access bypasses app logic entirely
Every query must manually include user filtering
SQL injection attacks can bypass checks
Admin tools and analytics panels need their own security

I needed a solution that works even when my code fails. That’s when I discovered Row-Level Security (RLS).

What is Row-Level Security?

Row-Level Security is a database feature that restricts which rows users can access at the database level. It works regardless of how the data is accessed - through my app, through admin tools, or even direct SQL queries.

The key insight is:

Traditional security: Trust application to filter data
RLS security: Database enforces filtering automatically

I found this quote from a Reddit discussion that explains it well:

“Users should only be able to see and edit their own data. Nothing else. RLS rules handle this and you can literally ask the AI to write them based on your schema.”

This means even if my application code has bugs, the database itself prevents unauthorized access.

What I Tried First

Before RLS, I was doing this in every query:

// My old approach - filtering in application code
async function getUserPosts(req, res) {
  const userId = req.user.id;

  // I had to remember this filter EVERY time
  const posts = await db.query(
    'SELECT * FROM posts WHERE user_id = $1 ORDER BY created_at DESC',
    [userId]
  );

  res.json(posts.rows);
}

This works, but I realized the problem:

What if I forget the WHERE clause?
What if someone accesses the database directly?
What if SQL injection bypasses my filter?

I needed a better approach.

The Solution: Enable RLS in PostgreSQL

PostgreSQL has built-in RLS support. Here’s how I set it up.

Step 1: Enable RLS on the Table

First, I enabled RLS on my posts table:

-- Enable Row Level Security on the posts table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

After running this, I got an unexpected result:

All access to the table was blocked!

This is because enabling RLS without any policies blocks all access by default. I needed to create policies next.

Step 2: Create RLS Policies

I created policies that let users see and modify only their own data:

-- Users can only view their own posts
CREATE POLICY "Users can view own posts"
ON posts FOR SELECT
USING (user_id = current_setting('app.current_user_id')::uuid);

-- Users can only insert their own posts
CREATE POLICY "Users can insert own posts"
ON posts FOR INSERT
WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);

-- Users can only update their own posts
CREATE POLICY "Users can update own posts"
ON posts FOR UPDATE
USING (user_id = current_setting('app.current_user_id')::uuid)
WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);

-- Users can only delete their own posts
CREATE POLICY "Users can delete own posts"
ON posts FOR DELETE
USING (user_id = current_setting('app.current_user_id')::uuid);

The USING clause filters which rows can be seen or modified. The WITH CHECK clause validates new or updated rows.

Step 3: Set User Context in Application

Now I needed to pass the user context to the database. I did this using session variables:

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function getUserPosts(userId) {
  const client = await pool.connect();
  try {
    // Set the user context for RLS
    await client.query(`SET app.current_user_id = $1`, [userId]);

    // RLS automatically filters to user's posts
    // No WHERE clause needed!
    const result = await client.query('SELECT * FROM posts ORDER BY created_at DESC');
    return result.rows;
  } finally {
    client.release();
  }
}

Now when I query, RLS automatically filters the results. Even if I run SELECT * FROM posts, I only see my own posts.

Testing RLS

I tested to make sure RLS was working:

async function testRLS() {
  const client = await pool.connect();

  // User 1 context
  await client.query(`SET app.current_user_id = 'user-1-uuid'`);
  const user1Posts = await client.query('SELECT * FROM posts');
  console.log('User 1 sees:', user1Posts.rows.length, 'posts');

  // User 2 context
  await client.query(`SET app.current_user_id = 'user-2-uuid'`);
  const user2Posts = await client.query('SELECT * FROM posts');
  console.log('User 2 sees:', user2Posts.rows.length, 'posts');

  client.release();
}

Output:

User 1 sees: 5 posts
User 2 sees: 3 posts

This confirmed that each user only sees their own data.

Supabase RLS Example

I also tried this with Supabase, which makes RLS even easier with built-in authentication:

-- Enable RLS on profiles table
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Public profiles visible to everyone
CREATE POLICY "Public profiles are viewable by everyone"
ON profiles FOR SELECT
USING (is_public = true);

-- Users can view their own profile
CREATE POLICY "Users can view own profile"
ON profiles FOR SELECT
USING (auth.uid() = id);

-- Users can update their own profile
CREATE POLICY "Users can update own profile"
ON profiles FOR UPDATE
USING (auth.uid() = id);

The auth.uid() function automatically gets the current user ID from Supabase’s authentication system. No need to set session variables manually.

Handling Service Accounts

I discovered one issue: my background jobs couldn’t access any data because they don’t have a user ID.

I created a service account role that bypasses RLS:

-- Create a service account role
CREATE ROLE service_account;
GRANT ALL ON posts TO service_account;

-- Create policy for service accounts
CREATE POLICY "Service account has full access"
ON posts FOR ALL
TO service_account
USING (true)
WITH CHECK (true);

Now my background jobs use the service account role and can access all data.

Common Mistakes I Made

Mistake 1: Enabling RLS Without Policies

I ran this:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

And suddenly all access was blocked. The solution is to always create policies first, then enable RLS.

Mistake 2: Forgetting to Set Session Variables

I wrote queries without setting the user context:

// This returns NO rows if RLS is enabled!
const result = await client.query('SELECT * FROM posts');

I had to remember to set the session variable before every query.

Mistake 3: Overly Permissive Policies

I initially wrote:

-- BAD: Too permissive
CREATE POLICY "Users can modify posts"
ON posts FOR ALL
USING (user_id = current_setting('app.current_user_id')::uuid)
WITH CHECK (true);  -- This allows inserting ANY user_id!

The WITH CHECK (true) allows inserting rows with any user_id. The correct version checks both:

-- GOOD: Proper checks
CREATE POLICY "Users can modify posts"
ON posts FOR ALL
USING (user_id = current_setting('app.current_user_id')::uuid)
WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);

Using AI to Generate RLS Policies

I learned that AI assistants can generate RLS policies based on my schema. I gave this prompt:

"Create RLS policies for a 'comments' table where:
- Users can read all comments
- Users can only create/edit/delete their own comments
- Admins can do everything"

And got this result:

-- Comments are readable by all authenticated users
CREATE POLICY "Comments are readable by all authenticated users"
ON comments FOR SELECT
TO authenticated
USING (true);

-- Users can create own comments
CREATE POLICY "Users can create own comments"
ON comments FOR INSERT
TO authenticated
WITH CHECK (user_id = auth.uid());

-- Users can update own comments
CREATE POLICY "Users can update own comments"
ON comments FOR UPDATE
TO authenticated
USING (user_id = auth.uid());

-- Admins have full access to comments
CREATE POLICY "Admins have full access to comments"
ON comments FOR ALL
TO admin_role
USING (true)
WITH CHECK (true);

This saved me time and reduced errors.

Why This Matters

I think RLS provides defense in depth:

Application bugs can’t bypass security: Even if I forget a WHERE clause, RLS still filters the data
Zero-trust at database level: Every query is automatically filtered
Compliance requirements: Meets data isolation requirements for GDPR, HIPAA
Simpler application code: No manual user filtering in every query
Prevents data leaks: SQL injection or admin panel misconfigurations can’t expose data

Summary

In this post, I showed how to implement Row-Level Security at the database level. The key points are:

Enable RLS on tables with user-specific data
Create policies that filter rows based on current user
Set session variables to pass user context
Handle service accounts for background jobs
Use AI to generate policies based on your schema

RLS guarantees that users can only access their own data, even when application-level security fails. It’s an essential layer of protection for multi-tenant applications.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 PostgreSQL Row Security Policies
👨‍💻 Supabase RLS Documentation
👨‍💻 Reddit: Best Practices for Database Security

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!