Nov 18, 20267 min read

Postgres Row-Level Security for Multi-Tenant SaaS

Use Postgres row-level security to keep each tenant's rows invisible to the others: policies keyed on a transaction-local setting, a non-owner role so policies apply, connection pool safety, and tests that prove isolation.

Illustration for Postgres Row-Level Security for Multi-Tenant SaaS

Most multi-tenant SaaS apps put every customer in the same tables and rely on a WHERE tenant_id = $1 in every query. It works until one query forgets the clause, usually in an admin export or a new report, and one customer's invoices show up in another customer's dashboard. That bug is easy to write and hard to catch in review.

Postgres row-level security moves the tenant filter into the database. You attach a policy to each table, set the tenant at the start of a transaction, and Postgres applies the condition to every select, update and delete on that table. This guide sets it up so the policy applies, stays safe with a connection pool, and is tested.

RLS building blocks at a glance

PieceWhat it doesCommon mistake
tenant_id columnMarks each row's ownerMissing on child tables
ENABLE ROW LEVEL SECURITYTurns policies on for a tableForgetting new tables
CREATE POLICY ... USING / WITH CHECKFilters reads, validates writesNo WITH CHECK, so inserts can target other tenants
SET LOCAL / set_config(..., true)Per-transaction tenant contextSession SET leaking across pooled connections
Non-owner roleRole that policies apply toConnecting as owner or superuser, which bypasses RLS
Index on tenant_idKeeps filtered queries fastSequential scans on large tables

Schema and policies

Give every tenant-owned table a tenant_id, enable RLS, and add one policy that covers reads and writes. current_setting('app.tenant_id', true) returns NULL instead of erroring when the setting is missing, so an unset context matches no rows.

CREATE TABLE invoices ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, number TEXT NOT NULL, total_cents BIGINT NOT NULL, UNIQUE (tenant_id, number) ); CREATE INDEX invoices_tenant_idx ON invoices (tenant_id); ALTER TABLE invoices ENABLE ROW LEVEL SECURITY; CREATE ROLE tenant_app NOLOGIN; GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO tenant_app; CREATE POLICY tenant_isolation ON invoices TO tenant_app USING (tenant_id = current_setting('app.tenant_id', true)::uuid) WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

USING filters rows that queries can see and modify. WITH CHECK rejects inserts and updates that would write a row for another tenant, so INSERT ... VALUES ('other-tenant', ...) fails instead of succeeding quietly.

Make sure the policy applies

Policies do not apply to superusers, roles with BYPASSRLS, or the table's owner unless the table is set to FORCE ROW LEVEL SECURITY. Many apps connect as the role that ran the migrations, which owns the tables, and their policies silently do nothing.

The cleanest fix is a role that owns nothing, and above, tenant_app is a NOLOGIN role with plain table grants. The application connects as its usual user, which must be a member of tenant_app, and switches into it inside the transaction with SET LOCAL ROLE. Once the current role is tenant_app, policies apply even if the login user is the owner, because RLS checks the current role.

Set the tenant per transaction

With a connection pool, every setting must be transaction-local: SET LOCAL and set_config(name, value, true) reset at commit or rollback; a plain SET stays on the connection and leaks into the next request that borrows it.

func WithTenant(ctx context.Context, pool *pgxpool.Pool, tenantID string, fn func(pgx.Tx) error) error { return pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error { if _, err := tx.Exec(ctx, `SET LOCAL ROLE tenant_app`); err != nil { return err } if _, err := tx.Exec(ctx, `SELECT set_config('app.tenant_id', $1, true)`, tenantID); err != nil { return err } return fn(tx) }) } // A query with no tenant filter still only sees one tenant's rows. err := WithTenant(ctx, pool, tenantID, func(tx pgx.Tx) error { rows, err := tx.Query(ctx, `SELECT id, number, total_cents FROM invoices ORDER BY number`) if err != nil { return err } defer rows.Close() // scan rows... return rows.Err() })

SET LOCAL ROLE cannot take a parameter, which is why the role name is a constant and the tenant ID goes through set_config with a bind parameter. Never build either statement with string concatenation from request data.

Test isolation, and know the costs

Write a test that inserts rows for two tenants, then runs SELECT count(*) FROM invoices under each tenant and with no tenant set, expecting the per-tenant count and zero. Add one that tries to insert a row with the other tenant's ID and expects an error. These tests catch the owner-bypass mistake immediately.

RLS adds the policy predicate to every plan, so the tenant_id index matters, and composite indexes should lead with tenant_id. Policies that call functions or subqueries per row are slow; keep them to a column comparison. Background jobs that work across tenants need a separate role with BYPASSRLS, used only by those jobs, as the Postgres CREATE POLICY reference describes for privileged access.

Best fit: shared-schema SaaS where a forgotten tenant filter would be a security incident. Pair it with application checks rather than replacing them.

How do you use RLS with Encore?

Encore

Encore is a backend framework with built-in infrastructure, and its Go database primitive gives you Postgres locally with encore run and on RDS or Cloud SQL in your own account after deploy. The piece that makes RLS fit is that each service connects with its own runtime role. Grant your tenant_app role to the billing service's runtime role in a migration, not to encore_services, which every service inherits and could use to set an arbitrary tenant and read invoices.

-- billing/migrations/2_rls.up.sql ALTER TABLE invoices ENABLE ROW LEVEL SECURITY; CREATE ROLE tenant_app NOLOGIN; GRANT tenant_app TO billing; -- the billing service runtime role, not encore_services GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO tenant_app; CREATE POLICY tenant_isolation ON invoices TO tenant_app USING (tenant_id = current_setting('app.tenant_id', true)::uuid) WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

The tenant comes from the auth handler, so endpoints never accept it as a parameter:

package billing import ( "context" "encore.dev/beta/auth" "encore.dev/storage/sqldb" ) var db = sqldb.NewDatabase("billing", sqldb.DatabaseConfig{Migrations: "./migrations"}) // UserData is set by the app's auth handler. type UserData struct { TenantID string } func withTenant(ctx context.Context, fn func(tx *sqldb.Tx) error) error { data := auth.Data().(*UserData) tx, err := db.Begin(ctx) if err != nil { return err } defer tx.Rollback() // no-op after Commit if _, err := tx.Exec(ctx, `SET LOCAL ROLE tenant_app`); err != nil { return err } if _, err := tx.Exec(ctx, `SELECT set_config('app.tenant_id', $1, true)`, data.TenantID); err != nil { return err } if err := fn(tx); err != nil { return err } return tx.Commit() } type Invoice struct { ID string `json:"id"` Number string `json:"number"` TotalCents int64 `json:"total_cents"` } type ListResponse struct { Invoices []Invoice `json:"invoices"` } //encore:api auth method=GET path=/invoices func List(ctx context.Context) (*ListResponse, error) { resp := &ListResponse{} err := withTenant(ctx, func(tx *sqldb.Tx) error { rows, err := tx.Query(ctx, `SELECT id, number, total_cents FROM invoices ORDER BY number`) if err != nil { return err } defer rows.Close() for rows.Next() { var inv Invoice if err := rows.Scan(&inv.ID, &inv.Number, &inv.TotalCents); err != nil { return err } resp.Invoices = append(resp.Invoices, inv) } return rows.Err() }) return resp, err }

When running tests, Encore sets up the databases in a separate local cluster with all migrations applied, so the isolation tests above run with encore test against Postgres with those policies applied. Policies and roles live in migration files in the repository, reviewed like code, and applied in every environment on deploy.

That is a good boundary for coding agents too: an agent writing a new report query cannot leak data across tenants by forgetting a filter, since the policy is enforced by Postgres for any query that runs inside withTenant.

Best fit: Go SaaS backends on Encore that want tenant isolation enforced in Postgres, with roles and policies shipped as migrations.

How to choose

Use RLS when tenants share tables and a missing filter would be a breach. Connect or switch to a role that does not own the tables, set the tenant with set_config(..., true) inside a transaction, index tenant_id, and test isolation. For very large tenants or strict data residency, separate schemas or databases per tenant may fit better; the multitenancy overview compares those models.

Frequently asked questions

How does row-level security work for multi-tenant apps?

Each table has a tenant_id column and a policy such as USING (tenant_id = current_setting('app.tenant_id')::uuid). The application sets app.tenant_id at the start of each transaction, and Postgres adds the policy condition to every query, so a query that forgets its WHERE tenant_id clause still only sees that tenant's rows.

Is it safe to use RLS with a connection pool?

Yes, if the tenant setting is transaction-scoped. Use SET LOCAL or set_config('app.tenant_id', $1, true) inside a transaction so the value disappears at commit or rollback. A session-level SET survives on the pooled connection and can leak one tenant's context into the next request.

Why does my RLS policy not filter anything?

Superusers, roles with BYPASSRLS, and the table owner skip policies by default. If your application connects as the owner of the tables, either use ALTER TABLE ... FORCE ROW LEVEL SECURITY or, better, switch to a separate role that does not own the tables, for example with SET LOCAL ROLE inside the transaction.

How do I use row-level security with Encore?

Create a NOLOGIN role and its policies in a migration and grant the role to encore_services, the role Encore grants to both migration and runtime database roles. In each request, open a sqldb transaction, run SET LOCAL ROLE for that role and set app.tenant_id with set_config, then run your queries.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders