Creating user with encrypted password in PostgreSQL

Question

Is it possible to create a user in PostgreSQL without providing the plain text password (ideally, I would like to be able to create a user providing only its password crypted with sha-256) ?

What I would like to do is to create a user with something like that :

CREATE USER "martin" WITH PASSWORD '$6$kH3l2bj8iT$KKrTAKDF4OoE7w.oy(...)BPwcTBN/V42hqE.';

Is there some way to do that ?

Thank you for your help.

Answer 1

You may provide the password already hashed with md5, as said in the doc (CREATE ROLE):

ENCRYPTED UNENCRYPTED These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.

The information that's missing here is that the MD5-encrypted string should be the password concatened with the username, plus md5 at the beginning.

So for example to create u0 with the password foobar, knowing that md5('foobaru0') is ac4bbe016b808c3c0b816981f240dcae:

CREATE USER u0 PASSWORD 'md5ac4bbe016b808c3c0b816981f240dcae';

and then u0 will be able to log in by typing foobar as the password.

I don't think that there's currently a way to use SHA-256 instead of md5 for PostgreSQL passwords.