# 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`](https://github.com/rowstile/rowstile/blob/v0.1.0-beta.5/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 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`](https://github.com/rowstile/rowstile/blob/v0.1.0-beta.5/core/authzlib/__init__.py); every package carries the same one ([`packaging/version.py`](https://github.com/rowstile/rowstile/blob/v0.1.0-beta.5/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.
