SCRAM Authentication in PostgreSQL and SCRAM Pass-Through Authentication

Introduction

PostgreSQL supports multiple client authentication methods. In addition to external authentication providers, it also supports password-based authentication using credentials stored in the database.

Among the password-based methods, the recommended one is scram-sha-256, which is a form of SCRAM authentication as discussed in the previous article.

This article introduces how SCRAM authentication works in PostgreSQL, and explains a new feature planned for PostgreSQL 18: SCRAM pass-through authentication.

2025-05-31 Added usage of SCRAM pass-through authentication

(This article is a translation of a Japanese article written by the author themselves.)

Using SCRAM Authentication in PostgreSQL

SCRAM authentication is triggered in PostgreSQL when all of the following conditions are met:

1. The password for the user is stored in scram-sha-256 format,
2. The user has registered a password (credential) in the database, and
3. The authentication method selected in pg_hba.conf is either scram-sha-256 or md5*1.

Password Storage Format

Database user passwords are stored in the rolpassword column of the system catalog pg_authid.
When the password_encryption parameter is set to scram-sha-256, the value follows this format as described in the documentation and defined in RFC 5803:

SCRAM-SHA-256$<iteration count>:<salt>$<StoredKey>:<ServerKey>

Here, iteration count, salt, StoredKey, and ServerKey correspond to the values used in the SCRAM computation flow illustrated below:

Diagram: Computation flow in SCRAM authentication
Diagram: SCRAM authentication computation flow

Password Registration

Passwords are registered in PostgreSQL using the PASSWORD clause of the CREATE ROLE or ALTER ROLE command.

For example:
CREATE ROLE yourname ... PASSWORD 'yourpassword';

This sends the plaintext password to the server, where StoredKey and ServerKey are computed.

To avoid sending the plaintext password to the server, you can directly specify the SCRAM format string (as defined in RFC 5803) in the PASSWORD clause*2.
PostgreSQL tools provide client-side computation methods:

• PQencryptPasswordConn function in libpq (documentation)
• \password meta-command in psql (documentation)

Some PostgreSQL variants enforce password policies (e.g., complexity or reuse restrictions) on the server side.
However, if the password is hashed on the client and never sent in plaintext, the server cannot enforce such policies. In such cases, it is the user's responsibility to ensure compliance.

Authentication

The actual authentication method is determined by the server.
If the server selects password authentication, the plaintext password is sent.
To prevent this, the client can set the require_auth connection parameter explicitly to scram-sha-256 (documentation) to force SCRAM authentication.

What is SCRAM Pass-Through Authentication?

SCRAM pass-through authentication takes advantage of the SCRAM property discussed in the previous article: during a successful authentication session, the server obtains ClientKey, which is a credential equivalent to the user's password.

Using ClientKey, the server can generate ClientSignature and ClientProof, and authenticate itself to another server that shares the same credentials—effectively impersonating the user.

SCRAM Pass-Through Authentication in PostgreSQL

The following commit was added to the PostgreSQL master branch (REL_18_BETA1):

postgres_fdw: SCRAM authentication pass-through
This enables SCRAM authentication for postgres_fdw when connecting to
a foreign server without having to store a plain-text password on user
mapping options.

This is done by saving the SCRAM ClientKey and ServerKey from the
client authentication and using those instead of the plain-text
password for the server-side SCRAM exchange. The new foreign-server
or user-mapping option "use_scram_passthrough" enables this.

According to the PostgreSQL 18 Beta documentation, when using postgres_fdw, if both the source and target servers share the same credentials and the user authenticates to the source with scram-sha-256, the source server can use the recovered ClientKey to authenticate to the target. This eliminates the need to store plain-text passwords in the pg_user_mapping system catalog.

To support this, a new libpq connection parameter scram_client_key has been introduced*3.

Using SCRAM Pass-Through Authentication in postgresql_fdw

Setting Passwords for Remote Server Users

When using SCRAM pass-through authentication, the credentials of the source and destination servers must be exactly the same, including salt and other information. Refer to the credentials of the source server from the pg_authid system catalog and specify them directly in the PASSWORD clause as the password for the user on the destination server.

# Referencing credentials of the source server
SELECT rolpassword FROM pg_authid WHERE rolname = 'test'
                                    rolpassword

--------------------------------------------------------------------------
 SCRAM-SHA-256$4096:test-salt$test-stored-key:test-server-key
(1 row)

# Change credentials of server to connect to (requires administrator privileges)
ALTER ROLE test PASSWORD 'SCRAM-SHA-256$4096:test-salt$test-stored-key:test-server-key'

Creating User Mappings

When creating a user mapping to an external server, use the use_scram_passthrough option instead of the password option.

# PostgreSQL 17 and earlier
 CREATE USER MAPING FOR test SERVER server2 OPTIONS (user 'test', password 'testpassword');

# PostgreSQL 18
 CREATE USER MAPING FOR test SERVER server2 OPTIONS (user 'test', use_scram_passthrough 'true');

Connect to the source server

Connect to the source PostgreSQL server; since SCRAM pass-through authentication only passes credentials, the source server must be logged in with SCRAM authentication.

Storage and Use of ClientKey in Pass-Through Authentication

In PostgreSQL, SCRAM authentication message exchanges are handled in the scram_exchange function in src/backend/libpq/auth-scram.c.

As of May 25, 2025, the master branch unconditionally copies ClientKey into the global MyProcPort structure upon successful authentication (source).
This information continues to be maintained during the lifetime of the backend process associated with the connection.

In postgres_fdw, if the MyProcPort structure contains the SCRAM keys and use_scram_passthrough is enabled, those keys are added to the connection parameters (source).

Conclusion

The previous article explained how a server can reconstruct ClientKey during SCRAM authentication, and how this enables transparent login to another server in the same credential-sharing cluster. PostgreSQL’s SCRAM pass-through authentication is a direct application of this property.

Although this feature must be explicitly enabled, it seems intended not for security reasons, but to give administrators control over the chosen authentication method.
Even in environments where postgres_fdw is not used, ClientKey is unconditionally retained in memory after a SCRAM login. The MyProcPort structure where the data is saved is a global variable, and is an area that can be easily referenced if extension modules or user-defined functions to be added to the PostgreSQL server are implemented in C language (or its wrapper).
Thus, DBAs must continue to verify the trustworthiness of any added modules.

Personally I feel more at ease when I can choose not to keep the ClientKey in memory.

Footnotes

*1: Even if the method is md5, SCRAM authentication will be used if the password is stored in SCRAM format, for compatibility reasons.

*2: This means that PostgreSQL does not transparently accept any arbitrary password format.

*3: A corresponding scram_server_key parameter is also added for server authentication.