Why should a migration never set a role password in plaintext?

Role password set in plaintext by the migration

Critical: a working credential committed to the repository; rotate it, git history keeps it.

What happens

CREATE ROLE … PASSWORD 'x' puts the database password into a file that is committed, reviewed in pull requests, printed by CI and kept in git history forever, and the server writes the statement to its own log when log_statement covers DDL. Rotating it later does not un-leak it. Postgres accepts an already-hashed SCRAM-SHA-256 verifier in the same position, and the rule stays silent on one; an md5 verifier is reported as a warning because it can be cracked offline.

Why it is dangerous on a populated table

Table size does not change this one: a committed credential is live from the first row, and git history keeps it after the fix.

Fires on

CREATE ROLE app_user LOGIN PASSWORD 'hunter2';

The safe pattern

CREATE ROLE app_user LOGIN;
-- deploy job, from the secret store, never committed:
--   ALTER ROLE app_user PASSWORD 'SCRAM-SHA-256$4096:…$…:…';

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

ALTER ROLE app_user WITH PASSWORD 'correct horse battery staple' VALID UNTIL '2027-01-01';

CREATE ROLE app_user LOGIN PASSWORD 'hunter2';

CREATE USER reporter WITH ENCRYPTED PASSWORD 'md5a3f2c0d9e8b7a6f5e4d3c2b1a0f9e8d7';

CREATE ROLE app_user LOGIN;
GRANT reporting TO app_user;

ALTER ROLE app_user PASSWORD NULL;

CREATE ROLE app_user LOGIN PASSWORD '${APP_USER_PASSWORD}';

CREATE ROLE app_user LOGIN PASSWORD 'SCRAM-SHA-256$4096:c2FsdHNhbHRzYWx0c2FsdA==$U3RvcmVkS2V5U3RvcmVkS2V5U3RvcmVkS2V5U3RvcmVkS2V5U3RvcmU=:U2VydmVyS2V5U2VydmVyS2V5U2VydmVyS2V5U2VydmVyS2V5U2VydmU=';

How to check locally

npx bolvrk check migration.sql