7

How safe is a Postgres user's password?

When a new db user is created, is the stored password hashed and salted?

5

Postgres uses MD5 as algorithm with the username as "salt".

Using a salt normally prevents computation of hashes, for example in rainbow tables. Using the username as salt only partially covers this problem. It is possible to precompute hashes for common usernames, such as "root" or "postgres".

Furthermore, MD5 is one of the fastest cryptographic hashes there is. It is possible to calculate 10¹¹ MD5 hashes per second. This makes it possible to crack the passwords using a brute-force attack, i.e. trying many passwords. Modern password hashing functions are designed to be slow, so that a brute-force attack takes a long time.

In my opinion the Postgres password storage technique does not conform to modern security standards. You can mitigate this by using a long, random password and not reusing that password anywhere else.

2

Pg stores its passwords in pg_authid

Password (possibly encrypted); null if none. If the password is encrypted, this column will begin with the string md5 followed by a 32-character hexadecimal MD5 hash. The MD5 hash will be of the user's password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe. A password that does not follow that format is assumed to be unencrypted.

You can see them by running

SELECT rolpassword
FROM pg_authid;

Note, not all users auth md5. You can have users that auth against most anything using the PG Auth modules, or a PAM module.

  • When a user is created does it automatically encrypt the password, or do you have to tell it to encrypt it? – LINUX G33NYUS Mar 3 '17 at 20:44
  • 1
    The password is hashed ("encrypted") by default, unless you explicitly specify CREATE USER g33nyus WITH UNENCRYPTED PASSWORD 'hunter2', or if the password_encryption setting is off. – Sjoerd Mar 3 '17 at 20:52
0

Starting from PostgreSQL 10, the SCRAM-SHA-256 password authentication mechanism is included:

To upgrade an existing installation from md5 to scram-sha-256, after having ensured that all client libraries in use are new enough to support SCRAM, set password_encryption = 'scram-sha-256' in postgresql.conf, make all users set new passwords, and change the authentication method specifications in pg_hba.conf to scram-sha-256.

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.