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.

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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.