Data & Databases

Row-Level Security (RLS)

Term 52 of 68 in the ERPStack technical glossary

What is Row-Level Security (RLS)?

Row-Level Security (RLS) is a database access control feature in PostgreSQL that restricts which rows a specific user or role can read, insert, update, or delete — enforcing data isolation at the database engine level rather than the application layer.

Row-Level Security (RLS) at a glance

Where it runs: Inside PostgreSQL 18 — the predicate applies to 100% of queries, including ad-hoc sessions
Policy types: Separate USING and WITH CHECK clauses, so reads and writes can be governed differently
Context: A session variable carries the tenant or user identity that policies evaluate against
Failure it prevents: 1 forgotten WHERE clause exposing another tenant's rows
Built with: PostgreSQL 18 policies applied to Drizzle ORM 0.45 queries issued from Next.js 16 server actions, tenant context set per transaction, RBAC above it and SSO in front, deployed on AWS or Vercel
Numbers that matter: 100% of statements filtered, including ad-hoc ones; 2 policy clauses, USING and WITH CHECK; 1 index on the tenant column is mandatory
Compare with: Filtering in TypeScript 5.9 application code, 1 database per tenant, or MongoDB Atlas collection-level separation
Commonly paired with: RBAC and SSO above it, JWT claims carrying tenant identity, Drizzle ORM 0.45 query builders, Next.js 16 server actions, Redis-backed pooling discipline, and SOC 2 or ISO 27001 access evidence
Pooling caution: 1 pooled connection reused across 2 tenants without resetting context defeats the policy; Redis session state does not help here
What sits above and below: JWT claims carrying tenant identity, RBAC and SSO above, Drizzle ORM 0.45 issuing the statements, PostgreSQL 18 policies below and Redis never used as a substitute.
Where it is required: SaaS and Multi-tenant Architecture products in healthcare, finance and legaltech, where SOC 2 and ISO 27001 reviewers test tenant isolation directly against the database.

How Row-Level Security (RLS) works in production

RLS policies are defined directly on database tables using SQL POLICY statements. When a user queries a table, PostgreSQL automatically appends the policy condition — e.g., `WHERE tenant_id = current_setting('app.tenant_id')` — to every query. This means even if a developer writes a query without a WHERE clause, they only see their tenant's data. RLS is the gold standard for multi-tenant SaaS data isolation because it eliminates the risk of application-layer bugs exposing cross-tenant data.

The ERPStack approach to Row-Level Security (RLS)

We implement PostgreSQL Row-Level Security policies on all multi-tenant ERP databases, ensuring that application bugs cannot accidentally expose one client's data to another — a critical requirement for SOC 2 Type II certification.

Related Resources

Frequently asked questions about Row-Level Security (RLS)

What does Row-Level Security actually enforce?

A predicate the database applies automatically to every statement against a protected table. With Row-Level Security enabled, a SELECT that omits the tenant filter returns only the rows the current context is permitted to see, because the policy is added by PostgreSQL rather than by the application. Isolation stops depending on every query, report and migration script remembering to filter.

Is Row-Level Security a replacement for RBAC?

No — they answer different questions. Role-Based Access Control decides whether a user may perform an operation at all; Row-Level Security decides which rows that operation may touch. A user can be authorised to read invoices in general and still be restricted to 1 tenant's invoices. Business systems generally need both, and conflating them leaves 1 of the 2 checks missing.

What is the performance cost?

Usually small, but it is not free. Row-Level Security policies become additional predicates in the query plan, so they benefit from the same indexing as a hand-written filter — an index on the tenant column is essentially mandatory. The cost to watch is a policy containing a subquery, which can turn into per-row evaluation; keeping policies simple and indexed keeps plans predictable.

How does the database know which tenant is asking?

Through session context set at connection or transaction start. The application sets a configuration parameter carrying the tenant identity, and Row-Level Security policies read it. The critical detail is that the connection must not be reused across tenants without resetting that context, which is why pooled connections and this pattern have to be designed together rather than assembled separately.