Row-level security
Row-level security (RLS) is a database feature that attaches a policy to a table so that queries see only the rows the policy permits. The filter is applied by the database engine on every statement, which means it applies to code the application team has not thought about — ad-hoc queries, reports, migrations, background jobs.
| Bypass | Why it happens | What closes it |
|---|---|---|
| Table owner exemption | In Postgres the table's owner is not subject to its policies by default, and applications commonly connect as the owner | Connect as a non-owner, DML-only role, or set FORCE ROW LEVEL SECURITY on the table — ideally both |
A role with BYPASSRLS | Granted for a data migration or an analytics job and never revoked | Audit role attributes; keep BYPASSRLS off every role the application can reach |
| Superuser connections | A pooled connection string using the administrative user, often inherited from a template | Separate credentials for application traffic; superuser only for schema changes |
| Policy enabled but not forced | ENABLE ROW LEVEL SECURITY was run; FORCE was not | Set FORCE, and test as the actual application role rather than as the developer's |
| Session context never set | A background job or a new code path opens a connection without setting the tenant variable | Design the policy to fail closed, so a missing context returns zero rows rather than all rows |
Where the filter lives, and why the layer matters
Four layers can enforce tenant separation, and they differ in what happens when the layer above them has a bug.
- Application
WHEREclauses. Correct only where a developer remembered. A missing clause returns another tenant's rows to a user, with no error and nothing in a log to notice. - ORM default scopes. Better, because the default is safe. Bypassed by every raw query, every reporting tool, every
unscopedcall written to fix a performance problem at 5pm. - Service-layer guards. Centralised checks in one place. Correct if every data access goes through that place, which stops being true the first time a batch job talks to the database directly.
- Database row-level security. Applies to every statement on the connection regardless of origin. A missing filter in application code yields zero rows rather than a leak.
None of these replaces the others; RLS is a floor, not a substitute for authorisation logic. What it changes is the consequence of the mistakes that actually happen.
Fail-open versus fail-closed
The single most important design choice in an RLS deployment is what happens when the tenant context is not set on the session.
A policy written as org_id = current_setting('app.current_org_id')::uuid compares against NULL when the setting is absent. In SQL, a comparison with NULL is not true, so the row is not returned. Every query returns nothing, the failure is loud and immediate, and no data leaks. That is fail-closed.
A policy written to skip the check when the setting is empty — a convenience added so that a migration or an admin script "just works" — returns every row in the table to any connection that forgot to set the context. That is fail-open, and it is how a background job written eight months after the policy becomes a cross-tenant leak.
The test for which one you have is simple and worth asking a vendor to run in front of you: open a connection, set nothing, and select from a tenant table. Zero rows is the correct answer.
Why it matters to a district
Districts sign data privacy agreements that promise student data will not be disclosed to unauthorised parties. The overwhelmingly likely mechanism by which that promise gets broken is not a break-in; it is a query in a multi-tenant product that omitted a filter, or a report that joined a table nobody scoped, and another district's families appearing in your export.
That risk is not visible in a demo, in a feature comparison, or in a SOC 2 report, which attests that controls existed and operated rather than describing the isolation model. It is visible in exactly one place: the answer to "what mechanism prevents it, and what does a query return when that mechanism has nothing to work with?"
Performance, and the honest answer about cost
RLS policies become additional predicates in the query plan. They cost roughly what the equivalent hand-written WHERE clause costs, provided the tenant column is indexed and the policy is written against a simple session variable rather than a subquery. Policies that join other tables to determine access can be genuinely expensive, and a hierarchy-aware policy is exactly the kind that tempts a developer to write a subquery.
The practical guidance: index the tenant column, keep the policy expression simple, resolve hierarchy into a materialised path or an array on the row rather than recomputing it per query, and measure with EXPLAIN under the application role rather than as a superuser — because as a superuser the policy is not applied and the plan you are looking at is not the plan production runs.
How Kastr applies it
Every request runs inside a transaction that issues SET LOCAL ROLE kastr_app — a non-owner role holding DML privileges only — and sets app.current_org_id for the duration. Owner exemption cannot apply because the application never connects as the owner. Policies are hierarchy-aware, so an organisation sees itself and its descendants and never a sibling. They fail closed. A cross-tenant leakage suite runs in continuous integration against real Postgres 16, including a regression check for the owner-bypass case specifically, because that is the failure that reintroduces itself quietly during a refactor.
The audit log tables take this further: they carry INSERT and SELECT policies and no UPDATE or DELETE policy at all, which combined with revoked privileges is what makes the audit log append-only at two independent layers.
Questions people actually ask
Is row-level security different from application-level permission checks?
Yes, and they solve different problems. Application checks decide what a user is allowed to do; RLS decides which rows the database will return no matter what the application asks for. RLS is a backstop for the case where the application logic is wrong, missing, or bypassed by a report or a background job.
Can row-level security be bypassed?
Yes, in five well-known ways: connecting as the table owner, using a role with BYPASSRLS, connecting as superuser, enabling policies without forcing them, and writing a policy that permits access when the tenant context is unset. The first is the most common in practice, because applications are frequently configured with the credentials that created the schema.
What happens if the tenant context is not set?
It depends entirely on how the policy was written, and this is the question that separates a real control from a decorative one. A fail-closed policy compares against NULL and returns zero rows. A policy with a convenience escape for missing context returns every row in the table. Ask a vendor to demonstrate this rather than describe it.
Does RLS slow down queries?
Roughly as much as the equivalent WHERE clause, provided the tenant column is indexed and the policy expression is simple. Policies containing subqueries or joins can be materially expensive. Measure under the application role, not as a superuser — a superuser is not subject to the policy, so the plan you see is not the plan you run.
One price. Every feature. Locked for three years.
$3.50 per student per year under 5,000 students. No tiers, no add-on modules, no per-message fees. Published on the site because you should not have to book a call to learn a price.