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?

  • 2
    MD5 is neither encryption nor is it safe to use. – ceejayoz Jun 4 '15 at 14:40
  • I can understand and agree with recommending against MD5, but I'm 99% sure that it is a one-way cryptographic hash function. – Vasili Syrakis Jun 4 '15 at 14:47
  • @ceejayoz It's a cryptographic hash. It's pedantic but strictly correct to note that it's not "encryption", but will just confuse most people. It's an increasingly weak hash, but at this point it's the only option you've got with PostgreSQL, as protocol changes (in progress) are needed to support stronger hashes. – Craig Ringer Jun 4 '15 at 23:38
  • Backups (pg_dumpall) have users in the form of SQL statements to recreate them with their passwords. Not disclosing cleartext passwords in these statements is a benefit. – Daniel Vérité Jun 16 '15 at 11:06

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.

  • Thanks for the answer. Connections from a web application (say django) are local, right? Even though a user maybe accessing the site over the internet, they are not access the database directly over the internet. They are accessing the django app which is displaying the database records. – VT_Drew Jun 8 '15 at 15:26
  • @VT_Drew Presuming the web app is on the same host as the db or on the same trusted network then yes – Craig Ringer Jun 8 '15 at 23:03
  • Actually scram can be used as a strong alternative for logins, this however requires that the server has the password stored reversible. – eckes Sep 17 '20 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.