Skip to content

Deploying a policy: migrations ​

The compiler runs in the rowstile command, never in the database. A policy change is a migration, written for the tool in rowstile.toml, reviewed and deployed with the app's other migrations. The database needs nothing installed first: plpgsql, which every Postgres has, is enough. core/Dockerfile builds a Postgres for development and tests: the stock image, with the command in it.

rowstile migrate                          # the next migration, and db/policy.lock (no database needed)
rowstile migrate --check                  # in CI: exit 1 if the policy has a change no migration has
rowstile push                             # a development database, with the same migration
rowstile diff                             # who gains and loses what; changes nothing, locks the app's tables as it runs
rowstile test                             # the tests and invariants
toolwhat rowstile migrate writes
alembica revision after the current head, <rev>_authz_<name>.py, with its SQL beside it (.sql)
prismaprisma/migrations/<time>_authz_<name>/migration.sql
djangothe app's next migration after its latest, <n>_authz_<name>.py (RunSQL, numbered as makemigrations numbers the next), with its SQL beside it (.sql); dir is the app's migrations folder, and undoing it is refused
drizzledrizzle/<n>_authz_<name>.sql with statement breakpoints, its journal entry and snapshot
efcoreApp/Migrations/<time>_Authz<Name>.cs, a migration class whose Up runs the SQL in one statement (a DO block: Npgsql splits a command at each semicolon, and BEGIN ATOMIC bodies have some), and its .Designer.cs, which names the folder's DbContext and builds the model its snapshot holds (dotnet ef migrations has-pending-model-changes then shows no change)
railsdb/migrate/<time>_authz_<name>.rb, a migration class whose up executes its SQL, beside it (.sql), in the transaction Rails runs it in; a name the folder has already gets the next number (authz_policy_2)
ectopriv/repo/migrations/<time>_authz_<name>.exs, a migration module whose up executes its SQL, beside it (.sql), in the transaction Ecto runs it in, by Postgres's simple protocol (query_type: :text: Ecto's usual one takes one statement at a time); its module named as the folder's latest migration names its own (MyApp.Repo.Migrations.AuthzPolicy); a name the folder has already gets the next number (authz_policy_2)
laraveldatabase/migrations/<Y_m_d_His>_authz_<name>.php, an anonymous migration class (as Laravel 9 and newer write them) whose up runs its SQL, beside it (.sql), in the transaction Laravel runs it in, named after the folder's last migration; undoing it (migrate:rollback) is refused
sql<n>_authz_<name>.sql when the folder's last file is numbered (0005_... after 0004_...), else <time>_authz_<name>.sql
sqlxthe same file as sql: a simple migration, forward only, which sqlx's migrator (sqlx migrate run, sqlx::migrate!) runs in a transaction; a folder of reversible ones (.up.sql and .down.sql) numbers it after them too
goose, dbmatethe same SQL, with each tool's markers, named as for sql
flywayV<n>__authz_<name>.sql when the folder's last version is a number (V3__ after V2__), else V<time>__authz_<name>.sql; and its script config file beside it (.sql.conf, placeholderReplacement=false: the SQL holds ${, which Flyway would take for a placeholder)

A number, not the time, after numbered files: the app's next migration, numbered after rowstile's, then comes after it too. goose, dbmate (--strict) and Flyway refuse a migration whose version is below one applied already.

  • The lock file (db/policy.lock, next to the policy; commit it) lists the policy's lines, then each function, view, trigger, policy and table the migrations made so far, with a hash of its SQL. So the next migration holds only what changed: a new permission is a new view and a few CREATE OR REPLACE of the functions that list permissions; unchanged inheritance tables are never touched. A view that changes is replaced in place (emptied first, then defined again), so what the app built on a masked view stays. The same policy always gives the same bytes. Lines that only moved (a comment added above a rule) make a small migration: it writes where each line is now, which the messages name, and makes nothing.
  • Each migration checks where it starts: the lock's hash is recorded in authz.policy_versions, and a migration applied out of order, twice, or to a database changed since with push or apply stops with a message naming both.
  • Each migration is one transaction, your tool's. Plain SQL files run with psql -1 -v ON_ERROR_STOP=1 -f <file>. Run a statement at a time (psql -f alone), a migration refuses before it changes anything (AZ615). It makes its session read-only first, and its last statement puts the setting back. So run migrations on a direct connection to Postgres, or through a pooler in session mode, not through one in transaction mode: there the last statement may run on another server connection, and the first one stays read-only for the next client. With ON_ERROR_STOP, psql stops before that statement; on a direct connection the session then ends, and the setting with it.
  • Each migration starts with what changed, as comments (-- + type file: can comment = folder.view). migrate also marks the lock and its migrations linguist-generated in .gitattributes, so reviews show them collapsed.
  • Inheritance that changes is built beside, then swapped in: two migrations. The first fills the new inheritance table while the app runs (it only reads the app's tables); the second, a short transaction, swaps it in and does again what the app changed in between (rows written since the first one's snapshot, and the links the change feed records). A tree of several types, one whose conditions read other tables, one that takes links from shares, or one whose link tables the policy in force doesn't watch yet is rebuilt in one migration, under lock. --one-phase asks for one migration.
  • The first migration is the whole policy, which also applies over a database that already has one (applied with rowstile apply).
  • Upgrading rowstile is the next migration when the new version makes something differently: rowstile migrate --check says so, and rowstile migrate writes it. When it changes only an inheritance tree's functions (a message, say), the migration replaces them in place and the tree keeps its rows. A new version that makes the same needs no migration. Until the database is upgraded, a command that calls a function the policy in force doesn't have says which version applied that policy, and rowstile reapply (AZ626). The other way round is refused: a command older than the version that last wrote the lock file or the database stops (AZ616), unless told --downgrade.
  • Development: rowstile push (and rowstile dev, on every save) runs the migration from the policy in force to this one, directly; rowstile apply applies the whole policy again. Neither is for production, which takes migrations. Push only changes a development database: the first push to a database that never had a policy marks it as one; mark one your migrations set up with rowstile push --development, once. A database with a policy and no mark is refused (AZ610), and stays refused after rowstile remove: taking the policy out doesn't make a database a development one.

The version (semantic versioning; 1.0 will freeze the public surface) is the compiler's, in core/authzlib/__init__.py; every package carries the same one (packaging/version.py sets them).

  • Who may apply: whoever may change the tables' policies and triggers, which is their owner (or a superuser). The {...} conditions are SQL that runs with the rights of whoever applies the policy, so whoever writes a policy has the rights of whoever applies it.
  • Every applied policy is kept in authz.policy_versions, with the rowstile version that applied it and the lock's hash (not readable by the app role).
  • Backups: everything is an ordinary table, view, function or policy, so pg_dump keeps it all: shares, audit trail, change feed, policy history, and what the policy made.
  • Removing: rowstile remove --yes drops everything the policy made and gives back the SELECT that masks took (table-wide, or a column's own); shares and history stay, and row-level security stays on (with a warning), so nothing becomes readable by accident. The privileges applying gave are taken back: every privilege is as it was before the first apply.
  • Nothing needs a superuser: later grants on masked columns are reported by authz.lint(), not refused.