Row-Level Security

Row-level security takes "which rows may this role see" out of every query and puts it in the table. Once a table has it enabled, Postgres attaches the matching policy's condition to every statement that touches it, so a filter forgotten in one endpoint stops being a disclosure and becomes a query that returns nothing. The rule is enforced identically for the application, for a scheduled job, and for a psql session run by someone who has forgotten the rule exists.

Cartwheel's analytics team wants direct access to pg-replica-a instead of asking for a CSV every Thursday, and the blocker is customers. The contract that covers the analysts limits them to the city they work on, and today the only thing implementing that limit is an agreed query nobody checks. Writing the rule as a policy makes it a property of the table rather than a habit of the person typing. The price is a predicate in every plan that touches the table, plus a testing burden that most teams discover late.

Enabling It, and Writing a Policy

Enabling row-level security on a table denies everything. There is no partial state: with the feature on and no applicable policy, a default-deny rule applies and no rows are visible or modifiable by anyone the feature applies to. Policies then add access back. That order is deliberate and it is the opposite of how people expect a filter to behave, so the first thing anyone sees after the ALTER TABLE is an empty result set from a table that is visibly full of rows.

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;   -- nothing is visible now

CREATE POLICY customers_by_city ON customers
    FOR ALL TO cartwheel_analytics
    USING      (city = current_setting('app.city', true))
    WITH CHECK (city = current_setting('app.city', true));

The two clauses answer different questions. USING decides which existing rows a statement may see: it filters what SELECT returns, and it decides which rows UPDATE and DELETE are allowed to touch, with a row that fails it simply not being there. WITH CHECK decides what a row may look like after an insert or an update, and a row that fails it raises an error rather than disappearing. That asymmetry is the useful part: a violated read is silent, a violated write is loud. Policies are permissive by default and combine with OR; declaring one RESTRICTIVE combines it with AND instead, which is how a blanket rule is layered under several narrower ones.

Omitting WITH CHECK on an ALL or UPDATE policy is not the trap it looks like, because Postgres reuses the USING expression when the clause is absent. The dangerous version is the explicit WITH CHECK (true) somebody adds after an update failed with a policy violation: reads stay confined to one city, and any analyst can now move a customer row into another. Write both clauses out even when they are identical, so the next reader sees a decision rather than a default.

The Session Variable, and Why SET LOCAL

The policy needs a value from the application, and the mechanism is a configuration parameter with a two-part name. Postgres accepts a setting for any parameter written as extension.name, holding it as a placeholder until something defines it, so app.city requires no extension and no server configuration — it exists because a session set it.

BEGIN;
  SET LOCAL app.city = 'rotterdam';
  SELECT count(*) FROM customers;    -- only that city's rows exist here
COMMIT;                              -- and the value is gone again

SET LOCAL lasts to the end of the current transaction whether it commits or rolls back, and then the session-level value takes over again. That is precisely the property a transaction pooler requires: pgbouncer-01 hands the same server connection to a different client the moment a transaction ends, so a session-level SET would carry one analyst's city into the next client's queries with no error anywhere. Issued outside a transaction block, SET LOCAL warns and does nothing, and the queries after it run under whatever the session already held.

The second argument to current_setting decides what happens when nothing set the value. With true the function returns null instead of raising, city = NULL evaluates to null, the policy admits no rows, and the endpoint that forgot the variable gets an empty result rather than a leak. Without the argument the same query raises an error naming the missing parameter. Both are safe, and the difference is whether the bug surfaces as a blank dashboard or as a stack trace. Cartwheel fails closed here and puts the loud version in a health check.

Owners, FORCE, and BYPASSRLS

This is the section that decides whether any of the rest works. Table owners normally bypass row security on their own tables, so if the application connects as cartwheel_migrator, the role that owns the tables, enabling policies changes nothing for it, produces no error, and leaves a team convinced the data is protected. Superusers and roles carrying the BYPASSRLS attribute always bypass policies, and nothing on the table changes that.

ALTER TABLE customers FORCE ROW LEVEL SECURITY;

SELECT rolname, rolsuper, rolbypassrls
  FROM pg_roles
 WHERE rolsuper OR rolbypassrls;   -- the list that ignores every policy

FORCE ROW LEVEL SECURITY makes the owner subject to the table's policies too, which is the setting a migration should include as a matter of course. It does not reach superusers or BYPASSRLS roles, so that second query is the real answer to "who can see everything" and it belongs in the same review as the policy. Two further facts round it out: referential integrity checks always bypass row security, so a foreign key can prove that a row exists which the querying role cannot read, and pg_read_all_data does not bypass policies, which is what makes it a defensible grant for a broad reader.

The Predicate Is in Every Plan

A policy expression is evaluated for each row before any condition or function that came from the user's query, with leakproof functions as the only exception. The consequence is an indexing one: the column the policy references has to be indexed, or every statement against the table degrades into a sequential scan with the predicate applied per row. On customers that means an index on city; on a multi-tenant schema it means the tenant column leading the composite indexes that already exist, which is Chapter 8's subject rather than this one. The failure gets filed as "row-level security is slow" and is almost always a missing index on the policy's own column.

Leakage is the subtler cost. A cheap user-supplied function can be evaluated ahead of a filter when the planner is free to reorder, which is the mechanism security_barrier views exist to block and the reason marking a function LEAKPROOF is a decision only a superuser can make. Error messages are the other channel: a unique violation or an arithmetic error can name a value from a row that was never returned. Row-level security is a strong control against a forgotten WHERE clause written by your own team, and a weaker one against a role permitted to run arbitrary SQL against a schema it can read.

Where It Fits, and Where It Does Not

The shape it suits is narrow and common: a discriminator column on every table, a small number of roles, and a rule expressible as one boolean over the row in front of you. Multi-tenant SaaS is the archetype and how most people meet the feature. Cartwheel's version is real SQL access to a defined slice instead of a weekly export. The third case is regulated data, where a query-level mistake is not a bug report but a notification with a deadline attached.

It stops fitting when the rule needs more than the row. Authorization that depends on a second table, on a role hierarchy, or on request context beyond a single value turns into policy SQL that joins, and that join runs once per row considered, so on orders it runs 40 million times. Policies also apply per table, not per query: a policy on customers does nothing for orders, and a join between them exposes every order regardless of city until orders carries its own. When the rule needs a graph rather than a predicate, it belongs in the application.

Testing a Policy Like a Firewall Rule

A policy is a security control and it deserves the tests a firewall rule gets. The suite connects as each role, sets each variable, and asserts both halves — that the permitted rows come back and that the forbidden ones do not. The negative assertion is the one that catches a policy that stopped applying because a migration recreated the table without it.

SET ROLE cartwheel_analytics;
BEGIN;
  SET LOCAL app.city = 'rotterdam';
  SELECT count(*) FROM customers;                          -- expect: > 0
  SELECT count(*) FROM customers WHERE city <> 'rotterdam';  -- expect: 0
  INSERT INTO customers (email, city)
       VALUES ('x@cartwheel.example', 'utrecht');           -- expect: error
ROLLBACK;
RESET ROLE;

The first assertion proves the policy is not denying everything, which is the state a table lands in when the feature is enabled and the policy is not. The second proves it is filtering rather than trusting the client's own WHERE. The third proves WITH CHECK is doing its job, and it is the one that fails silently for months after somebody wrote WITH CHECK (true) to make an error go away. Run all three in continuous integration against a database built by the production migrations, and treat a policy change the way you would treat a change to a security group.