Repository navigation
The owner password reaches PostgreSQL in plaintext inside ALTER ROLE #140
Description
Found in the review of #139 (S-006, owned PostgreSQL databases).
The problem
engine/ansible/roles/cloudfall_postgresql/tasks/owner_password.yml sets the owner role's password with psql's ALTER ROLE ... PASSWORD :'var'. psql substitutes the value into the statement text before it sends it, so the PostgreSQL server receives the plaintext password inside the SQL. With password_encryption = scram-sha-256 the stored value is a SCRAM verifier, but while the statement runs the text is visible in pg_stat_activity, and it is written to the PostgreSQL log when log_statement is ddl or all, or when the statement fails (log_min_error_statement).
The Ansible side is clean: the value is in no argv, inventory, Ansible output, answer or receipt, and the host file is removed in an always block. The leak is only on the PostgreSQL server's side.
Reproduce
On a host converged with an owned database, set log_statement = 'ddl', run cloudfall converge services again, and read the PostgreSQL log: the ALTER ROLE billing PASSWORD '...' line holds the password.
Not reproduced live yet (no PostgreSQL in the review environment); this is documented PostgreSQL behaviour.
Proposed fix
Compute the SCRAM-SHA-256 verifier (SCRAM-SHA-256$4096:<salt>$<StoredKey>:<ServerKey>) on the controller, stage the verifier instead of the password, and send ALTER ROLE ... PASSWORD '<verifier>'. PostgreSQL stores a pre-hashed verifier as is, so neither the log nor pg_stat_activity ever holds the password. Keep the answer's reference, key and SHA-256 as they are. A regression test can check the staged file holds a verifier that verifies the password, never the password.
Touches engine/ansible/roles/cloudfall_postgresql/tasks/owner_password.yml and the controller staging in sdk/src/cloudfall/secrets.py / engine/src/cloudfall_engine/cli.py.
Related: #81, #122, spec docs/specs/006-standalone-postgresql.md (S-006-6).
Activity
shipmill-romamo commentedon Oct 27, 2026
Triage: implement, for the next release candidate. Waits on your decision below.
Confirmed on main (
389f0af):engine/ansible/roles/cloudfall_postgresql/tasks/owner_password.ymlsendsALTER ROLE ... PASSWORD :'var', and psql puts the value into the statement text, so the server sees the plaintext password. Today it never reaches Ansible output, the inventory, an answer or a receipt (S-006-6 holds), but PostgreSQL itself can log it (log_statement = 'ddl'/'all', or a failing statement) and shows it inpg_stat_activitywhile it runs.The plan: compute the SCRAM-SHA-256 verifier on the controller (random 16-byte salt, 4096 iterations, the format PostgreSQL stores), stage the verifier instead of the password in the same 0600 file, and send
ALTER ROLE ... PASSWORD '<verifier>'. PostgreSQL stores a pre-hashed verifier as it is, so the password never reaches the server. Answers keep naming the reference, key and the value's SHA-256. A test checks that the staged file holds a verifier that verifies the password and never the password. One side effect: the stored verifier gets a new salt on every converge, soALTER ROLEalways reports a change. Re-applying the same password already did that.shipmill-romamo commentedon Oct 27, 2026
@romamo Decision needed: should owner role passwords reach PostgreSQL only as a SCRAM verifier computed on the controller, so the server never sees the plaintext?
pg_stat_activity. It costs a small SCRAM helper on the controller (standard libraryhashlib.pbkdf2_hmacandhmac, no new dependency).ALTER ROLE ... PASSWORDwith the plaintext and document turning offlog_statementfor DDL: no code change, but any host with statement logging on keeps the password in its logs.Reply here with a number or your own answer; shipmill takes this up on the tick after your reply.
romamo commentedon Oct 30, 2026
Decision by @romamo, relayed by Claude Code
shipmill-romamo commentedon Oct 30, 2026
Triage: implement, on
@romamo's answer (option 1). Security fix, no goal needed.The plan stands as in the triage comment above: compute the SCRAM-SHA-256 verifier on the controller (random 16-byte salt, 4096 iterations, PostgreSQL's stored format), stage the verifier instead of the password in the same 0600 file, and send
ALTER ROLE ... PASSWORD '<verifier>'. Answers keep naming the reference, key and the value's SHA-256. A regression test checks the staged file holds a verifier that verifies the password and never the password. The rule is recorded as D-9 indocs/decisions.md. An implementer is on it; the pull request will be linked here.shipmill-romamo commentedon Oct 30, 2026
Pull request: #156. The controller stages the password's SCRAM-SHA-256 verifier (
cloudfall.secrets.scram_sha256_verifier) in the same 0600 file, and every path that runsservices.ymlgoes through it. The decision record is #155 (D-9).One addition to the plan: the password is prepared with SASLprep the way PostgreSQL's
pg_saslprepdoes, so a non-ASCII password still logs in; ASCII passwords are hashed as given. The verifier is checked against RFC 7677's published exchange, not yet against a live PostgreSQL server.Record D-9: owner passwords reach PostgreSQL only as a SCRAM verifier (…