Skip to main content

Enforcing RLS with the restricted align_app role

Align isolates tenants at two layers: the application always scopes queries to a single tenant (withTenant / SET LOCAL app.current_tenant), and PostgreSQL Row-Level Security (RLS) is a hard backstop that filters every row by tenant_id.

RLS only protects you if the app connects as a non-owner role - PostgreSQL lets a table owner bypass RLS. This is the same runbook for a new install and for an existing one. Installation walks a new install through the same commands right after migrations run, because the role does not exist before then.

It is automatic if you set rlsCutover.enabled (see the next section). Otherwise you set align_app's password and add it to the database secret yourself. This page also covers brain, which the install guide does not.

The migrations job keeps using the owner connection, because it creates roles, tables and FORCE ROW LEVEL SECURITY policies.

Let the chart do it (ALI-1806)​

Set rlsCutover.enabled: true. A hook Job runs after the migrations and before the gateway starts. It sets the align_app login password, refuses a role that could bypass RLS, and connects with app-url to confirm the gateway's connection works. It replaces steps 2 and 5 below. Two keys must exist in the database.secretName Secret first:

  • app-password: the password to set.
  • app-url: postgresql://align_app:<that password>@<host>:5432/<db>.

postgresql.enabled: true generates both. With your own database, add them yourself. The Job is safe to repeat on every upgrade. It does not restart a gateway that is already running, so on an existing release restart it once after the first run. Brain stays on the owner connection, as described under Brain below.

If you use RDS Proxy (read this first)​

When the app connects through RDS Proxy (DATABASE_APP_URL points at a *.proxy-* endpoint), two things must be true before the cutover works - both were real failures caught during the preview rollout:

  1. Register align_app with the proxy. RDS Proxy keeps its own credential registry; a role it doesn't know is rejected (This RDS proxy has no credentials for the role align_app) even though the role authenticates fine directly. In align-infra this is the additional_roles input on the rds-proxy module (creates a Secrets Manager secret + an extra proxy auth block + IAM access). Apply that before re-sealing.

  2. No libpq options startup parameter. RDS Proxy rejects command-line options (Feature not supported: RDS Proxy currently doesn't support command-line options). The gateway sets statement_timeout per-connection via a pool connect handler (not options: '-c statement_timeout=...'); make sure your gateway image includes that fix before pointing DATABASE_APP_URL at the proxy, or every query fails FATAL.

Direct-to-RDS deployments (no proxy) are unaffected by both.

One-time cutover​

1. Create the role (automatic)​

Migration 073_create_align_app_role.sql creates align_app as a NOLOGIN role with least-privilege grants. It runs with every migration job - nothing to do.

2. Give the role a password (in-cluster)​

RDS is typically private, so run this from inside the cluster. Start a pod from the migrations image, which has psql and reaches the database like the migrations job does, then:

psql "postgres://<owner-user>:<owner-pass>@<host>:5432/<db>" -v ON_ERROR_STOP=1 \
  -v app_pw="<align_app password>" <<'SQL'
ALTER ROLE align_app WITH LOGIN PASSWORD :'app_pw';
SQL

Pick any strong password and use the same value in step 3. psql fills in :'app_pw' from stdin and quotes it, so the shell never builds the password into SQL. A -c string would not be filled in. The command fails if the role does not exist yet, which means migration 073 has not run.

3. Re-seal the database secret with app-url​

Add an app-url key to the align-database secret, using the same tool you use for your other secrets (kubectl, Sealed Secrets, External Secrets). It is the align_app connection string:

postgresql://align_app:<align_app password>@<host>:5432/<db>

The owner url stays in the secret, because migrations use it.

4. Roll the gateway​

The gateway prefers DATABASE_APP_URL (wired from the optional app-url secret key) over DATABASE_URL. Once the key exists, restart the gateway:

kubectl rollout restart deploy/align-gateway -n align-<env>

Until app-url exists, DATABASE_APP_URL is unset and the gateway falls back to the owner DATABASE_URL, so deploying the Helm change ahead of the secret is non-breaking.

PgBouncer

DATABASE_APP_URL is only wired on the direct-connection path. When the gateway's PgBouncer sidecar is enabled (gateway.pgbouncer.enabled: true), the chart does not emit app-url, because it points at the database directly and the gateway prefers it over the PgBouncer connection - so the cutover requires PgBouncer disabled until the app role can be routed through the sidecar (a separate change).

5. Verify​

As the owner, confirm RLS is enabled AND forced:

SELECT relname, relrowsecurity, relforcerowsecurity
  FROM pg_class WHERE relname = 'decision_snapshots';
-- relrowsecurity = t, relforcerowsecurity = t

Then connect directly as align_app (using the app-url connection string, not SET ROLE - the owner is usually not a member of align_app, and SET ROLE would fail on a typical RDS setup). Confirm both the effective user and that a tenant-scoped read is filtered:

psql "postgres://align_app:<align_app-pass>@<host>:5432/<db>"
SELECT current_user;  -- align_app
SELECT set_config('app.current_tenant', '<tenant-uuid>', false);
SELECT count(*) FROM decision_snapshots;  -- only this tenant's rows

Brain (optional follow-up)​

The brain service connects via discrete POSTGRES_USER / POSTGRES_PASSWORD keys and also sets app.current_tenant, so it benefits from the same backstop. Pointing brain at align_app needs a small change to read app-specific credentials; it is tracked separately. Brain remains on the owner connection until then - the gateway cutover above is independent and safe on its own.

Rollback​

Remove (or blank) the app-url key and re-seal; the gateway falls back to the owner DATABASE_URL on its next restart. No data migration is involved.