Row-Level Security Implementation in Postgres for Multi-Team Analytics

Multi-tenant software makes one promise above all others: each customer's data belongs to them, and nobody else gets to see it. That's the whole deal. Break that promise once, and it doesn't matter how good the rest of the product is.

Application code tacks WHERE tenant_id = $1 onto every query that touches shared data, keeping that promise today. It works. Until it doesn't.

And the way it stops working is almost always quiet. One endpoint forgets the clause. One ORM upgrade rewrites a query and drops it. One new feature ships without anyone remembering the filter needs to be there too. None of these appear in a code review as "security bug."" They show up as a normal-looking line of code that happens to return everyone's data instead of one tenant's.

It gets worse once you zoom out past the application itself. Background jobs, direct database connections, and BI tools that skip the ORM all bypass it entirely. Everything else is wide open by default.

And when it fails, it doesn't fail loudly. There's no crash, no stack trace, no alert. A tenant just quietly sees rows that aren't theirs, and the first sign anyone gets is a confused support ticket asking why a dashboard shows someone else's numbers. That's the kind of bill a "silent" leak can generate before anyone even understands what happened.

Row-level security (RLS) is Postgres's answer to this exact failure mode. Instead of trusting every piece of code that touches the database to remember the filter, RLS moves the filter into the database engine itself. The policy lives on the table itself, consolidated in one place rather than scattered across a codebase as application logic. Which raises the obvious question: if this is so much safer, why isn't everyone already doing it? Turning that promise into something that actually holds up takes more than flipping a switch, and that's what the rest of this piece walks through. The average global data breach cost reached $4.88M, with lost business and customer turnover a major factor.

Where RLS fits among the three multi-tenancy architectures

Before getting into how RLS works, it helps to know what it's competing against. There are three basic ways to lay out tenant data in Postgres, and each one trades isolation for complexity differently.

The first option is a separate database per tenant. This gives the strongest possible isolation, since tenants are physically separated at the database level. But it comes with real operational weight: more connections to manage, more infrastructure to monitor, more everything. It tends to make sense for regulated industries or white-label products where isolation is a contractual requirement, not just a nice-to-have.

The second option is a separate schema per tenant inside one database. This still gives high isolation, and it's a popular middle ground, but it has a ceiling. Past a certain tenant count, the approach becomes hard to manage: system catalog bloat occurs, migrations that should run once now have to run across every schema, and schema drift appears as tenants fall out of sync with each other over time. Estimates on exactly where that ceiling sits vary depending on who you ask, but the pattern all of them describe is the same: schema-per-tenant works fine at small scale and turns into a maintenance headache as tenant count grows. On top of that, running an aggregate query across all tenants (something a data team will eventually want to do) gets genuinely difficult when every tenant's data lives in its own schema.

The third option is a shared schema with row-level security enforcing the boundary. Every tenant's rows sit in the same tables, and Postgres itself decides, row by row, who gets to see what. Done correctly, shared schema plus RLS gives strong isolation with correct configuration, has the lowest operational complexity, and schema changes run once. Schema changes run once, against one set of tables, instead of once per tenant. For most SaaS analytics workloads operating at any real scale, this is the sensible default.

The catch is right there in "done correctly." Skip either one, and the model falls apart.

This is precisely why RLS matters so much for internal multi-team analytics specifically. A BI tool, a dashboard query, or a data team's ad-hoc script connecting directly to the database cannot bypass a policy the way it can bypass an ORM filter. But they all have to go through the database. A policy enforced by the database engine catches every single one of them, no matter how they connect. Shared schema requires a tenant_id column on every tenant-scoped table, with RLS enforcing it unconditionally, in exchange for simplicity.

How Postgres RLS works mechanically before any policy is written

Being precise about what RLS actually does matters because the mental model matters for everything that follows. RLS is not a filter applied after the fact to results that already came back. It's a rewrite of the query itself, before the planner ever sees it.

That looks like this in practice. A role runs SELECT * FROM documents. Before Postgres plans that query, it rewrites it internally to something close to SELECT * FROM documents WHERE <policy expression>. The policy expression becomes part of the query the planner actually optimizes. A policy that boils down to tenant_id = 42 gets to use an index on tenant_id exactly as if a developer had typed that condition directly. Isolation doesn't have to cost performance when the schema is built right.

Writes get the same treatment. INSERT and UPDATE statements get an analogous check applied to the new or changed row, so a tenant can't sneak in a row that belongs to someone else, or edit their way into another tenant's data.

Turning this on is one line: ALTER TABLE table_name ENABLE ROW LEVEL SECURITY. But that line alone doesn't grant any access. Enabling RLS on a table with no policies attached blocks all access to that table, full stop. That's a safe failure mode, arguably the safest one Postgres could pick, but it isn't a working one. Every access pattern the application actually needs (read this way, write that way) has to get an explicit policy written for it.

Policies have two working parts. The USING clause decides which existing rows are visible for SELECT, UPDATE, and DELETE. The WITH CHECK clause decides which rows a write is allowed to produce. A policy can be scoped to a specific command, or to a specific role, or both. And when there's more than one policy on a table, permissive policies combine with OR (any one of them passing is enough), while restrictive policies combine with AND across whatever the permissive policies allowed. Getting that combination logic wrong is a quiet way to end up with a policy that looks locked down but actually isn't, so testing exactly what a given role can see, not just assuming the policy does what its name suggests, is necessary.

Schema design that makes RLS policies reliable from the start

The right schema makes this work. The floor requirement is simple to state: every table holding tenant-specific data needs a tenant_id column, ideally a UUID, with a NOT NULL constraint and a foreign key back to a tenants table. Skip the NOT NULL constraint, and a row can end up with no tenant at all, which policies won't know how to handle correctly.

The standard pattern looks like this. A tenants table holds the metadata: an id as the primary key, a name, a created_at timestamp. Then every tenant-scoped table, whether it's events, customers, or invoices, carries a tenant_id UUID NOT NULL REFERENCES tenants(id). Same pattern, repeated across every table that needs it. That consistency is what makes policies predictable instead of a table-by-table guessing game.

Indexing isn't an optional finishing touch here, it's load-bearing. Every table of any real size needs a single-column index on tenant_id, something as simple as CREATE INDEX idx_events_tenant_id ON events (tenant_id). But analytics workloads usually filter on a time range too, and that's where a lot of teams get surprised. Without a compound index on (tenant_id, occurred_at DESC), Postgres can satisfy the tenant filter fine, but then it has to sort the entire matching result set to handle the time-range filter, and on a large table that sort is expensive. The fix is a composite index, something like CREATE INDEX orders_tenant_created_idx ON orders (tenant_id, created_at DESC). One index, two problems solved.

A query joining pg_tables, pg_policies, and information_schema.columns can surface which tables actually have RLS turned on, and which ones just have a tenant_id column sitting there unprotected. A query joining pg_tables, pg_policies, and information_schema.columns can surface exactly that gap. It's the kind of check that takes a few seconds to run and can catch a table that quietly shipped without its policy attached, which is exactly the kind of mistake that turns into the silent leak described earlier.

Passing tenant identity into the database session safely

Policies need to know which tenant is asking. A policy written as tenant_id = current_tenant_id() is only as good as whatever current_tenant_id() actually returns, and the database has no way to infer that from the query text alone. Something has to tell it, on every connection, who's asking.

There are two common ways to solve this.

The first is a session variable, sometimes called a GUC (grand unified configuration, if you're curious). The application sets a custom variable, something like app.current_tenant or app.tenant_id, right after authenticating a request. The policy then reads it back with current_setting('app.tenant_id', true)::UUID. That true flag returns NULL (not an error) if the variable is unset, which causes the policy to deny all access rather than throw an exception. A NULL fails the policy's comparison and denies access. An error would crash the query. Denying is the outcome you want by default.

Always set that variable with SET LOCAL inside a transaction, never a bare SET at the session level. Why does that distinction matter so much? Because a bare SET sticks around for the life of the connection, and on a pooled connection, that connection gets handed to a completely different request afterward, carrying the previous tenant's context along with it. That's a leak waiting to happen, and it's an easy one to miss in testing because it occurs only under the exact conditions pooling creates. A helper function like CREATE OR REPLACE FUNCTION set_tenant(p_tenant_id UUID) wraps the set, following the pattern from S3.

The second approach uses per-tenant Postgres roles instead of a session variable. A role gets created per tenant, and a stable function extracts the tenant ID from current_user; the policy calls that function instead of reading a session variable. This can work well when the tenant count is low and each tenant gets its own dedicated connection. But it doesn't scale gracefully: a separate role and a separate connection pool per tenant gets operationally expensive fast once tenant counts climb.

However the context gets set, the request lifecycle has to actually do it, every time. In practice that means: pull tenant identity out of the JWT or the subdomain, grab a connection, set the tenant context, run the query, then reset or close the connection. That's the full loop, and it has to run on every single request, no exceptions.

Background jobs are where this loop tends to quietly go missing. A job that runs outside the normal request cycle, a scheduled export, a nightly aggregation, doesn't have HTTP middleware setting tenant context for it. If nobody adds that step explicitly, the job either fails the policy check entirely or, worse, runs with whatever context happened to be left over on the connection.

Role design and FORCE ROW LEVEL SECURITY (the most commonly missed configuration)

The role the application uses to query the database also owns the tables it's querying. And in every version of Postgres, table owners bypass row security by default. Doesn't matter how carefully the policy is written. If the querying role owns the table, the policy simply doesn't apply to it.

This tends to happen by accident, not by design. The most common RLS gotcha: the app role owns the tables it queries. Nobody set out to bypass RLS. It just happens as a side effect of using one role for two different jobs.

The fix is to split those jobs apart. Keep a dedicated migration or owner role that creates and owns the schema. Have the application connect as a separate role, one that owns nothing and has no BYPASSRLS attribute. Two roles, two responsibilities, no accidental bypass.

FORCE ROW LEVEL SECURITY. This makes even the table's owner subject to the policies on that table. It's a belt-and-suspenders move that applies to every tenant-scoped table as a matter of habit, not just the ones where a mistake seems likely.

Setting up the application role correctly is a short list of commands: CREATE ROLE app_user LOGIN, then GRANT SELECT, INSERT, UPDATE, DELETE ON <tables> TO app_user. That role should never get SUPERUSER or BYPASSRLS, under any circumstance. Either attribute makes every policy on every table irrelevant for that role. BYPASSRLS in particular should be reserved for maintenance tasks and logical dump roles, nothing that touches application or analytics traffic. It's tempting to grant it "just for this one script," but that one script is exactly how the bypass becomes permanent.

The four bypass risks that silently break correct-looking policies

A policy can be written correctly, tested correctly, and still get bypassed through a path that has nothing to do with the policy itself. Four of these are known by name.

Views. A view runs with the permissions of its owner by default, something close to security-definer behavior even though it isn't technically the same mechanism. That means RLS on the underlying table gets checked against the view's owner, not against whoever is actually running the query through the view. If that owner happens to be a superuser or the table's owner, querying through the view strips out RLS protection entirely for anyone who uses it. Postgres 15 added a real fix: creating a view with security_invoker = true makes the RLS check run as the actual caller instead of the view's owner. On anything older than 15, every view sitting over a tenant-scoped table needs to be treated as a potential policy gap and reviewed with extra care.

pg_dump. Backups run with row security turned off by default, and they'll error out rather than produce a silently incomplete backup (this is deliberate; nobody wants to discover during a restore that half the data quietly didn't get backed up). But that means the role running pg_dump needs elevated privileges, superuser or BYPASSRLS, to do its job. The practical consequence: backups should never double as an export channel for application-layer clients. That role has to bypass the exact protection everything else depends on.

COPY. COPY FROM isn't supported on RLS-protected tables at all, so bulk loading has to go through INSERT statements instead. COPY TO, on the other hand, does respect SELECT policies, so exporting data with COPY TO still honors tenant isolation. Same command family, two different behaviors depending on direction, which is easy to get backwards if you're moving fast.

Partitioned tables. A policy on the parent table applies to its partitions when the query goes through the parent. But a policy attached to an individual partition only applies when that partition is queried directly. Teams that query partitions directly, maybe for performance reasons, need policies at both levels, not just the parent. Missing the partition-level policy is an easy blind spot precisely because querying through the parent works fine and hides the gap.

Connection pooling with RLS and the session-pinning failure mode

Connection pooling and RLS don't naturally get along, and it's worth understanding exactly why before it becomes a production problem.

Transaction-mode poolers, PgBouncer being the common example, reuse the same server-side connection across requests from different clients. That's the entire point of pooling: don't open a new database connection for every request, reuse what's already open. But it means a server connection that had app.tenant_id set to Tenant A during one transaction can turn around and serve Tenant B's next request, if nothing cleared that variable in between. It's a real risk already sitting in the architecture. That's the exact leak this whole architecture was built to prevent, sneaking back in through the connection layer instead of the application layer.

The fix is to wrap set_config('app.current_tenant_id',..., true) inside the transaction, the third argument, true, makes the setting transaction-local, so it resets automatically when the transaction ends and PgBouncer returns the connection to the pool. A bare SET, run outside a transaction block, has no such reset behavior, and running one behind a transaction-mode pooler is asking for exactly the cross-tenant leak described above.

Certain operations force PgBouncer to pin a connection to a client rather than releasing it back to the pool after each transaction. That's a pool-capacity problem more than a security one, and it changes how many concurrent connections a given pool can actually support. Between session pinning and the tenant-context reset requirement, pooling with RLS isn't a "set it and forget it" configuration. It rewards teams that understand exactly what happens to a connection between one transaction and the next, and it punishes teams that assume pooling behaves the same way with RLS as it does without it. Analytics interfaces that sit on top of Postgres, such as Basedash, a natural-language BI platform that translates plain-English business questions into SQL against a governed semantic layer, connect through the same pooled path and are subject to every one of these constraints. IBM research shows the average global data breach cost reached $4.88M, with lost business and customer turnover a major factor https://navanathjadhav.medium.com/building-a-multi-tenant-saas-the-database-design-nobody-talks-about-7831b576655f.