# Running rowstile

Upgrading, backups, retention and what to watch. Applying is the `rowstile` command, run as the owner of
the tables; everything else here is plain SQL.

## Configuration

- **Sign in with `authz.act_as()`**, first in each transaction (the clients and SDKs do). The session is
  signed, so the app role can't change who is signed in by setting `authz.user_id`; only the app role (and
  its members) may call `act_as`, and services sign in with `authz.login_key()` or `authz.login_jwt()`.
  `SELECT * FROM authz.connection_check()` at start-up says what is wrong with the app's connection.
- **Memory**: the inheritance tables are read on every check. Give Postgres enough shared buffers for
  the database's hot part; on the benchmark of 2026-10-03 (a build from before the rename, [`core/bench/README.md`](/benchmark.md)),
  250k folders needed more than 1 GB and a million (a 6 GB database, 3.4 GB of it inheritance tables:
  [`core/bench/results/2026-10-03-20000000-files-pg16.txt`](https://github.com/rowstile/rowstile/blob/v0.1.0-beta.5/core/bench/results/2026-10-03-20000000-files-pg16.txt)) more than 4 GB.
- **Check your setup**: `SELECT * FROM authz.lint()` lists ways around the rules it can see.

## Behind a pooler

rowstile signs in each transaction, so a pooler in transaction mode works for the app's connection. Every
night every conformance suite (each SDK's, and the sync engines') runs with the app's connection through PgBouncer
1.26 in transaction mode, keeping prepared statements ([`integrations/pooler.sh`](https://github.com/rowstile/rowstile/blob/v0.1.0-beta.5/integrations/pooler.sh)); the owner and the change feed connect directly.
Supavisor (Supabase's pooler) was tried by hand, before 0.1.0-alpha.1: [Managed Postgres](/managed-postgres.md).
Session mode works as plain connections do. Statement mode can't: signing in and the queries after it are one
transaction, and it refuses transactions.

- **The change feed needs its own connection to Postgres.** `LISTEN` lasts as long as the session, and a pooler
  in transaction mode gives the session to other clients between transactions: the feed connects and hears
  nothing, with no error. Give `changes()` (and any `LISTEN` of your own) the database's direct URL, or a
  pooler in session mode (Supabase's session pooler, Neon's host without `-pooler`).
- **Prepared statements.** A pooler that keeps them per client (PgBouncer 1.21 or newer with
  `max_prepared_statements` above 0) needs nothing. One that doesn't (older PgBouncer,
  `max_prepared_statements = 0`, Supavisor in transaction mode) needs the drivers to make none:
  - asyncpg, with SQLAlchemy: `?prepared_statement_cache_size=0` on the URL and
    `connect_args={"statement_cache_size": 0}`;
  - psycopg: `prepare_threshold=None` (`ConnectionPool(url, kwargs={"prepare_threshold": None})`, or in
    SQLAlchemy's `connect_args`);
  - postgres.js: `{ prepare: false }`. node-postgres and Prisma need nothing;
  - pgx (Go): `default_query_exec_mode=exec` on the URL, or `QueryExecModeExec` in the pool's
    `ConnConfig.DefaultQueryExecMode`. pgx's default prepares each statement by name, the SDK's sign-in too.

  Without it, requests fail with "prepared statement ... does not exist" or "already exists". They fail; nobody
  is signed in as someone else. With no prepared statements, each query is planned anew, the policy's checks
  with it: [what that costs](/reference/limits.md#limits).

  These settings come from trials by hand on Supabase's transaction pooler, before 0.1.0-alpha.1 ([Managed
  Postgres](/managed-postgres.md)); pgx's from one on PgBouncer 1.26 with `max_prepared_statements = 0`, after
  0.1.0-beta.3. The nightly run keeps prepared statements, so no check runs them.
- **Nothing may outlive a transaction.** The next transaction on that server connection may be another
  client's. A session-level `SET`, a temporary table, a statement prepared by name, or a `WITH HOLD` cursor in
  your own code reaches it: a cursor declared while signed in as one user is read by the next client, signed
  in or not. This holds for any pool. rowstile's own sign-in lasts one transaction.
- **Migrations and the command** log in as the owner. Give them the database's direct URL, or a pooler in
  session mode, as the change feed: that is how the nightly run connects them, and how [Managed
  Postgres](/managed-postgres.md) sets them up. No check runs them through a pooler in transaction mode. The
  app logs in as the app role (`authz.connection_check()` judges the role the app's connection logs in as).
- **PgCat** (1.2, tried by hand before 0.1.0-alpha.1) doesn't serve asyncpg or postgres.js: it doesn't handle
  a message they send when they prepare, and their first query never returns. It also drops a connection's
  `options`. node-postgres and Prisma work through it.

## On managed Postgres

The role a managed service gives you owns your tables but isn't a superuser, which is all rowstile needs. Make
the app role with SQL and give it to the owner once (`GRANT app_user TO CURRENT_USER`). [Managed
Postgres](/managed-postgres.md) has the setup, and what we found on Neon and Supabase: their connection strings,
poolers, passwords, test databases and, on Supabase, the owner's search path.

## Deploying a policy change

A policy change is a migration: `rowstile migrate` writes it for the tool in `rowstile.toml`
(`[migrations]`), from the lock file (`db/policy.lock`, committed next to the policy), and the deploy runs
it with the app's other migrations, as the owner of the tables. There is no separate step. In CI,
`rowstile migrate --check` fails a change to the policy that has no migration.

- A migration holds only what changed. Most take milliseconds and lock the app's tables only where a
  rule or a trigger on them changes (the migration's first comments say what changed; `rowstile review`
  says which tables it locks, and in which mode). Each waits at most 10 seconds for a table (`lock_timeout`,
  unless the migration tool set one), then fails, rather than queueing every query behind it; run it again
  when the database is quieter.
- A change to how something inherits comes as two migrations when it can: the first builds the new
  inheritance table beside the one in use while the app runs (it reads the app's tables, and takes as
  long as the backfill takes: seconds for tens of thousands of folders, 40 seconds at 250k, three minutes
  at a million); the
  second swaps it in, in a short transaction. Deploy them one after the other, or together: the second
  catches up with whatever the app changed in between. Other inheritance changes rebuild the table in one
  migration, with the app's tables locked while it runs.
- A migration checks that the database is where the one before it left it, so migrations applied out of
  order, or on a database changed by hand since (`rowstile push` or `apply`), stop with a message.
- Preview first with `rowstile diff`: who gains and loses what. It makes the new policy in a transaction it
  undoes, so the app's tables stay locked while it runs: run it on a copy of production or a staging database,
  not on production at its busiest.

`rowstile apply db/policy.authz` still applies the whole policy in one transaction (keeping inheritance
tables that didn't change); use it for databases that don't take migrations. `rowstile push`
is for development databases.

## Upgrading rowstile

1. Install the new version of the `rowstile` command.
2. `rowstile migrate` writes what the new version makes differently, as the next migration; deploy it. If
   it makes the same, there is nothing to write, and `rowstile migrate --check` passes as before.
   (Databases that don't take migrations: `rowstile reapply`.)

This holds from 0.1.0-beta.1 on: the beta started afresh. A database an alpha (0.1.0-alpha.9 or earlier) made
is not upgraded: apply the policy from scratch, on a new database, or on the same one after
`rowstile remove --yes` (shares and history stay), then `rowstile apply`. Then upgrade from release to release
as above.

The tests ([`core/tests/upgrade.sh`](https://github.com/rowstile/rowstile/blob/v0.1.0-beta.5/core/tests/upgrade.sh)) install the release before this one (from the beta on) and make databases
with it, then upgrade them: with the next migration, with `rowstile reapply`, with `rowstile apply`, and with `rowstile push`
for a development database. Each then holds what this version makes on a new database, and the shares, API keys
and requests made before still work.

## Backups

`pg_dump` includes everything: the shares, the audit trail, the change feed, the policy history
(`authz.policy_versions`), and the objects the policy generated, as ordinary objects. Restore with
`pg_restore` (or `psql`); nothing needs to be installed first. `pg_dump` keeps no roles, though, and the rules
and grants name the app role: on a new server, make the roles first (`pg_dumpall --roles-only`, or the
migration that makes the app role). Then check:

```sql
SELECT authz.verify();     -- the inheritance tables match a rebuild
```

and, if a newer rowstile is in use now, its next migration (`rowstile migrate`).

If `authz.verify()` says false, rows were written with the triggers off (a restore with `--disable-triggers`, a
bulk load under `session_replication_role = replica`, `ALTER TABLE ... DISABLE TRIGGER`): the inheritance
tables no longer match the app's tables, and people keep or miss access they shouldn't. `rowstile reapply
--force` (or `rowstile apply db/policy.authz --force`) computes every inheritance table again, under its
lock; a plain `apply` keeps the tables it finds unchanged, stale or not.

A data-only restore with `--disable-triggers` ends with `ENABLE TRIGGER ALL`, which leaves the audit trail's
guard on in ordinary sessions only: a session with `session_replication_role = replica` could change the trail.
`authz.lint()` says so, and `rowstile reapply` puts the guard back in every replication role.

## Retention

The audit trail and the change feed grow forever unless trimmed. Schedule (pg_cron, or your app's
jobs):

```sql
SELECT authz.trim_changes();                    -- keeps 7 days, or the setting changes_keep
SELECT authz.trim_audit(interval '2 years');    -- how long is your compliance decision; recorded
```

A change feed reader that falls behind the trimmed part gets an error from `authz.changes_since()` and
must read everything again, then follow from where the feed was when it started (`authz.changes_position()`,
[Governance](/reference/governance.md)). The feed gives an entry once every transaction older than its own has
ended, in any database of the server: a transaction left open (idle in transaction, a long batch) holds the
feed back until it ends. `idle_in_transaction_session_timeout` bounds the first kind.

Shares that end or begin with time (an expiry, a break glass, an approved request for a week) are announced by
a schedule too, if something reads the change feed: run it as often as that reader should hear of them. Until
it runs, the feed says nothing of a share that ended or began.

```sql
SELECT authz.announce_timed_shares();           -- every minute, say: the shares that ended or began since
```

With the sync index (a preview, [The sync index](/reference/sync.md)), the same shares leave or reach the engines'
clients when `authz.sync_tick()` runs: schedule it as often as a share that ends should leave them. After the first
apply with sync lines, which fills the index's tables, vacuum them (`VACUUM authz_sync.file__readers` for each
synced table): until autovacuum has, a write that reads many of their rows is slow ([Speed and limits](/reference/limits.md)).

```sql
SELECT authz.sync_tick();                       -- every minute, say: the shares whose start or end passed since
```

With `sync after commit`, a worker keeps the index: run `authz.sync_work()` in a loop, or schedule it as often as
a change should reach the engines' clients, and watch `authz.sync_behind()`, how long the oldest change it hasn't
worked in has waited ([After commit](/reference/sync.md#after-commit)).

```sql
SELECT authz.sync_work();                       -- every second, say: what committed since its last run
SELECT authz.sync_behind();                     -- NULL: the index holds every change committed
```

## Retrying a write

A write that changes who inherits from whom (a row of a tree made, moved, linked, shared into another, or deleted)
locks what it touches: the rows it changes with everything below them, and the rows those hang under
([Limits](/reference/limits.md)). Two writes to different parts of a tree run side by side. Where two meet, one
waits, and some writes end with an error that means: run the transaction again.

| the write ends with | when | |
|---|---|---|
| a serialization error (`40001`) | in REPEATABLE READ or SERIALIZABLE: another write to what this one touches committed after this transaction's snapshot | run the transaction again |
| a deadlock (`40P01`) | at any level: two transactions that each make several tree writes met crosswise. A transaction of several statements makes several; so does one statement that cascades to rows that are links (`ON DELETE CASCADE` on a link table), a `MERGE`, an `INSERT ... ON CONFLICT DO UPDATE`, a `WITH` that writes. Postgres ends one of the two after `deadlock_timeout` (a second, unless set) | run the transaction again |
| "the policy was applied while this write ran" ([AZ723](/errors/AZ723.md), `40001`) | a policy was applied, or a migration ran, while the write was under way | run the transaction again |

In READ COMMITTED, a statement that makes one tree write (a row made, a move, a link, a share) takes its locks in
one order, so two such statements wait for each other in turn, not in a circle, and neither needs a retry. Any
app that writes in transactions of several statements should retry `40P01` and `40001` anyway: Postgres itself
may end those with either.

A write of more than a thousand rows takes its whole type instead (a setting, `tree_lock_limit`:
[Limits](/reference/limits.md)), as does every tree write to a type that inherits through another type too or
whose inheritance condition reads other rows: the other tree writes to that type wait while it runs. So does every
tree write in SERIALIZABLE, where writes that run side by side make Postgres keep too much of what they read.

## What to watch

| | how |
|---|---|
| the inheritance tables are exact | `SELECT authz.verify()` (reads everything, a batch of rows at a time: about two and a half minutes for a million folders; run it off-peak); false: `rowstile reapply --force` |
| the policy's invariants hold | `SELECT * FROM authz.check_invariants()` |
| a tree write waits | in `pg_stat_activity`, a session whose query writes a tree's table with `wait_event_type = 'Lock'` (`transactionid` or `tuple`: a lock row of what it touches, or its type's row, which another writer holds until it commits); `pg_blocking_pids(pid)` names that writer. A move of more than a thousand rows takes its whole type ([Retrying a write](#retrying-a-write)), and holds it for half a second to a second per 20k folders below (on the benchmark of 2026-10-03, a build from before the rename: `core/bench/results/2026-10-03-*.txt`) |
| slow checks | `pg_stat_statements` on your app's queries; `EXPLAIN` shows the policy's subplans |
| who did what | `authz.audit`; break-glass use is also announced on channel `authz_alerts` |
