MD5 pw encryption in postgresql using CREATE USER WITH PASSWORD

Question

So I have postgresql database and in pg_hba.conf file I am authenticating users via md5.

I typically create users using the command:

CREATE USER username WITH PASSWORD 'password'; 

From the postgresql website if you don't specify ENCRYPTED or UNENCRYPTED then:

the default behavior is determined by the configuration parameter password_encryption

When a password is specified in CREATE USER or ALTER ROLE without writing either ENCRYPTED or UNENCRYPTED, this parameter determines whether the password is to be encrypted. The default is on (encrypt the password).

Since pg_hdba.conf is set to authenticate using md5 I am fairly confident that command I am using is automatically MD5 encrypting the passwords I enter. Howerver, the postgres site also says:

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).

So is there any benefit to using a MD5 hash in the CREATE USER command? Also, how does the command distinguish between a password string and a MD5 hash? Are you supposed to omit the single quotes around the "password" if using an MD5 hash?

Answer 1

So is there any benefit to using a MD5 hash in the CREATE USER command?

The only benefit to supplying a pre-hashed input to CREATE USER ... WITH PASSWORD is that if log_statement = 'all' is on there's no risk of the cleartext going into the logs. However, since knowing the hash is sufficient to authenticate as the user, that doesn't help you any.

In general you should just use CREATE USER ... WITH ENCRYPTED PASSWORD and be done with it.

PostgreSQL's protocol lacks support for strong digest functions and a proper HMAC exchange, so it's wise to use SSL over anything except a trusted local network anyway.