Background

"Dance like no one is watching. Encrypt like everyone is."

-- Adrian Lamo

The simplest way to create a user to access a PostgreSQL database is something like this:

CREATE USER app PASSWORD 't0pSecret!'

However, the text of this SQL will be sent to the remote database server for execution. That means the plaintext of the password is itself sent to the database.

If statement logging is enabled the following will appear in server logs:

2024-01-14 19:09:09.064 UTC [75] LOG:  statement: CREATE USER app PASSWORD 't0pSecret!';

If there's a pool or proxy in between you and the database server, it could end up in those logs as well.

We can do better. Much better!

Password Encryption (i.e. "hashing")

The PostgreSQL docs refer this topic as "password encryption". In reality, the operation that is being performed is form of hashing and HMAC.

The server does not need to know the exact text of your database user's password. It just needs to know if your supplied credentials match the ones that are authorized to access that user.

Password hashing is one way to perform such a calculation and allows the server to only store the result of that one-way hash function. The server can then check if the result of applying the password hashing operation for the credentials supplied in your connection attempt matches the expected password hash.

PostgreSQL Password Encryption - MD5

Up until PostgreSQL v10, there was only one encryption option built-in to PostgreSQL for dealing with passwords: md5

With md5 password encryption, the server stores MD5(password || username) in it's internal pg_shadow catalog (think /etc/shadow but for database passwords).

The hashing operations described in this post use the following expressions:

MD5( ...) = result of md5 hash function of its one and only argument

|| = binary operation to concatenate the raw bytes of its arguments

So 'foo' || 'bar' = 'foobar' and MD5('test') = '098f6bcd4621d373cade4e832627b4f6'

During the authentication step of the PostgreSQL wire protocol, the server picks a random salt and asks the authenicating client to calculate: MD5( MD5(password || username) || salt)).

The random salt prevents the value from a previous connection from being re-used for a later connection attempt (also known as a replay attack). Unfortunately, the size of the salt in the wire protocol is only four-bytes so it's not particularly effective. With enough connection attempts, assuming there is no transport level encryption (TLS), then an eaves dropper could collect every possible authentication response.

PostgreSQL Password Encryption - SCRAM-SHA-256

Starting in PostgreSQL v10, a new option was added and made the default for handling passwwords: SCRAM-SHA-256

SCRAM is an acryonym for Salted Challenge Response Authentication Mechanism.

It can be used with various hashing algorithms. The PostgreSQL implemention supports only SHA-256.

SCRAM has a number of advantages over md5 encryption:

• A modern hash function (SHA-256)
• Larger salt sizes (defaulting to 16-bytes)
• key stretching (defaulting to 4096 rounds)
• Proving to the client that the server knows the user's credentials (the client authenticates the server too!)

With the addition of SCRAM-SHA-256, there is no reason to ever use md5 authentication on a modern PostgreSQL server.

CREATE USER / ALTER USER

The PostgreSQL SQL commands for creating a user, CREATE USER ..., accepts a ... PASSWORD 'literal-value-goes-here' clause for specifying the password. The ALTER USER ... command operates similarly.

The server checks that new password value and decides if it is already encrypted. If not, the server encrypts it using the connection's default encryption method (SHOW password_encryption to see the default).

If the value of the new password starts with md5 then the server assumes it is an already encrypted password using the md5 scheme.

If the value of the new password starts with SCRAM-SHA-256$ then the server assumes it is an already encrypted password using the SCRAM-SHA-256 scheme.

So the following would instruct the PostgreSQL server to encrypt the password using the connections default password encryption:

CREATE USER alice PASSWORD 'abcd'

Whereas this would be treated as an already encrypted md5 password:

CREATE USER alice PASSWORD 'md5fb592cb4152e2aacaaf452714d283f7e'

And this would be treated as an already encrypted SCRAM-SHA-256 password:

CREATE USER alice PASSWORD 'SCRAM-SHA-256$4096:jHhJMplyRcr1io3v3YwabQ==$WNwkp8PFet/L8UUtFBEa7Sn8xSofpPZ4klcfuB0w6Yk=:Oqnfxd3FhyYTALxW+YeU/yVAzxKT+ho08E+hE/'

In all three cases, the user will be able to log in with the same password, abcd, however in the first case the user's password may have been compromised by being logged during the command execution.

The same update could be done via an ALTER USER ... command:

-- Have the server encrypt the password ... BAD
ALTER USER alice PASSWORD 'abcd';

-- Provide an md5 encrypted password ... BETTER (but not great)
CREATE USER alice PASSWORD 'md5fb592cb4152e2aacaaf452714d283f7e'

-- Provide a SCRAM-SHA-256 encrypted password ... BEST (really this is only one you should be using)
CREATE USER alice PASSWORD 'SCRAM-SHA-256$4096:jHhJMplyRcr1io3v3YwabQ==$WNwkp8PFet/L8UUtFBEa7Sn8xSofpPZ4klcfuB0w6Yk=:Oqnfxd3FhyYTALxW+YeU/yVAzxKT+ho08E+hE/'

There's no reason to present the plaintext password to the server. The server does not even save the plaintext of the password. It immediately encrypts it (using the current password_encryption option) and saves the encrypted value:

postgres=# ALTER USER alice PASSWORD 'abcd';
ALTER ROLE

postgres=# SELECT passwd FROM pg_shadow WHERE usename = 'alice';
                                                                passwd                                                                 
---------------------------------------------------------------------------------------------------------------------------------------
 SCRAM-SHA-256$4096:awluLTEXFg1qnYqxpmRNkQ==$FPHOxeG4sUCa+vy6W83VMHaXr3rSRq2QVfkvmAMad2A=:g9oXk/UT6fF+N8z5aFIQpRvLfm8Yz13ZEjdhrWhY2/0=
(1 row)

At best the password is not leaked to some log file. But you'll never really know, right?

Libraries

As creating users and updating their passwords is a common enough task, it's been added to a number of PostgreSQL libraries. The helpers in those libraries handle encrypting the password so that it can be used in SQL commands without exposing its plaintext.

C - libpq

https://www.postgresql.org/docs/current/libpq-misc.html#LIBPQ-PQENCRYPTPASSWORDCONN

The libpq C library is part of core PostgreSQL and is the basis for a number of user tools and other drivers. It provides a function, PQencryptPasswordConn(...) that will encrypt a password using the defaults for the provided connection.

Java - PostgreSQL JDBC Driver (PGJDBC)

https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/util/PasswordUtil.java

A helper class, PasswordUtil was recently added to the PostgreSQL Java database driver, PGJDBC. The methods in that class allow for fine grained control of the encryption type and internal parameters (e.g. number of SCRAM iterations or salt size). There are also methods for generating the ALTER USER ... SQL command for external execution or inclusion in a script.

A new helper method was also added PGConnection that performs a password change operation for a user using the default encryption setting of the database server. This is simplest method for updating a user's password without leaking it along the way.

Only ALTER USER ... PASSWORD ... is supported as creating a user has many additional options and could change over time.

To use it:

String username = "alice";
String newPassword = "my-new-secret-password";
PGConnection pgConn = conn.unwrap(PGConnection.class);
pgConn.alterUserPassword(username, newPassword.toCharArray(), null);

When creating users, the recommended approach is to issue the CREATE USER ... operation first without a password and then follow up to invoke the new PGConnection.alterUserPassword(...) method.

These new helpers will be available in the next release of the PGJDBC driver.

node.js - pg-password-util

https://www.npmjs.com/package/pg-password-util

On node.js, you can use pg-password-util. This is an NPM module (written by me) that encrypts the password client side and has helpers for updating an existing user's password.

It's designed to work with pg the most popular PostgreSQL driver for node.js. To update a user's password:

import { alterUserPassword } = require('pg-password-util');

// client is a pg.Client
await alterUserPassword(client, {
    username: 'app',
    password: 'my-new-secret-password',
});

Using this helper the plaintext password is never sent over the wire and will not appear in any server logs.

python - psycopg's encrypt_password

https://www.psycopg.org/psycopg3/docs/api/pq.html#psycopg.pq.PGconn.encrypt_password

Python's psycopg driver is a wrapper for libpq (the PostgreSQL C library). It includes a python function wrapping the PQencryptPasswordConn(...) C function mentioned above:

>>> enc = conn.info.encoding
>>> encrypted = conn.pgconn.encrypt_password(password.encode(enc), rolename.encode(enc))
b'SCRAM-SHA-256$4096:...

The return value is a string that can be included in an ALTER USER ... PASSWORD ... statement.

Thanks to Robert Ladyman for suggesting this Python library.

Password Generation

In case it was not obvious, the abcd password above is a terrible password. Real passwords, particularly for service accounts like application database users, should be long and cryptographically random.

On most *nix environments you can generate one using:

tr -d -c a-zA-Z0-9 </dev/urandom | head -c 43

That will generate a random 43-character password. Each character has 62 choices (a-z, A-Z, or 0-9). That's about 5.954 bits of entropy per character (ln(62) / ln2). With 43-characters, that's a little more than 256-bits entropy in total.

Final Thoughts

There are plenty of alternatives to passwords. (I know at least one person who, upon reading this, is already mentally drafting an email to me that everybody should be using Kerberos...) But if you are going to use them, they should not be exposed.

Using the helpers listed above, you could:

1. Generate a new long random password.
2. Save the password to a secure secrets store.
3. Update the database user with the new credentials.

All without persisting the value in plaintext anywhere along the way.

Do you have another library that should be included in this post or want to expand on this further? Let me know!