PostgreSQL Native Row-Level Security

RowLevelSecurityNative delegates tenant isolation to PostgreSQL's built-in row-level security. Each query runs in a transaction that first calls set_config('app.tenant_id', <tenant>, true), and the CREATE POLICY clauses installed on tenant-scoped tables reference that session variable to filter rows. Quark cannot bypass the policy even from client.Raw() — the engine itself enforces isolation.

Native is PostgreSQL-only. Other dialects continue to use RowLevelSecurityClient, which is client-side WHERE injection on the Quark builder.

See ADR-0012 for the design rationale and the trade-offs against the legacy client-side strategy.

When to use Native vs Client

Concern	RowLevelSecurityNative	RowLevelSecurityClient
Where the filter is enforced	PostgreSQL engine	Quark query builder
client.Raw() / client.Exec()	Filtered by the policy	Bypasses the predicate
Dialects supported	PostgreSQL only	All six dialects
Setup cost	One CREATE POLICY per table	None
Per-query overhead	Implicit transaction + set_config	None
Recommended for	Production where bypass risk matters	Cross-dialect dev / staging

Native is not a drop-in upgrade of Client. The two are mutually exclusive per router. Pick Native when you want the engine to be the last line of defence and you can install policies; pick Client when portability across the six dialects matters more.

Setup

1. Install the policy on each tenant-scoped table

You have two options: run the DDL yourself, or embed the quarktenant library and run it from CI.

Option A — quarktenant CLI (recommended): embed the library in a small main.go that registers your models on the Client and delegates to quarktenant.Run. The library reads every registered model and emits one ALTER TABLE ... ENABLE/FORCE ROW LEVEL SECURITY pair plus one CREATE POLICY <table>_tenant_isolation per table.

// myapp/cmd/tenant/main.go
package main

import (
    "context"
    "os"
    "github.com/jcsvwinston/quark"
    "github.com/jcsvwinston/quark/quarktenant"
    _ "github.com/jackc/pgx/v5/stdlib"
    "myapp/models"
)

func main() {
    client, _ := quark.New("pgx", os.Getenv("QUARK_DSN"),
        quark.WithLimits(quark.Limits{AllowRawQueries: true}))
    defer client.Close()

    _ = client.RegisterModel(&models.Order{}, &models.Invoice{})
    os.Exit(quarktenant.Run(context.Background(), os.Args[1:], client))
}

Then in CI / Makefile:

# Print the DDL the library would emit (no DB change)
go run ./cmd/tenant install-rls-policies --dry-run

# Apply for real. Acquires a distributed migration lock first.
go run ./cmd/tenant install-rls-policies

# Override the tenant column or session variable
go run ./cmd/tenant install-rls-policies \
    --tenant-col=org_id \
    --native-rls-var=app.org_id

A complete runnable example lives at examples/tenant-rls-native/main.go.

Option B — manual DDL: write the SQL yourself and apply it through your normal schema-migration pipeline.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY orders_tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id', true)::text)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::text);

The WITH CHECK clause is what stops inserts and updates from escaping isolation; without it, a tenant could write rows for another tenant even though it cannot read them.

ALTER TABLE ... FORCE ROW LEVEL SECURITY is important: by default CREATE POLICY exempts the table owner. In most deployments the application role IS the owner, which would silently make the policy decorative. FORCE removes that exemption so the policy applies to every role.

UUID / BIGINT tenant IDs need an explicit cast

current_setting() always returns TEXT. When your tenant column is TEXT/VARCHAR the default ::text cast just works. When the column is UUID or BIGINT you must override the cast — pass --cast=uuid to the CLI, or write the policy DDL manually with ::uuid / ::bigint. Otherwise the comparison fails at runtime and the policy returns zero rows.

2. Configure the router

cfg := quark.DefaultTenantConfig()
cfg.Strategy = quark.RowLevelSecurityNative
cfg.BaseClient = pgClient
// cfg.NativeRLSVar = "app.tenant_id"   // default, override only if your policy uses a different name

router := quark.NewTenantRouter(cfg, ResolveTenant, nil)

NativeRLSVar defaults to "app.tenant_id". If your policies reference a different setting (e.g., app.org_id), set it here and keep both sides in sync.

3. Issue queries

Two paths, both transparent:

// Recommended for multi-step work: single transaction, single set_config.
err := router.Tx(tenantCtx, func(tx *quark.Tx) error {
    var orders []Order
    orders, err := quark.ForTx[Order](tenantCtx, tx).
        Where("status", "=", "paid").
        List()
    if err != nil { return err }
    // ... more queries against the same tx ...
    return nil
})

// Fire-and-forget single query: implicit transaction per call.
orders, err := quark.For[Order](tenantCtx, router).
    Where("status", "=", "paid").
    List()

Both paths reach PostgreSQL with current_setting('app.tenant_id', true) set to the resolved tenant. The policy filters rows server-side.

Implicit-transaction caveat

For[T](ctx, router) under Native wraps every executor call (ExecContext, QueryContext, QueryRowContext) in an implicit transaction. This is necessary because set_config(..., is_local=true) only takes effect within a transaction.

There is one trade-off: *sql.Rows and *sql.Row are opaque struct types from database/sql, so the implicit transactions are committed by a context.AfterFunc registered against the caller's context.Context rather than when the rows close. The practical consequences:

• Request-scoped ctx (typical HTTP handler): the tx commits when the handler returns. Connection released cleanly. Use this freely.
• Long-lived ctx (CLI batch jobs running many queries before cancellation): each query holds its connection open until ctx ends. With high tenant concurrency this can saturate the connection pool. Use router.Tx for batch jobs.
• Streaming via Iter / Cursor: same issue, plus the cursor must outlive the tx. Use router.Tx and run the iterator inside the callback.

The recommended pattern is therefore: router.Tx for everything non-trivial. The implicit-For[T] path is the ergonomic path for short reads in request-scoped contexts.

Failure modes

Query returns zero rows when it should return data

The most common symptom of "Native is not wired correctly". Causes:

1. The policy is not installed on the table. Run SELECT * FROM pg_policies WHERE tablename = '<your_table>'; and verify the policy exists and references the same setting (app.tenant_id by default).
2. set_config was never called on the current transaction. Happens when raw SQL is issued outside router.Tx or For[T](ctx, router). The setting reads back as NULL, the policy's comparison evaluates to NULL (i.e., false), every row is filtered out.
3. FORCE ROW LEVEL SECURITY is missing and the caller is the table owner. Without FORCE, the policy is decorative for the owner role. Re-emit the DDL with ALTER TABLE ... FORCE ROW LEVEL SECURITY.

ErrUnsupportedFeature: RowLevelSecurityNative requires PostgreSQL

Construction-time check. The router's BaseClient is not on PG. Native is PG-only; switch to RowLevelSecurityClient for the non-PG dialects in your deployment.

Insert fails with new row violates row-level security policy

The seeded tenant ID disagrees with current_setting('app.tenant_id'). Check that the model's tenant_id field is set before insert and matches the resolver's output for the current context.

Migration from RowLevelSecurityClient

The two strategies are mutually exclusive per router. To migrate an existing Client-strategy table to Native:

1. Install the policy + FORCE ROW LEVEL SECURITY.
2. Switch the router config to RowLevelSecurityNative on the next deploy.
3. Audit client.Raw() callers: under Client they were filtered by the Quark builder (which they were bypassing); under Native they are filtered by the engine (the policy applies). The "you might already be leaking" path becomes "the engine catches you".
4. Run the test suite. Any test that relied on the Client-strategy WHERE tenant_id = ? syntax being literally present in the SQL will need to be updated — the SQL no longer carries the predicate.

The legacy RowLevelSecurity alias still resolves to RowLevelSecurityClient. Migration to Native is an explicit opt-in.

Limitations and roadmap

• No LISTEN/NOTIFY integration. Inbound channel listening stays out of scope (ADR-0013). Outbound CRUD events ship via Client.UseEventBus.
• No automatic pg_temp cleanup. The policy stays on the table even after dropping the router; manage the policies in your normal schema migrations.
• The implicit-tx executor leaks tx until ctx ends. Acceptable for HTTP handlers; not acceptable for long-lived CLI ctx. Documented above. A follow-up may add an opt-out flag or a streaming-friendly executor.

The full Phase 5 roadmap is in TASKS.md under §"Fase 5" — F5-3 (CLI generator), F5-4..F5-6 (transactional hooks + EventBus), and F5-7 (audit log).