LOG: statement: alter user john password 'IloveDBAs';
The username and password have obviously been changed but the point is plain: PostgreSQL printed the password value in plain text to the log file. A few tests of my own confirmed that whenever the PASSWORD specification is used in a CREATE USER or ALTER USER command, it will be printed to the server log:
LOG: statement: create user john password 'badidea';
LOG: statement: alter user john password 'alsobad';
The way around this in psql is to use the \PASSWORD command. When creating a new user, do not specify the password at creation time, just use \PASSWORD later:
# create user susan;
CREATE ROLE
# \password susan
Enter new password:
Enter it again:
# \password john
Enter new password:
Enter it again:
Now, like most password-change processes, you're asked to enter it twice and the input is not echoed to the screen. The log will show this:
LOG: statement: create user susan;
LOG: statement: ALTER USER susan PASSWORD 'md5d311d09782068f6a2391358ae512e113'
LOG: statement: ALTER USER john PASSWORD 'md5a7e4187c1c977ed2e700e880dac11f15'
You can see the passwords are still printed, but they are md5-hashed now.
However, this only works when I'm using psql. If a developer is using a GUI tool that offers a feature to change a password, they could very well be doing the same bad command under the hood.
Note: this behavior was originally observed by me in PostgreSQL 9.2 but it's still the case in 9.6 and 10.2. From a discussion that was had with the pgsql-admin community, it's not likely to be changed any time soon.
Update
Backfilling some helpful comments from the original blog post that were lost in migration.
Jeremy Scheider writes:
I suspect that you don't want the md5 hashes to get logged either. This might be a good read:
https://security.stackexchange.com/questions/41064/is-postgres-password-based-authentication-secure
comment on that link: "The hash in the pg_shadow table is now a password equivalent. When an attackers steals this table (we assume this will happen eventually), she just uses any hash for authentication without even spending time to crack md5."
and Stephen Frost adds:
The real answer to this is to use the new SCRAM authentication method added in PostgreSQL v10, or to use a better authentication method such as Kerberos/GSSAPI or Client-Side Certificates.