Generating postgresql user password

Question

I tried to generate password for postgres using hashlib from Python.

>>> import hashlib
>>> hashlib.md5("psql123").hexdigest()
2636d1ddc54901f98d011ffe050c0eb7

But postgresql requires md5 prefix, so then

sudo -u postgres psql
ALTER USER postgres PASSWORD 'md52636d1ddc54901f98d011ffe050c0eb7';

However, authentication would fail if I use psql123 as password.

If I use passlib, I am fine. See http://pythonhosted.org/passlib/lib/passlib.hash.postgres_md5.html

Doing the following using psql123 as password is okay.

ALTER USER postgres PASSWORD 'md556074e7318bd4cee558faab0678a2fad';

I don't understand what the warning in passlib want to say. Is it okay to use this hash for postgres user? Also, where in the doc does it say username has to be part of the input?

I assume this is why postgres can't understand the result from hashlib. As a LDAP user, I can generate a password in the shell. Does postgres has a built-in command to do that? Does psycopg2 has that? It looks like it doesn't.

Answer 1

Postgres' password hash is very close to what you did, it just needs the username to be included as follows:

 pghash = "md5" + hashlib.md5(password + username).hexdigest()

AFAIK, the postgres docs don't really document this hash format at all, and seem to assume admins will rarely deal with these hashes directly :( There are no builtin methods for generating these hashes that I know of. If the password provided to the ALTER USER command doesn't conform to the postgres hash format, it assumes the password hasn't been hashed, and takes care of that internally - per the docs for CREATE ROLE's ENCRYPTED keyword. (IMHO this is a flawed behavior, because if a hash depends on the username, it means hashes can't be copied and pasted between different accounts, break when the account is renamed, and (guessing entropy wise) only has ~6 bits of effective salt).

The warning at the top of passlib's documentation for the hash could probably be clearer. It was meant to warn people browsing through the passlib documentation that 1) this hash was horribly insecure, 2) that they shouldn't adopt it for use in their own applications, and 3) that it was only fit for the purpose of working with postgres user accounts, since it's the strongest (and only) hash format postgres supports for it's own accounts.

(If you're trying to use postgres to hash passwords for your own application's user accounts, I'd strongly second Clodoaldo's recommendation to use bcrypt by way of the pgcrypto extension).

Answer 2

alter user postgres ENCRYPTED password 'psql123';

For other uses use the pgcrypto module.

create table "user" (name text, password_hash text);

insert into "user" (name, password_hash) values
('u1', crypt('psql123', gen_salt('bf')));

select * from "user";
 name |                        password_hash                         
------+--------------------------------------------------------------
 u1   | $2a$06$SeH4u4aRtT2Zr39er4eSiONT/0IBQHYMbQXn2RauPJKCYdNX1.58G

select name, password_hash = crypt('psql123', password_hash)
from "user"
;
 name | ?column? 
------+----------
 u1   | t

Install it as super user logged in the target database:

create extension pgcrypto;