Postgres row-level security, without the footguns

Postgres row-level security (RLS) is one of the most powerful tools in the database for enforcing multi-tenancy and data isolation. It's also one of the easiest to get subtly wrong in ways that don't fail until production. This is a field guide based on three production rollouts where we used RLS as the primary tenant boundary.

What RLS actually does

Row-level security is a per-row authorization layer enforced inside Postgres. You enable RLS on a table, declare policies that define which rows a query can see or modify, and Postgres rewrites every query to include those predicates automatically. The application layer cannot forget to apply the filter — the database enforces it.

For multi-tenant SaaS, this is enormously valuable. A bug in your ORM query, a missing WHERE tenant_id = ?, a developer pulling a shortcut — none of those leak data across tenants if RLS is configured correctly. The database is the last line of defence, and it doesn't get tired.

The defaults that bite

• RLS is off by default on new tables. Every migration that adds a table needs an explicit ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Forget it once and that table has no isolation.
• Table owners and superusers bypass RLS.If your application connects as the table owner (common in dev), policies don't apply. Tests pass; production leaks.
• RLS only applies after a policy exists.Enabling RLS with no policies means the table is effectively empty for non-owners. That's safer than the inverse, but a fresh migration that enables RLS without adding a policy will break every query.
• FORCE ROW LEVEL SECURITY closes the owner bypass. Always set it. Without it, your application user — if it owns the table — silently sees everything.

The multi-tenant pattern

The pattern we use on every multi-tenant Postgres database. Add a tenant_id column to every tenant-scoped table, set a session variable per request, write one policy per table.

-- One-time setup per tenant table.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

-- Per-request, after acquiring a connection.
SET LOCAL app.current_tenant = '';

USING filters reads. WITH CHECK validates writes. You need both. Without WITH CHECK, a tenant couldINSERTrows with someone else's tenant_id and they'd stick.

Session variables and connection pools

The biggest production footgun: SETpersists for the lifetime of a connection. If you use a connection pool — and you do — the next request that picks up that connection inherits the previous tenant's session variable. Cross-tenant data leak.

Two fixes, both mandatory:

• Always use SET LOCAL, never SET. SET LOCAL is scoped to the current transaction and reverts on commit/rollback. Wrap every request in a transaction.
• Reset the variable on connection acquire. A defensiveRESET app.current_tenant at the start of every request catches connections released without a transaction commit.

func withTenant(ctx context.Context, db *sql.DB, tenantID string, fn func(*sql.Tx) error) error {
  tx, err := db.BeginTx(ctx, nil)
  if err != nil { return err }
  defer tx.Rollback()

  // SET LOCAL — reverts on commit/rollback.
  if _, err := tx.ExecContext(ctx,
    "SET LOCAL app.current_tenant = $1", tenantID); err != nil {
    return err
  }
  if err := fn(tx); err != nil { return err }
  return tx.Commit()
}

Testing RLS, properly

RLS is the kind of feature where unit tests don't help. You need integration tests that actually connect as the application user, set the tenant variable, and assert that cross-tenant queries return zero rows. The test suite must include:

• A test that creates two tenants, writes data as each, and asserts that one cannot read the other's rows.
• A test that asserts an UPDATE without the correct tenant_id fails or affects zero rows.
• A test that runs as the table owner and asserts FORCE ROW LEVEL SECURITY still applies.
• A test that exercises the connection pool — repeated requests with different tenants — and asserts no leakage.

Performance pitfalls

RLS policies are query predicates. They participate in query planning like any WHERE clause. Two performance considerations worth knowing:

• Index your policy predicate. Every tenant-scoped table needs an index that includes tenant_id. Without it, the planner does sequential scans on what should be index lookups.
• current_setting() is cheap, but not free. Use the missing_ok = true form when the variable might be unset, and consider STABLE-marked wrapper functions if you see it appearing as a per-row cost in EXPLAIN ANALYZE.

RLS is not a substitute for application-layer authorization. Use it as a defence-in-depth boundary that catches the bugs your authorization layer misses. If you're designing a multi-tenant Postgres schema and want a review, we've done this a few times. The decisions around isolation are hard to unwind once you're in production.