Data Leakage & Row-Level Security (RLS): What It Solves — and What It Doesn't

When One Missing Filter Becomes a Real Problem

When One Missing Filter Becomes a Real Problem

Let's be honest — this kind of issue happens more often than teams like to admit.

You write a query, everything looks fine, it passes code review… but one small detail is missing:

WHERE user_id = ...

That's it. That's the bug.

In a multi-tenant system, that single omission can expose data between users. Not always millions of records, not always system-wide — but enough to be serious. Sometimes it's just one endpoint. Sometimes only certain users notice. But the risk is real.

And here's the important part: this usually isn't caused by hackers. It's caused by normal development mistakes.

The deeper problem isn't the missing filter. It's this:

The system relies entirely on the application layer to enforce data access.

That's where Row-Level Security (RLS) comes in.

So, What Is Row-Level Security — Really?

Think of RLS as moving part of your authorization logic into the database.

Normally, your app does this:

• Check if user is logged in
• Run a query with filters

But the database itself doesn't "know" which user owns which data. It just executes whatever query it receives.

With RLS, the database starts enforcing rules like:

"This row belongs to user X. Only user X can see it."

So even if your query forgets the filter… the database will still refuse to return unauthorized rows.

That's the key shift:

• Without RLS → the app is responsible
• With RLS → the database enforces it too

And no — this doesn't replace your backend logic. It backs it up.

Why Most Teams Don't Use RLS

If it's this useful, why isn't everyone using it?

A few very practical reasons:

• It's unfamiliar Most developers are trained to handle access control in the backend, not the database
• It's not plug-and-play You need a way to pass user context (like user_id) into the database session
• ORMs give a false sense of safety Tools like Prisma or Sequelize make queries cleaner — but they don't prevent logical mistakes
• It feels like overkill For small or simple apps, teams often skip it

All of that is understandable. But in multi-tenant systems, skipping RLS is a calculated risk.

Step 1 — Turn It On (and Use the Right Role)

Let's start simple.

You have a table like this:

CREATE TABLE documents (
  id         UUID PRIMARY KEY,
  user_id    UUID NOT NULL,
  title      TEXT,
  content    TEXT
);

Now enable RLS:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

At this point, something important happens:

If you don't define any policies, no data is accessible at all.

That's intentional. PostgreSQL defaults to deny all.

Also — don't connect your app using a superuser. Superusers can bypass RLS entirely, which defeats the purpose.

Step 2 — Tell the Database Who the User Is

This is where things usually get confusing — but it doesn't have to be.

You pass the current user's ID into the database session.

Then you write a policy like this:

CREATE POLICY user_isolation_policy
ON documents
FOR SELECT
TO app_user
USING (
  user_id = current_setting('app.current_user_id')::UUID
);

What this means in plain terms:

"Only return rows where user_id matches the current session's user."

Now from your application (example in Node.js):

await client.query(
  'SET LOCAL app.current_user_id = $1',
  [userId]
);

After that, this query:

SELECT * FROM documents;

…will automatically return only that user's data.

No WHERE clause needed. The database enforces it.

Step 3 — Actually Test It (Don't Skip This)

This is where many teams get into trouble.

They define policies… and assume everything is fine.

You need to simulate real conditions.

For example:

SET ROLE app_user;
SET app.current_user_id = 'some-uuid';
SELECT * FROM documents;

Now check:

• Do you only see your own data?
• What happens if the user ID is missing?

Try this:

RESET app.current_user_id;
SELECT * FROM documents;

A safe setup should either:

• return no rows, or
• fail safely

You can enforce that with:

USING (
  user_id = NULLIF(current_setting('app.current_user_id', true), '')::UUID
)

If the context isn't set → access is denied.

Real-World Case: Organization-Level Access

In most SaaS apps, access isn't just per user — it's per organization.

So instead of:

"user can see their own data"

It becomes:

"users can see data within their organization"

Example:

CREATE POLICY org_policy
ON projects
FOR SELECT
TO app_user
USING (
  org_id = current_setting('app.current_org_id')::UUID
);

And maybe:

CREATE POLICY owner_policy
ON projects
FOR UPDATE
TO app_user
USING (
  owner_id = current_setting('app.current_user_id')::UUID
);

Now you have layered control:

• org-level visibility
• user-level ownership

What RLS Does NOT Protect You From

This part matters.

RLS is powerful — but it's not magic.

It won't protect you from:

• Superuser access They bypass everything
• SQL injection (if you're careless) Especially if attackers can manipulate session variables
• Direct DB access by developers/admins If roles aren't restricted
• Broken business logic in your app RLS doesn't replace validation
• Performance issues Every query now includes additional filtering

About Performance (Let's Be Realistic)

Yes, RLS adds overhead.

But in most real systems:

• If your policy is simple (user_id = ...)
• And you index the column properly

→ the impact is usually small and acceptable

Problems only show up when:

• policies are complex
• indexes are missing

The Bigger Picture: Don't Rely on One Layer

Here's the takeaway.

RLS is not a replacement for your backend logic.

It's a second line of defense.

A solid setup looks like this:

• Backend → filters data explicitly
• Database (RLS) → enforces it again
• Roles → limit access
• Logging → helps detect issues

Because in real systems:

Bugs happen. Queries get copied. Filters get missed.

RLS is there for the moment that happens.

Simple Checklist You Can Use

• Enable RLS
• Don't use superuser for your app
• Define USING and WITH CHECK policies
• Pass user context via SET LOCAL
• Test edge cases (especially missing context)
• Index your policy columns (user_id, org_id)