Row-level security
Row-level security moves the *which rows may this connection touch* rule out of the application and into the table. The database then applies it to every query, including the one somebody wrote without a filter. Connect runs it forced on every workspace-scoped table, as the last of three independent isolation layers.
The mechanism, in one pass#
You enable the feature on a table and attach one or more policies. A policy is an expression evaluated per row; rows for which it is false are invisible, as though they were not there. A table with the feature enabled and no policy at all denies everything, which is the correct default and surprises people once.
The expression almost always compares a column against something the connection carries: a session variable set at the start of the request, or the database role in use. That is the whole trick — the application declares *who this connection is acting as*, once, and the database applies the consequence to every statement afterwards.
-- shape only; the real policies live in the hardening script
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages FORCE ROW LEVEL SECURITY;
CREATE POLICY workspace_isolation ON messages
USING (workspace_id = current_setting('app.workspace_id', true))
WITH CHECK (workspace_id = current_setting('app.workspace_id', true));USING and WITH CHECK answer different questions#
| Statement | USING applies to | WITH CHECK applies to |
|---|---|---|
| SELECT | Which rows are visible | — |
| INSERT | — | Whether the new row is permitted to exist |
| UPDATE | Which rows may be updated | What the row is allowed to become |
| DELETE | Which rows may be deleted | — |
The consequence people meet first: with USING alone, a connection can move a row *out* of its own scope by updating the very column the policy reads. WITH CHECK is what refuses that. The consequence people meet second is the inverse — a maintenance connection whose session variable is not set writes nothing at all, because every insert fails the check. That is the policy working, not a fault, and repair work belongs on a connection that is meant to have the wider access.
Policies also combine in two ways. Permissive policies are OR'd together, so adding one *widens* access; restrictive policies are AND'd, so adding one narrows it. A tenant boundary written as a restrictive policy cannot be widened by somebody later adding a convenience policy beside it.
FORCE, and the owner who ignores your rules#
Forcing the feature applies the policies to the owner as well. Two other escapes remain and neither is closed by anything on the table: a role carrying the bypass attribute, and the superuser. Those roles exist for backup, restore and repair, and treating them as ordinary application credentials removes the boundary entirely.
Does Connect use row-level security?#
Used, forced, on every workspace-scoped table. It is the third of three independent isolation layers. Above it, an allowlist in the middleware refuses a customer session that calls an operator path, and the ORM workspace kernel filters every query it builds. Row-level security is the layer that still holds when a query is written without a filter, which is precisely the failure the other two cannot see.
The independence is the point. Three layers that all derive from the same application variable would be one layer described three times; these three fail differently, so a mistake in one is caught by another.
What a policy cannot do#
- It cannot survive a pooled connection with a stale session variable. If the variable is set per request and the pool hands the connection on without clearing it, the next request inherits the last one's scope. Setting it inside the transaction is the usual answer.
- It cannot constrain a bypassing role. Backup, restore and administrative roles see everything by design.
- It is not authorisation inside a tenant. It answers *is this row in scope*, not *may this person read this record*. Role and permission checks are a separate concern.
- It does not protect a copy. A dump, a replica's files or a log line containing row data are outside the policy's reach — that is what encryption at rest and access control on backups are for.
- It is not free. The expression runs per row and a badly written one can prevent an index being used, turning a fast query into a scan.
None of that argues against it. It argues for treating it as the layer that makes a whole class of application bug harmless, rather than as the security model.
Questions#
Is row-level security enough on its own?
It is enough to make a missing filter harmless, which is a great deal. It is not enough on its own: bypassing roles exist, pooled connections can carry a stale scope, and permissions inside a workspace are a different question entirely. Here it is one of three layers, deliberately.
Why did a maintenance script write nothing?
Most often because the check clause refused every insert — the connection's session scope did not match the rows it was trying to write. That is correct behaviour. Repairs belong on a connection intended to have wider access, not on a policy loosened to let the script through.
What happens if a table is created without a policy?
If the feature is not enabled on it, nothing is enforced at the database at all, and the table is protected only by the layers above. That is why the hardening step and the scoped-table list are release artefacts rather than documentation.