How I Implement Row-Level Security with Postgres and Prisma

Last year, I was three weeks into a project for a Saudi construction client when I noticed their internal users could view data from rival companies. The problem? A lack of row-level policies in their PostgreSQL database. This wasn't some academic architecture flaw—it was actively leaking competitive data between contractors. We had to fix it overnight.

Row-level security (RLS) in Postgres is one of those things you can ignore until it bites you (and it will bite you). By the time this client asked for RLS, we’d already written 80% of the API with Prisma ORM. Retrofitting RLS into that setup taught me a few hard lessons about balancing database control with ORM convenience.

How Row-Level Security Actually Works (Without the Jargon)

PostgreSQL's RLS operates through policies. These policies say "User X can do Y on table Z only when condition Q is true." For example: a healthcare app I built for a Sharjah clinic has a policy that doctors can only edit patient records under their supervision.

Prisma complicates this slightly because it abstracts database queries. If you’re not careful, your Prisma operations might bypass Postgres policies. The solution? Use Prisma's client extensions or middleware to pass user context to the database. I’ll show you how shortly.

Here’s the bare minimum to enable RLS on a table:

ALTER TABLE "Invoice" ENABLE ROW LEVEL SECURITY;
CREATE POLICY invoice_access ON "Invoice" 
FOR ALL 
TO authenticated 
USING (CURRENT_USER = user_id::text);

This blocks non-owners from touching invoice records—assuming user_id matches your Postgres role name.

Implementing RLS in Practice

I used Prisma’s client-side extensions to inject user IDs from JWT claims:

1. Define policies in Prisma models using comments:

model Invoice {
  id Int
  user_id String
  /// @policy("CURRENT_USER = user_id::text")
  amount Decimal
}

1. Generate SQL policies through migrations. I automate this with a custom script that parses these comments and creates Postgres policies.

1. Pass user context via Prisma middleware. This middleware sets the current user before queries:

prisma.$use(async (params, next) => {
  if (params.model === 'Invoice') {
    params.args.where = { 
      ...params.args.where, 
      user_id: currentUser.id 
    };
  }
  return next(params);
});

The Ugly Details That Bite You Later

PostgreSQL's CURRENT_USER works great—until users switch roles. One of my Dubai clients had admins with temporary escalation privileges, but their RLS policies relied strictly on CURRENT_USER. Result? Admins couldn't moderate reports without breaking out a separate superuser role.

Here’s what I learned:

• •Don’t hardcode CURRENT_USER for admin overrides
• •Test policies with edge cases like suspended or ghost users
• •Use JSON claims in JWT tokens for complex roles, not just database roles

I once spent 4 hours debugging why a Laravel API call couldn't find records—turned out the Prisma client didn’t pass the expected user_id format. PostgreSQL policies silently rejected the queries.

Real-World Example: Tawasul Limo Booking Platform

For Tawasul Limo, I designed RLS policies to enforce driver/customer isolation:

model Ride {
  id Int 
  driver_id String
  customer_id String
  
  /// @policy("user_role = 'driver' AND driver_id = CURRENT_USER")
  /// @policy("user_role = 'customer' AND customer_id = CURRENT_USER")
}

This setup lets drivers see only their assigned rides while preventing customers from faking bookings under others' names. We stored user_role in a JWT claim and passed it via Prisma middleware.

We hit a snag when implementing ride cancellation—admins needed visibility across the entire platform. The solution? A separate database role with permission to bypass RLS on the Ride table using Postgres’ ALTER TABLE ... FORCE ROW LEVEL SECURITY behavior.

Frequently Asked Questions

How do you test row-level policies locally before deploying?

Use Docker to replicate production data in your local Postgres instance. I seed test users with different roles, then run queries through Prisma to verify access. Tools like psql’s \x display mode help check policy details row-by-row.

Can RLS policies conflict with application-level guards?

Absolutely. A client once had both Prisma-based guards and RLS policies rejecting the same query—debugging that was a nightmare. Stick to one source of truth: I recommend prioritizing RLS for data consistency, and use app-level guards only for UX purposes.

What are the best practices for auditing existing RLS policies?

Run SELECT * FROM pg_policy order by policyname; in Postgres to see all active policies. For complex systems, document each policy’s intent in a shared wiki. I track policy changes through migration scripts with descriptive filenames like 20240515_policy_fix_invoice_rls.sql.

How do you handle multi-tenant RLS with shared resources?

Use a "tenant ID" column for resources accessible across teams—like UAE real estate listings shared between multiple agents. Combine this with cross-scope policies:

CREATE POLICY tenant_access ON "Property"
FOR SELECT
TO authenticated
USING (tenant_id = current_setting('app.tenant')::uuid);

If you're building SaaS platforms or internal tools where data isolation matters, RLS isn’t optional. I’ve implemented it across dozens of UAE startups and enterprises—from a luxury limo booking platform in Khalifa City to a pharmaceutical inventory system in Kuwait. You can read more about my approach to full-stack security in Laravel vs Node.js for UAE Web Projects.

Need help designing secure, scalable Postgres schemas? Book a free consultation to discuss your project.