DEV Community

Cover image for We Turned On Row-Level Security in Postgres. Four Things Broke.
StoreFlux
StoreFlux

Posted on

We Turned On Row-Level Security in Postgres. Four Things Broke.

Row-level security looks like a one-line change in Postgres: create a policy, set a session variable, done. We turned it on for a multi-tenant commerce backend, and it took four fixes before it worked. Here they are, along with the decision we had to make.

What RLS does here

Every tenant-scoped table has a policy that compares its tenant_id to a session variable, app.tenant_id. The API sets that variable on each connection, from the authenticated user's tenant. A query that forgets its tenant filter gets no rows instead of another tenant's rows. That is the backstop we wanted. EF Core query filters already scope tenant reads for tenant-entity types, but a future handler that forgets the filter should fail closed, not leak.

The app connects as a least-privilege role, not the superuser. Migrations run as a separate role.

1. One command, one parameter, two statements

The interceptor resets the role and sets the tenant in a single command text. With a parameter in the same command, Npgsql's extended protocol rejects multiple statements. The fix was two calls instead of one. It is a small change, and it failed on the first real request.

2. Refreshing a materialized view needs ownership

A background job refreshes a materialized view as the least-privilege service role. Postgres needs ownership of the view for REFRESH MATERIALIZED VIEW, not just BYPASSRLS. The view was still owned by the superuser from its original migration. A migration moved ownership to the right role.

3. An unset setting is an empty string, not NULL

For a custom setting like app.tenant_id, set_config with NULL stored an empty string. The policy cast that value to uuid, so unauthenticated requests crashed with an invalid uuid error instead of failing closed. The fix wraps the cast in NULLIF, so an empty string becomes NULL and the policy returns no rows.

4. A role change on an open connection did nothing

This one cost the most time. A scoped DbContext keeps its connection open for the whole request. The interceptor applied the role when a connection was opened, but some handlers switch to cross-tenant mode midway through, and those reads silently returned zero rows under RLS. The interceptor now has a refresh entry point, and the repository re-applies role state before and after each cross-tenant call.

The decision we had to make

Fixing number 4 exposed a bigger question. Anonymous endpoints, like login, registration, guest cart, and contact forms, have no tenant identity, so there is nothing for the policy to key off. They have always resolved tenant scope from the store slug in code. For those requests only, we elevate the role for the whole request. Authenticated requests keep full RLS enforcement.

We chose that on purpose. For an anonymous request, RLS can't fail closed to a tenant it doesn't know, so it would only break the endpoint, not protect it. The protection for those endpoints is the explicit tenant lookup in code.

I'm not sure this is the right trade. The alternative is putting tenant resolution into every query, which is more work and gives more places to forget. If you have run RLS with anonymous or pre-auth traffic, how did you handle it?

Top comments (1)

Collapse
 
x1y1co profile image
X1Y1 •

On the anonymous endpoints, you may not need to elevate the role for the whole request. Resolve the store slug to a tenant_id with one small SECURITY DEFINER function (search_path pinned, returns only the id), then set app.tenant_id from that and keep RLS on for everything after it. The elevated part shrinks to one lookup instead of every query the handler runs, and guest cart and contact form writes go through the same policies as signed-in traffic.

One more thing worth checking with pooled connections: set_config with is_local = true scopes the value to the transaction, so it can't carry over into the next request that picks up the same connection. With false it lives for the whole session, which is the kind of leak RLS is supposed to prevent.