8

How safe is a Postgres user's password?

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

LINUX G33NYUS
  • 245
  • 3
  • 6
  • 1
    See also: [The security of the PASSWORD function in MySQL](https://security.stackexchange.com/questions/151998/the-security-of-the-password-function-in-mysql). – Sjoerd Mar 03 '17 at 20:45

2 Answers2

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.

Sjoerd
  • 28,897
  • 12
  • 76
  • 102
  • 1
    *"Postgres uses MD5 as algorithm with the username as "salt"."* This answer is outdated as of [PostgreSQL 10](https://www.postgresql.org/docs/10/auth-methods.html#AUTH-PASSWORD) also supports `scram-sha-256` now. – Raymond Nijland Aug 07 '19 at 14:13
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.

Evan Carroll
  • 2,547
  • 4
  • 23
  • 35
  • When a user is created does it automatically encrypt the password, or do you have to tell it to encrypt it? – LINUX G33NYUS Mar 03 '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`](https://www.postgresql.org/docs/9.5/static/runtime-config-connection.html#GUC-PASSWORD-ENCRYPTION) setting is off. – Sjoerd Mar 03 '17 at 20:52