I tried to generate password for
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.
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).
alter user postgres ENCRYPTED password 'psql123';
For other uses use the
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;