The Day PostgreSQL Row-Level Security Broke My API

I love PostgreSQL. It's powerful, reliable, and has features that MongoDB users can only dream about.

But one feature almost cost me a client.

Row-Level Security (RLS).

The "Smart" Decision

I was building a multi-tenant SaaS with the PERN stack:

• PostgreSQL (with Prisma ORM)
• Express.js
• React
• Node.js

To keep tenant data separate, I decided to use PostgreSQL RLS instead of filtering in application code.

"More secure," I thought. "The database will handle everything automatically."

I set up RLS like this:

-- Enable RLS on all tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policy for projects
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Same for tasks and users...

And in my Prisma middleware, I set the tenant context:

prisma.$use(async (params, next) => {
  const tenantId = getCurrentTenantId(); // From JWT
  await prisma.$executeRaw`SET app.current_tenant_id = ${tenantId}`;
  return next(params);
});

Beautiful. Elegant. Wrong.

The Disaster

One morning, users started reporting:

"My projects are gone!" "I had 50 tasks yesterday, now I see nothing!" "Did someone delete my account?"

I checked the database directly:

SELECT COUNT(*) FROM projects WHERE tenant_id = 'user-tenant-id';
-- Returns: 47 projects

Then I checked the API:

curl https://api.myapp.com/projects
# Response: []

Empty array.

The data was there. The API was working. What happened?

Debugging the Nightmare

I spent 4 hours checking:

• API routes
• Authentication middleware
• Prisma queries
• Database connections

Nothing.

Finally, I connected directly to PostgreSQL and ran the same query the API was using:

SELECT * FROM projects;
-- Returns: 0 rows

Wait, what?

I ran it again with a different user:

SET app.current_tenant_id = 'some-tenant-id';
SELECT * FROM projects;
-- Returns: projects from THAT tenant only

Then it hit me.

The tenant ID wasn't being set correctly in the API.

But I had middleware that set it on every request. How could it fail?

The Real Bug

I found the issue in my authentication flow:

// My middleware (WRONG)
app.use(async (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (token) {
    const decoded = jwt.verify(token, SECRET);
    req.tenantId = decoded.tenantId;
    
    // Set for Prisma
    await prisma.$executeRaw`SET app.current_tenant_id = ${req.tenantId}`;
  }
  next();
});

The problem? Prisma connection pooling.

Prisma reuses database connections across multiple requests. When I set app.current_tenant_id on a connection, it persisted for the NEXT request too — but only if that request reused the same connection.

So:

• Request 1 (Tenant A) → Sets tenant_id = A → Works fine
• Request 2 (Tenant B) → Reuses same connection → tenant_id is STILL A → Sees NO data

Some users saw nothing. Others saw OTHER tenants' data.

This was a security breach.

The Fix

I learned that PostgreSQL session variables are connection-scoped. With connection pooling, you MUST reset them every time.

The correct approach:

// Use a connection pool that resets state
const resetAndSetTenant = async (tenantId) => {
  // Reset any existing setting
  await prisma.$executeRaw`RESET app.current_tenant_id`;
  // Set new value
  await prisma.$executeRaw`SET app.current_tenant_id = ${tenantId}`;
};

// Then in middleware
app.use(async (req, res, next) => {
  const tenantId = extractTenantFromToken(req);
  await resetAndSetTenant(tenantId);
  next();
});

But even better? Don't use RLS with Prisma's default connection pool.

I switched to a different pattern:

// Filter in application code instead
const getProjects = async (tenantId) => {
  return prisma.project.findMany({
    where: { tenantId: tenantId }
  });
};

It's not as "elegant," but it's predictable. No connection state issues. No cross-tenant data leaks.

What I Learned

1. RLS + Connection Pooling = Danger

PostgreSQL session variables don't automatically reset. If you use RLS, you need connection-level isolation or explicit reset on every request.

2. Test with Multiple Concurrent Users

My tests only used one user at a time. The bug only appeared under real load.

3. Security Features Can Create Security Holes

RLS is supposed to protect data. Improper implementation can expose it.

The Audit

After fixing, I ran a security audit:

-- Check if any user accessed wrong tenant data
SELECT 
  p.tenant_id as actual_tenant,
  u.email,
  a.action,
  a.timestamp
FROM audit_log a
JOIN projects p ON a.project_id = p.id
JOIN users u ON a.user_id = u.id
WHERE p.tenant_id != u.tenant_id;

Thankfully, the bug only lasted 2 hours and only affected "view" operations. No data was modified across tenants.

But I had nightmares for a week.

Better RLS Pattern (If You Must Use It)

// Create a dedicated pool for each tenant
const pools = new Map();

const getTenantPool = (tenantId) => {
  if (!pools.has(tenantId)) {
    pools.set(tenantId, new PrismaClient({
      datasources: { db: { url: getConnectionStringWithTenant(tenantId) } }
    }));
  }
  return pools.get(tenantId);
};

// Or use pgBouncer transaction pooling (not session pooling)

Commands That Help

Check current RLS policies

SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies;

Temporarily disable RLS (emergency only!)

ALTER TABLE projects DISABLE ROW LEVEL SECURITY;
-- Do what you need
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

View current session variables

SELECT name, setting FROM pg_settings WHERE category LIKE 'Customized%';

Conclusion

PostgreSQL is incredible. RLS is powerful.

But with great power comes great responsibility — and great foot-guns.

Now I have a rule: Never use session variables with connection pooling unless you fully understand the isolation model.

And I always test with 10 concurrent users before deploying auth-related features.

That "elegant" RLS solution cost me 8 hours of debugging and one very angry client email.

Worth it? Never again.