Apply database migrations

This page describes how to bring the database of an already deployed gateway up to the current schema, and the migration that cannot be undone.

Two things to read before any migration

The list of pending migrations

The list of migrations that will run is not written down in any document. The script computes it from the files present in migrations/ and from what the database has already received. A hand-maintained copy of that list goes stale; an administrator who follows it may think they are applying a constraint rename when the chain contains an irreversible migration.

export DATABASE_URL="postgresql://<utilisateur>:<mot-de-passe>@<hôte>:<port>/<base>"
cd gateway-db
./migrate.sh --status

Each [ ] line is a migration that will run on the next call without options. Read them all before you launch anything.

A migration is applied on top of a backup you know how to restore

Taking a backup is not enough. What protects you is having already restored a backup at least once, cold, on this installation. Until you have done the round trip, you have files that nobody knows will come back.

The procedure (the two scripts, the inventory to compare before and after, the restore test on a side database) is in Back up and restore the gateway database. Do it before coming back here.

Two points it covers that bear on what follows:

• the backup does not carry the secrets that live outside the database: not your providers’ keys, of which the database only knows the environment variable name, and not ADMIN_API_KEY. A restored database does not make an installation that starts again;
• the file is a secret: before the 015 migration, it contains every one of your holders’ keys in clear text.

The snapshots managed by your host remain useful, but they do not replace this test: neither their frequency, nor their retention, nor their exact contents can be verified from here.

The migrations that exist today

They are listed, in the order they apply, by the migration reference. That page is generated from the files themselves: it cannot omit a migration that exists, nor describe one that does not. This guide keeps no copy of it.

The script sorts file names and requires no contiguity: a gap in the numbering is not a lost migration.

015 is one-way

It is the only migration in the chain that cannot be undone.

Before it, users.api_key contains each client’s key in clear text. The migration computes the SHA-256 digest of each key, derives the displayable fragment from it (sk-lemniscate-…a3f9), then drops the column.

After the DROP COLUMN, no query, no screen, no database access retrieves a key. The conversion is seamless for holders: nobody has to change their key. What you lose is the ability to read a key back, and therefore to fix a failed handover other than by rotating the key.

An earlier backup then becomes the only place where those keys exist in clear text. Treat it as a secret: encrypt it, move it off the machine, destroy it once the migration is confirmed. Keeping it “just in case” reopens the leak the migration closes.

The whole thing fits in a single transaction, along with the registry entry: the migration goes through entirely or not at all.

Applying

Order matters. Schema and code are not forward compatible: the old code reads api_key, which no longer exists, and the new code does not work on the old schema. There is therefore a window during which authentication is down, for the duration of the redeployment. Plan for it.

1. Back up, and treat the file as a secret.

   ./sauvegarder.sh --sortie /var/sauvegardes/avant-migration-$(date +%Y%m%d-%H%M%S).sql.gz

   Keep the inventory the script prints: that is what you will compare against if you have to roll back. The details are in Back up and restore the gateway database.

2. Note what is going to run.

   ./migrate.sh --status

3. Count the accounts beforehand, so you have a number to find again after.

   psql "$DATABASE_URL" -c 'SELECT count(*) FROM users'

4. Apply.

   ./migrate.sh

   Each migration runs in a single transaction along with its entry in the schema_migrations registry: a migration that fails leaves neither a half-converted schema nor a wrong registry.

5. Verify, if 015 or 021 was part of the batch applied. The three numbers must be equal to each other and equal to the one from step 3.

   psql "$DATABASE_URL" -c "SELECT count(*) AS cles, count(*) FILTER (WHERE key_hash ~ '^[0-9a-f]{64}$') AS hachees, count(*) FILTER (WHERE key_hint <> '') AS avec_fragment FROM access_keys"

   No key column must remain on the accounts; zero rows is expected:

   psql "$DATABASE_URL" -c "SELECT column_name FROM information_schema.columns WHERE table_name = 'users' AND column_name LIKE '%key%'"

6. Put the service roles into service, if 023 was part of the batch applied.

   This migration creates two PostgreSQL roles, lemniscate_gateway and lemniscate_console, each with only the rights its service needs. It creates them without a password and without the right to connect (NOLOGIN): a secret written into the product would be a secret published to everyone who installs it. Open them with your own secrets:

   psql "$DATABASE_URL" -c "ALTER ROLE lemniscate_gateway WITH LOGIN PASSWORD 'votre-secret'"
   psql "$DATABASE_URL" -c "ALTER ROLE lemniscate_console WITH LOGIN PASSWORD 'un-autre'"

   Then, in each service’s configuration, replace the owner account identifier with that of its role. The gateway takes lemniscate_gateway, the console lemniscate_console.

   Both services refuse to start with the owner account.

   Before opening its port, each service asks the database whether the account it was given can rewrite billed consumption rows or the audit log. If it can, it stops and prints the three steps to follow: apply the migrations with the owner account, open the role with ALTER ROLE, point the service’s DATABASE_URL at that role. A database that cannot answer, because the migration has not been applied, gives the same refusal.

7. Redeploy the gateway and the console, in either order relative to each other, but after the migration.

   Migration first, deployment second.

   A migration that drops a column cuts off service for everyone if the deployed code still reads it. That is the case with 021, which moves an account’s key out of the users table into the access_keys table: a gateway on an earlier version, facing an already migrated database, rejects every request, because the column it authenticates on no longer exists.

   The safe order is the one on this page: apply the migration, then deploy. In between, a migration that drops no column leaves the gateway in place serving requests; 015 and 021 open the outage window announced at the top of this section.

   If the deployment fails after a migration has already been applied, do not go back to the old version of the code: apply the corresponding rollback, which lives in gateway-db/migrations/retours/. migrate.sh does not see that folder; a rollback is applied by hand, with psql, in a single transaction. The one for 021 brings the unique key back onto users, drops access_keys, removes version 021 from the registry itself, and states at the top what it cannot preserve.

8. Check. Authenticate a known key against the gateway, and open the account list in the console: every row must carry its fragment.

The case of a database with no registry

migrate.sh keeps a registry (schema_migrations) that it creates on the first call. A database whose schema was built by something other than the script does not have that registry. Finding nothing recorded, the script would want to replay everything from 000, which would destroy live data.

Stamping is for that case, once only:

./migrate.sh --baseline 006   # inscrit 000 à 006 sans les exécuter
./migrate.sh                  # applique 007 et les suivantes

After that, ./migrate.sh without options applies the following migrations.

The value 006 is not generic. It is the value for a database whose history is known: 001 through 006 were applied by hand, with no registry. --baseline records without running: a stamp placed too far ahead marks as applied migrations that were not, and those migrations never run. On 015, that leaves keys in clear text in a database the rest of the system believes converted.

Only place a stamp on a database where you know, by other means, how far the schema was built.

The second case is a database created by docker-compose, which received init.sql. That file describes the complete target state of the schema, and an integration test compares the two constructions column by column. The chain does not apply to it as is: 000 recreates tables that already exist there, and fails. The stamp to place is that of the last migration in the repository; the migration reference gives it, and the tutorial Get started with the gateway prescribes it too.

Adding a migration

If you extend the schema yourself, three steps, in this order:

1. create migrations/0NN-description-courte.sql; the three-digit prefix fixes the execution order;
2. mirror the same effect in init.sql, which describes the complete target state;
3. run the integration suite: the schema test fails if the two descriptions have diverged.

The 010 migration is not to be edited: its header forbids it, because it runs on a schema that still has users.api_key. A key published after 015 is purged in a new migration, on the digest.