Dev.to Security 🔐 Cybersecurity 👁 0 📖 2 min read

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, alo

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?

📰 Read the original article on Dev.to Security

Originally published by Dev.to Security. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.