Row-level security is where your business rules should live

A service business holds the most sensitive documents its customers own: passports, contracts, tax data, family records. The question "who can see this row?" is not a UI concern. It is the security model of the company. And the only place where that model cannot be bypassed is the database itself.

The failure mode of frontend authorization

Most internal tools are built like this: the interface checks the user's role and hides what they should not touch. The API trusts the interface. The database trusts the API.

Each of those trusts is an assumption an attacker can test. A hidden button is still a callable endpoint. A missing check on one route is a full data export. When the system grows — a second office, a new role, an external accountant — the number of places where a check can be forgotten grows with it.

Push the rule down

PostgreSQL row-level security (RLS) lets you attach policies to tables: this row is visible to this user only if this condition is true. The condition is evaluated by the database for every query, regardless of which application, script or curl command issued it.

In practice, for a multi-office system that means:

• a practices table where staff can read rows of their own office, and admins can read all;
• documents that inherit visibility from the practice they belong to;
• a profiles table that carries the role, so the policy can ask is_admin() instead of duplicating logic;
• public visitors who can read only rows marked as published, and nothing else.

The application still has roles and screens. But if the application is wrong, the database says no.

Patterns that held up

A few things I keep doing:

1. Deny by default. Enable RLS on every table before writing a single policy. A table without policies returns nothing — that is the correct failure.
2. One helper function per question. is_admin(), same_office(practice_id). Policies read like sentences and are easy to audit.
3. Separate the service role. Server-side jobs (imports, automations) use a service key that bypasses RLS — and never touches the browser. Everything user-facing goes through the anon key and the user's session.
4. Write the policies with the schema. They live in the same migration as the table. A table cannot land in production without its policies.
5. Test with the anon key. The most useful test is a query that should return nothing.

Security as an engineering property

None of this is exotic. It is what happens when you take seriously that the systems you build hold other people's lives on paper. Thinking like an attacker — what would I try if I had a valid login and bad intentions? — is not a separate discipline from engineering. It is engineering done properly.

Understand the system deeply enough to build it securely. Then let the database keep the promise.