14 April 2018

How (Not) to Change Passwords in PostgreSQL

A few months ago I was doing some testing on a development PostgreSQL database and watching the postgresql server log (akin to Oracle's alert log). I was skimming for lines relevant to my testing when I noticed something chilling:

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.

No comments:

Post a Comment