Row Level Security in Postgres Driven From Go Services

The usual defence against one tenant seeing another's rows is a where tenant_id = $1 in every query, written by every developer, forever. It works until someone forgets it once. Row level security (RLS) moves the filter into PostgreSQL. You attach a policy to a table, and the database adds the condition to every query on it, including the ones you did not write carefully. It is a safety net for the tenant_id column layout, not a replacement for good queries. This article drives it from Go 1.25.6 with pgx against PostgreSQL 18.6 and runs the failure cases, because RLS has three traps that make it look enabled when it is not.

It is part of the database cornerstone, and its security theme continues in SQL injection in Go.

The policy

The lab creates a table of documents for three tenants, 1,000 each, turns RLS on, and adds one policy:

alter table docs622 enable row level security;
create policy tenant_isolation on docs622
  using      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::int)
  with check (tenant_id = nullif(current_setting('app.tenant_id', true), '')::int);

using filters what a statement can see or modify; with check rejects rows being written that would fail it. current_setting('app.tenant_id', true) reads a custom setting, and the true means "return null instead of raising an error when it is not set". The nullif(..., '') turns an empty string into null, because a custom setting that was once set reads back as an empty string after it is reset. A comparison with null is never true, so no tenant means no rows, which is the safe default.

The application connects as a role, app622, that is not a superuser and does not own the table. The Go side sets the tenant at the start of each request's transaction:

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"time"

	"github.com/jackc/pgx/v5/pgconn"
	_ "github.com/jackc/pgx/v5/stdlib"
)

const (
	adminDSN = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"
	appDSN   = "postgres://app622:[email protected]:55432/lab?sslmode=disable"
)

func must(db *sql.DB, q string) {
	if _, err := db.Exec(q); err != nil {
		log.Fatalf("%s: %v", q, err)
	}
}

func count(ctx context.Context, q interface {
	QueryRowContext(context.Context, string, ...any) *sql.Row
}, query string) (n int, err error) {
	err = q.QueryRowContext(ctx, query).Scan(&n)
	return
}

func main() {
	ctx := context.Background()
	admin, err := sql.Open("pgx", adminDSN)
	if err != nil {
		log.Fatal(err)
	}
	defer admin.Close()
	for _, q := range []string{
		"drop table if exists docs622",
		"drop role if exists app622",
		"create role app622 login password 'apppw'",
		"create table docs622 (id bigserial primary key, tenant_id int not null, body text not null)",
		"insert into docs622 (tenant_id, body) select t, 'doc ' || i from generate_series(1, 3) t, generate_series(1, 1000) i",
		"alter table docs622 enable row level security",
		`create policy tenant_isolation on docs622
		   using (tenant_id = nullif(current_setting('app.tenant_id', true), '')::int)
		   with check (tenant_id = nullif(current_setting('app.tenant_id', true), '')::int)`,
		"grant select, insert, update, delete on docs622 to app622",
		"grant usage on sequence docs622_id_seq to app622",
	} {
		must(admin, q)
	}

	app, err := sql.Open("pgx", appDSN)
	if err != nil {
		log.Fatal(err)
	}
	defer app.Close()
	app.SetMaxOpenConns(1) // one connection, so every request reuses the same session, as a busy pool does

	// withTenant is the request scope: set_config(..., true) lasts for this transaction only.
	withTenant := func(tenant int, fn func(tx *sql.Tx) error) error {
		tx, err := app.BeginTx(ctx, nil)
		if err != nil {
			return err
		}
		defer tx.Rollback()
		if _, err := tx.ExecContext(ctx, "select set_config('app.tenant_id', $1, true)", fmt.Sprint(tenant)); err != nil {
			return err
		}
		if err := fn(tx); err != nil {
			return err
		}
		return tx.Commit()
	}

	// 1. The query forgot its WHERE clause, and still sees only its own tenant.
	_ = withTenant(2, func(tx *sql.Tx) error {
		n, _ := count(ctx, tx, "select count(*) from docs622")
		fmt.Println("1. tenant 2, select count(*) with no filter:", n, "(table holds 3000)")
		return nil
	})

	// 2. No tenant set: the policy matches nothing.
	n, _ := count(ctx, app, "select count(*) from docs622")
	fmt.Println("2. no tenant set at all:", n)

	// 3. WITH CHECK stops a write into another tenant.
	err = withTenant(2, func(tx *sql.Tx) error {
		_, err := tx.ExecContext(ctx, "insert into docs622 (tenant_id, body) values (3, 'smuggled')")
		return err
	})
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) {
		fmt.Printf("3. tenant 2 inserting a row for tenant 3: SQLSTATE %s %s\n", pgErr.Code, pgErr.Message)
	}

	// 4. A session level setting is the trap: it outlives the request.
	_, _ = app.ExecContext(ctx, "select set_config('app.tenant_id', '1', false)")
	n, _ = count(ctx, app, "select count(*) from docs622")
	fmt.Println("4. a request that set the tenant for the session leaves it behind; the next request, with no tenant of its own, sees", n, "rows of tenant 1")
	_, _ = app.ExecContext(ctx, "reset app.tenant_id")

	// 5. The table owner and superusers skip the policy unless it is forced.
	n, _ = count(ctx, admin, "select count(*) from docs622")
	fmt.Println("5. the superuser, no tenant set:", n)
	must(admin, "alter table docs622 owner to app622")
	n, _ = count(ctx, app, "select count(*) from docs622")
	fmt.Println("   the owner role app622, no tenant set:", n)
	must(admin, "alter table docs622 force row level security")
	n, _ = count(ctx, app, "select count(*) from docs622")
	fmt.Println("   the owner after force row level security:", n)
	must(admin, "alter table docs622 no force row level security")
	must(admin, "alter table docs622 owner to postgres")
	must(admin, "grant select, insert, update, delete on docs622 to app622")

	// 6. What the policy costs on a bigger table.
	must(admin, "insert into docs622 (tenant_id, body) select (i % 3) + 1, 'bulk' from generate_series(1, 300000) i")
	must(admin, "create index docs622_tenant on docs622 (tenant_id)")
	must(admin, "analyze docs622")
	bench := func(label string, run func() error) {
		start := time.Now()
		for i := 0; i < 100; i++ {
			if err := run(); err != nil {
				log.Fatal(err)
			}
		}
		fmt.Printf("   %-42s %v per query\n", label, (time.Since(start) / 100).Round(10*time.Microsecond))
	}
	fmt.Println("6. count of one tenant's rows (about 101,000), 100 runs")
	bench("explicit where tenant_id = 2, no policy", func() error {
		_, err := count(ctx, admin, "select count(*) from docs622 where tenant_id = 2")
		return err
	})
	bench("policy only (set_config in a transaction)", func() error {
		return withTenant(2, func(tx *sql.Tx) error { _, err := count(ctx, tx, "select count(*) from docs622"); return err })
	})
}

1. tenant 2, select count(*) with no filter: 1000 (table holds 3000)
2. no tenant set at all: 0
3. tenant 2 inserting a row for tenant 3: SQLSTATE 42501 new row violates row-level security policy for table "docs622"
4. a request that set the tenant for the session leaves it behind; the next request, with no tenant of its own, sees 1000 rows of tenant 1
5. the superuser, no tenant set: 3000
   the owner role app622, no tenant set: 3000
   the owner after force row level security: 0
6. count of one tenant's rows (about 101,000), 100 runs
   explicit where tenant_id = 2, no policy    8.74ms per query
   policy only (set_config in a transaction)  11.8ms per query

withTenant opens a transaction and runs select set_config('app.tenant_id', $1, true). The third argument, true, means the setting is local to the transaction and disappears at commit or rollback. That detail is the difference between a working isolation layer and an incident.

What the run shows

1. A forgotten filter is harmless. In tenant 2's transaction, select count(*) from docs622 with no where returned 1000 of the 3,000 rows. The query was careless, and the policy made it correct.

2. No tenant, no data. With nothing set, a count returned 0. A code path that runs outside the request scope fails closed.

3. Writes are checked too. Tenant 2 inserting a row with tenant_id = 3 failed with SQLSTATE 42501, new row violates row-level security policy for table "docs622". That is with check at work. Without it, a tenant could write rows it could no longer see, or plant rows in another tenant's data. Use a using and a with check clause together in nearly every policy.

4. The session level trap. The program then called set_config('app.tenant_id', '1', false), with the last argument false, which makes the setting last for the session. It reset nothing afterwards. The next request on that connection, which set no tenant of its own, saw 1000 rows: tenant 1's. The pool had one connection, so every request reused it, which is what a busy pool does in practice. A single handler that sets the tenant outside a transaction, or with false, poisons the connection for the next user. Always use true inside a transaction, wrap it in the request scope helper so nobody calls set_config directly, and treat any other use as a security bug.

5. The owner and superusers bypass it. A superuser counted all 3000 rows with no tenant set. The table owner did too, until the table was altered with force row level security, after which the owner saw 0. By default RLS does not apply to superusers, to roles with bypassrls, or to the table's owner. The role the application uses must be none of these, and migrations should run as a different, more privileged role. A common mistake is running the app as the role that created the tables, so the policy exists and does nothing. Check it from Go at start up: select rolsuper or rolbypassrls from pg_roles where rolname = current_user and refuse to serve if true.

6. The cost. On 300,000 more rows with an index on tenant_id, counting one tenant's roughly 101,000 rows took 8.74ms with an explicit where tenant_id = 2 and no policy, and 11.8ms through the policy inside a transaction that also called set_config. The difference includes the transaction round trips. The policy expression is added as a filter and the index is still used, so the overhead is modest. The caveat is that a policy expression the planner cannot push into an index scan, one calling a function not marked leakproof or doing a subquery, can be much slower, so look at the plans of your busiest queries with the policy on.

Writing policies that stay fast

• Keep the expression a plain comparison of an indexed column with a setting. A policy that runs a subquery against a membership table runs it for every row unless the planner can fold it.
• Index the column, and put it first in the indexes your queries use, as in the composite keys of the tenant model.
• Cast once. current_setting(...)::int is evaluated per row if the planner cannot treat it as a constant; wrapping the call in (select current_setting(...)::int) makes it an init plan evaluated once, which is the documented trick for heavy tables.
• Separate read and write policies with for select, for insert and so on when they differ, for example when admins may read across tenants but not write.

What RLS does not do

It does not stop a bug in the setting. If the middleware sets the wrong tenant, RLS enforces the wrong tenant perfectly. It does not protect COPY, replication or pg_dump run by a privileged role, or data copied into tables without policies, or cached results in Redis. It does not apply to views owned by a privileged role unless the view is created with security_invoker. And unique constraints and foreign keys are still checked globally, which can leak the existence of a row in another tenant through an error message, so be careful what you return from a constraint failure; see handling database errors.

Using it in a Go service

1. Create the application role without superuser and bypassrls, grant it only what it needs, and keep migrations on another role.
2. Apply enable row level security and force row level security to every tenant table, and have a test that fails when a table with a tenant_id column lacks a policy: query pg_class.relrowsecurity in CI.
3. Put tenant setting in one function that every request transaction uses, as in the lab's withTenant, and make a unit of work do it; see unit of work.
4. Keep the explicit where tenant_id = $1 in your queries as well. The policy catches mistakes; the filter keeps the plan obvious and the intent readable.
5. Test isolation with two tenants through the real handlers, including with an unset tenant.

If you use PgBouncer in transaction pooling mode, set_config(..., true) inside a transaction still works, because the setting lives and dies within the transaction, which is another reason to prefer it over session level settings. The feature is documented in the PostgreSQL manual under row security policies, including the default bypass rules, and set_config in the system administration functions.