Migration-role RLS posture

Issue: #2781 · P1 · prod-enablement · milestone 32

Gates: any migration-bearing deploy to Cloud SQL — every prod promotion of upsquad-core, and first external-tenant onboarding on hosted/SaaS. It does not gate on-prem, where the operator controls the DB role; on-prem should confirm its posture with the one-line check below rather than assume it is exposed.

---

The one thing to know

DELETE/UPDATE on a table with FORCE ROW LEVEL SECURITY affects zero rows and reports success when the connected role neither is a superuser nor holds BYPASSRLS. It does not error. The exit code is 0. psql prints DELETE 0, which is the same thing it prints when there was genuinely nothing to delete.

Measured on pgvector:0.8.0-pg16 — same statement, same database, same instant, only the role differing:

lane	role	result	exit	rows left
CI / dev	bootstrap superuser	DELETE 3	0	0
Cloud SQL	table owner, NOSUPERUSER NOBYPASSRLS	DELETE 0	0	3

120 tables in this schema are FORCEd, with a policy keyed on the app.org_id GUC that migrations never set. FORCE is precisely what removes the table owner's exemption, and migrations run as the owner.

Check the posture

SELECT current_user,
       rolsuper,
       rolbypassrls,
       rolsuper OR rolbypassrls AS can_see_tenant_rows
  FROM pg_roles
 WHERE rolname = current_user;

can_see_tenant_rows = f on a migration connection means every cross-tenant backfill in the chain is a no-op that will report success.

Grant it

psql -v migrate_role=<role> -v migrate_password=<pw> -f dev/postgres/migrate-role.sql

The script is idempotent and ends with a fail-closed self-check that raises if the role did not end up able to bypass RLS.

Who is allowed to grant it

PostgreSQL 16 requires the grantor to already hold the attribute:

ERROR:  permission denied to alter role
DETAIL: Only roles with the BYPASSRLS attribute may change the BYPASSRLS attribute.

Reproduced on 16.10 as a NOSUPERUSER CREATEROLE role holding ADMIN OPTION on the target — i.e. exactly Cloud SQL's cloudsqlsuperuser shape.

⚠ Open question for Cloud SQL — not resolved by this runbook

Cloud SQL's documentation lists cloudsqlsuperuser's attributes as CREATEROLE, CREATEDB, LOGIN, and states the postgres user "does not have the SUPERUSER or REPLICATION attributes". BYPASSRLS is not among them. If that is accurate, no reachable role on a Cloud SQL instance can grant BYPASSRLS, and the #2781 decision's mechanism is unavailable there.

This was not verifiable from the devbox — the GCP environment is dormant. Before any Cloud SQL deploy, run on the instance:

SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolsuper OR rolbypassrls;

• A role you can log in as appears → grant it and proceed.
• Nothing appears → the posture is unattainable; re-open #2781. The two fallbacks, neither of which is chosen here because it is a founder call:
  • a permissive per-role policy — CREATE POLICY … FOR ALL TO <migrator> USING (true) WITH CHECK (true) on each FORCE'd table. Grantable by the table owner, per-role like BYPASSRLS, but 118 policies of new surface.
  • migration 211's ALTER TABLE … NO FORCE / … FORCE pair, per statement — the original Option B, rejected because 37 of 50 sites already got it wrong and its failure mode is silent.

What enforces this

Mechanism	Where	Fails how
Migration preflight	migration 214_migration_role_rls_posture	RAISEs and stops the chain if the migration role cannot bypass RLS
Per-migration guard	the 10 backfills that used to carry SET LOCAL row_security = off	same, inline, so it holds regardless of chain position
Static lint	scripts/check-migration-rls-dml.py	exit 1 on DML whose bypass has been defeated
Runtime lane	hack/verify-migrate-role.sh (CI: Migrate as a non-superuser owner)	asserts rows affected, not exit code
Startup assertion	internal/dbguard	the service refuses to boot as the migration role

SET LOCAL row_security = off is not a bypass

Twelve migrations reached for it believing it was. Under FORCE it raises:

ERROR:  query would be affected by row-level security policy for table "context_embeddings"
HINT:   To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.

Postgres names the correct fix in its own hint. Those twelve were a bug but not a hazard — they fail loudly. The 37 silent-zero sites were the danger, and they are the ones migration 214 now covers. All twelve row_security = off lines were removed in #2781 and replaced with the inline posture assertion.

Reading a DELETE 0

If an operator statement against a tenant table prints DELETE 0 and you expected rows, do not conclude the rows were absent. Check the posture first, then count:

SELECT count(*) FROM <table> WHERE <the same predicate>;

A non-zero count after a DELETE 0 is this bug, not a race.

Admin sweeps need a role that can read across orgs (#3495, #3498)

A second population has the OPPOSITE precondition to a service. These tools find their work with a deliberately unscoped query over every tenant's rows, so that one invocation repairs a whole environment. Under FORCE ROW LEVEL SECURITY on a role RLS applies to, that query is filtered to zero rows with no error — the tool reports nothing to do and exits 0.

tool	run it as	scoped alternative
memory-backfill	a BYPASSRLS role	none
memory-extraction-replay	a BYPASSRLS role	none
memory-approval-repair	a BYPASSRLS role	-org <uuid> -agent <uuid> together
memory-status-reevaluate	a BYPASSRLS role	-org <uuid> -agent <uuid> together
memory-summary-backfill	a BYPASSRLS role	none
memory-eval-freeze	a BYPASSRLS role	none
rag-reembed	a BYPASSRLS role	none
agent-key-inventory	a BYPASSRLS role	none

Seven of them call dbguard.AssertCrossOrgReader before their first read and refuse by name rather than sweeping nothing. agent-key-inventory refuses the same way through its own keyinventory.ErrRLSBlind until #3506 converges it onto the shared assertion; the operator-facing behaviour is already identical. There is no environment variable that turns the refusal off: "this sweep saw nothing and said converged" is not an operational state anybody wants, it is the defect.

After the #3259 cutover

Once DATABASE_URL points every service at the NOSUPERUSER NOBYPASSRLS app_rw role, these tools must not inherit that DSN. Give the invocation its own:

# The migration role is the only role on this schema that can read across orgs
# today. dev/postgres/migrate-role.sql provisions it NOSUPERUSER BYPASSRLS.
DATABASE_URL="$MIGRATE_DATABASE_URL" go run ./cmd/tools/memory-backfill

On Kubernetes that means the tool's Job takes the migrate DSN secret (upsquad-db-jobs-style), not the app one. On docker-compose.tenant.yml, run it with -e DATABASE_URL=... rather than inheriting the service environment.

Three things NOT to do, in decreasing order of how tempting they are:

• Do not grant BYPASSRLS to app_rw. That un-does the entire cutover, for every service, to make one maintenance job work.
• Do not widen a policy. Same effect, quieter.
• Do not point it at the schema OWNER. FORCE ROW LEVEL SECURITY removes the owner's exemption — that is the whole point of FORCE — so an owner without BYPASSRLS is refused exactly as app_rw is. The refusal message says so.

The long-term answer is a dedicated cross-tenant platform_rw role (#1908 bucket (a)). When it exists, these tools get pointed at it and the assertion keeps passing unchanged.

The partial-scope trap

memory-approval-repair and memory-status-reevaluate accept -org and -agent, and both are required for the scoped mode to work. agent_memory's policy is

org_id = app.org_id AND (agent_id = app.agent_id OR app_has_share_grant(id))

so -org alone sets app.org_id, leaves app.agent_id NULL, and the AND's second arm is NULL for every row. Measured on app_rw against a corpus holding one stranded row in the named org: -org alone reported candidates=0 and exited 0; -org -agent reported 1. Since #3498 the partial scope is refused with a message naming the flag that is missing.

-memory <uuid,…> is a SQL filter, not a GUC, and never satisfies the policy on its own.