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| tool | what rowstile migrate writes |
|---|---|
alembic | a revision after the current head, <rev>_authz_<name>.py, with its SQL beside it (.sql) |
prisma | prisma/migrations/<time>_authz_<name>/migration.sql |
django | the 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 |
drizzle | drizzle/<n>_authz_<name>.sql with statement breakpoints, its journal entry and snapshot |
efcore | App/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) |
rails | db/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) |
ecto | priv/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) |
laravel | database/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 |
sqlx | the 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, dbmate | the same SQL, with each tool's markers, named as for sql |
flyway | V<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 fewCREATE OR REPLACEof 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 withpushorapplystops 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 -falone), 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. WithON_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).migratealso marks the lock and its migrationslinguist-generatedin.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-phaseasks 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 --checksays so, androwstile migratewrites 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, androwstile 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(androwstile dev, on every save) runs the migration from the policy in force to this one, directly;rowstile applyapplies 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 withrowstile push --development, once. A database with a policy and no mark is refused (AZ610), and stays refused afterrowstile 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_dumpkeeps it all: shares, audit trail, change feed, policy history, and what the policy made. - Removing:
rowstile remove --yesdrops 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.