Row Level Security: Concepts and Practical Implementation

1. What Row Level Security actually solves

Row Level Security answers a simple question that is often solved incorrectly: is this database user allowed to read or write exactly this row? Without Row Level Security, this check typically lives in the application layer, scattered across dozens of controllers, repositories and services. Every new query needs a correct WHERE clause with a tenant ID or user ID, and every forgotten clause opens a data leak between tenants or users.

Row Level Security moves this rule to where it cannot be bypassed: the database engine itself. A policy defined once applies to every query, every report, every ad-hoc script and every new developer who queries a table directly. That is the decisive difference from application-side filters: Row Level Security cannot be skipped by a forgotten WHERE clause, because the engine itself checks every row before returning it.

Row Level Security becomes particularly relevant in three scenarios: multi-tenant SaaS applications where tenants share strictly separated data in the same table, regulated industries with a legal obligation for data separation, and internal systems where different roles need different views of the same table. In all three cases, a central policy replaces hundreds of scattered application checks.

2. Row Level Security in PostgreSQL: policies and predicates

PostgreSQL offers native Row Level Security since version 9.5 via CREATE POLICY. A policy defines a boolean expression that is evaluated for every row before it becomes visible. First, Row Level Security must be enabled for the table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY, after which the defined policies apply to all users except the table owner and superuser, who are exempt from policies by default.

A policy can be defined separately for SELECT, INSERT, UPDATE and DELETE, with different expressions for USING (which existing rows are visible) and WITH CHECK (which new or changed rows are allowed). This separation is crucial: a user might, for example, be allowed to read their own rows but not create new rows with a foreign tenant ID, even if they accidentally pass the wrong ID.

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

-- Policy for SELECT/UPDATE/DELETE: only rows of the caller's tenant
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant')::int);

-- Separate WITH CHECK: prevents inserting rows for another tenant
CREATE POLICY tenant_isolation_insert ON invoices
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::int);

-- Optional: bypass policy for a maintenance role
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY admin_full_access ON invoices
  TO db_admin_role
  USING (true);

FORCE ROW LEVEL SECURITY matters here: without it, the table owner ignores all policies by default, which in practice often leads to a false sense of security when applications accidentally connect with the owner account. With active Row Level Security and a correct role model, tenant separation becomes a property of the database rather than the application code.

3. Row Level Security without a native feature: view-based patterns

Not every database offers native Row Level Security. MySQL, for instance, has no built in policy system, but the concept can be rebuilt with a combination of views and session variables. The core idea stays identical: an intermediate layer filters rows based on the current context before application code sees the data, and that layer lives in the database instead of the code.

The downside of this substitute for native Row Level Security: application code must consistently access data only through the view, never directly through the base table. A database grant that denies direct access to the base table and only permits the view makes this rule technically enforceable rather than a mere convention.

-- MySQL has no native Row Level Security, emulate it with a view
-- Session variable set per connection by the application layer
SET @current_tenant = 42;

CREATE VIEW invoices_scoped AS
  SELECT * FROM invoices
  WHERE tenant_id = @current_tenant;

-- Revoke direct table access, force usage of the scoped view
REVOKE SELECT ON invoices FROM app_user;
GRANT SELECT ON invoices_scoped TO app_user;

-- Application always queries the view, never the base table
SELECT id, amount, status FROM invoices_scoped
WHERE status = 'open';

This view-based alternative does not quite reach the same robustness as native Row Level Security, because session variables can be manipulated if application code embeds unvalidated input directly into SET statements. That is why the assignment of the session variable must come exclusively from a verified, server side source, never from a client parameter.

4. Securing multi-tenancy with Row Level Security

Multi-tenant architectures with a shared database and shared schema are the classic use case for Row Level Security. Instead of running a separate schema or database per tenant, all tenants share the same tables, distinguished only by a tenant_id column. This architecture is significantly cheaper to operate, but carries exactly the risk that Row Level Security addresses: a forgotten filter condition exposes data belonging to the wrong tenant.

In practice, Row Level Security gets combined with a connection pool that sets the correct tenant_id as a session variable per request before any query runs. Middleware in the application code extracts the tenant ID from the authenticated request and sets it at the start of every transaction. After that, not a single repository needs explicit tenant filtering anymore, because Row Level Security handles it transparently.

A frequently overlooked point: background jobs, cron jobs and reporting scripts must also set the same session context, otherwise they either see no rows at all (if the variable is missing and the policy is restrictive) or, worse, all rows of all tenants (if a superuser account without FORCE ROW LEVEL SECURITY is used).

5. Session context: how the database knows who is asking

For Row Level Security to decide which rows are visible at all, the database needs information about the current caller. In PostgreSQL, current_setting() combined with SET or SET LOCAL handles this task. SET LOCAL is especially important for Row Level Security in connection pools, because the value only applies within the current transaction and is automatically reset on commit or rollback, instead of lingering on the pooled connection for the next request.

A second approach uses the database role itself as context, without session variables: every tenant or user group gets its own database role, and policies check current_user or role membership directly. This approach scales worse with thousands of tenants because every role needs individual management, but it is more robust against accidentally forgetting to set a session variable, since the role is already fixed at connection time.

-- SET LOCAL scopes the variable to the current transaction only
BEGIN;
  SET LOCAL app.current_tenant = '42';
  SET LOCAL app.current_role = 'accountant';

  -- Row Level Security policies read both settings
  SELECT * FROM invoices;   -- automatically scoped
COMMIT;
-- After commit, the pooled connection has no leftover tenant context

-- Policy combining tenant scope with role-based visibility
CREATE POLICY role_scoped_invoices ON invoices
  USING (
    tenant_id = current_setting('app.current_tenant')::int
    AND (
      current_setting('app.current_role') = 'admin'
      OR status != 'confidential'
    )
  );

6. Performance implications of Row Level Security

A legitimate objection against Row Level Security: every query gets extended with additional conditions, which can affect the execution plan. In practice, the overhead is small as long as the columns policies check are properly indexed. A policy filtering on tenant_id benefits directly from an index on tenant_id, just like a manually written WHERE clause would.

A more subtle performance problem arises when policies contain complex subqueries or function calls evaluated per row. PostgreSQL tries to fold policy expressions into the query plan and combine them with other filters, but expensive functions inside a policy can prevent this optimization. The practical recommendation: keep policies as simple as possible, ideally a direct column comparison, and move more complex logic into an indexed helper column rather than a runtime function.

Comparing EXPLAIN plans with and without Row Level Security enabled usually shows only marginal execution time differences in well-indexed schemas. The actual cost factor is rarely Row Level Security itself, but missing indexes on the columns referenced in policies, a mistake that would slow down the same query even without Row Level Security.

7. Testing and verifying Row Level Security

Policies for Row Level Security need to be tested just as thoroughly as application logic, because a faulty policy is a direct data leak. The testing approach simulates different session contexts within the same test suite: for every tenant or role, a transaction is opened with the corresponding context, a query runs, and the result is checked against the expected row set.

An important test case for Row Level Security that often gets skipped: the negative test. It is not enough to verify that tenant A sees its own rows, one must explicitly verify that tenant A never sees tenant B's rows under any circumstance, not even through JOINs with other tables, aggregate functions, or subqueries that reference rows indirectly.

-- Test harness pattern: verify Row Level Security isolation
-- Run as tenant 42, expect only tenant 42 rows
BEGIN;
  SET LOCAL app.current_tenant = '42';
  SELECT count(*) FROM invoices WHERE tenant_id != 42;
  -- Expected result: 0, Row Level Security must hide foreign rows
ROLLBACK;

-- Negative test: attempt cross-tenant insert should fail
BEGIN;
  SET LOCAL app.current_tenant = '42';
  INSERT INTO invoices (tenant_id, amount) VALUES (99, 100.00);
  -- Expected: error, WITH CHECK policy rejects mismatched tenant_id
ROLLBACK;

-- Aggregation must still respect Row Level Security
BEGIN;
  SET LOCAL app.current_tenant = '42';
  SELECT sum(amount) FROM invoices;
  -- Must equal manual sum WHERE tenant_id = 42, nothing more
ROLLBACK;

8. Common mistakes with Row Level Security

The most common mistake with Row Level Security is forgetting FORCE ROW LEVEL SECURITY, which lets the table owner bypass all policies. If application code accidentally connects with the owner account instead of a restricted role, which happens often in development environments, the policies do not apply at all, and the mistake only surfaces in production once a data leak has already occurred.

A second common mistake: defining policies only for SELECT but forgetting WITH CHECK for INSERT and UPDATE. Without WITH CHECK, a user cannot read foreign rows, but can still create new rows with a foreign tenant_id, because the write direction remains unchecked. A third mistake is setting session variables with SET instead of SET LOCAL in pooled connections, which leaves one request's tenant context lingering for the next request on the same connection.

9. Row Level Security approaches compared

There are several ways to control access at the row level, with clear differences in security, maintainability and database support. The right approach for Row Level Security depends heavily on the database in use and the number of tenants.

In well-indexed schemas with anywhere from a handful to several thousand tenants, native Row Level Security delivers the most robust solution, because it operates independently of the application code. For databases without a native feature, the view-based variant remains the most practical alternative, as long as direct table access is consistently denied through grants.

10. Summary

Row Level Security moves row-level access control out of scattered application code and into a central, database-enforced rule. In PostgreSQL, Row Level Security is built with CREATE POLICY using separate USING and WITH CHECK expressions, enabled by ENABLE ROW LEVEL SECURITY and, for the owner, by FORCE ROW LEVEL SECURITY. Databases without a native feature can rebuild the same concept through views with session variables and consistent grants.

The biggest win from Row Level Security shows up in multi-tenant systems: a single, testable policy replaces hundreds of scattered WHERE clauses and also protects background jobs, reports and future code that would otherwise have to reimplement the rule. Performance overhead stays low as long as referenced columns are indexed, and negative tests against cross-tenant access are mandatory, not optional.

11. FAQ: Row Level Security