DEV Community: supi sula

SCRAM Authentication in PostgreSQL and SCRAM Pass-Through Authentication

supi sula — Mon, 26 May 2025 10:36:36 +0000

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:

The password for the user is stored in scram-sha-256 format,
The user has registered a password (credential) in the database, and
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.

Why Does SCRAM Authentication Take This Form?

supi sula — Mon, 26 May 2025 10:34:14 +0000

Introduction

One form of password-based authentication is SCRAM (Salted Challenge Response Authentication Mechanism). SCRAM is a type of challenge-response authentication that uses passwords. It achieves the following:

The server can verify that the client possesses the same knowledge as when the password was registered.
The client can verify that the server possesses the knowledge entrusted to it at the time of password registration.
At no point — during registration or authentication — is there any need to expose the plaintext password outside the client, i.e., the password is never sent to the server.

Because the server is never told the password, even if the password is reused elsewhere, the server cannot misuse it.

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

Overview of SCRAM Authentication

For a detailed description of the SCRAM protocol, refer to this article (in Japanese).
My post focuses not on protocol specifics, but on the information known only to the client or server, and the role of one-way function in the computations.

Computation Flow of Key Values in SCRAM

Below is a diagram showing the information involved in SCRAM and the direction of their derivation.
Rectangles represent data; bold arrows represent one-way function; ⊕ denotes XOR. The arrow direction indicates ease of computation.

Colors indicate data types:

Orange: Secret information that must not be disclosed by the client
Green: Information stored on the server
Yellow: Per-session information

Diagram: Computation flow in SCRAM authentication

(This diagram is a reconstruction based on one found in the previously referenced blog post, customized to reflect my own focus.)

Password Registration

When a password is registered, the client derives a SaltedPassword from the plaintext password along with a salt and iteration-count. Both the password and SaltedPassword must remain secret.

From the SaltedPassword, two keys — ClientKey and ServerKey — are derived, using HMACs with fixed labels "ClientKey" and "ServerKey". Then, ClientKey is hashed to produce StoredKey, and both StoredKey and ServerKey are stored on the server, along with the salt and iteration count. (*1)

This computation can be done server-side, but may also be performed on the client, which then sends only the results to the server. If all computations are client-side, the server knows only StoredKey and ServerKey, and cannot derive ClientKey, SaltedPassword, or the original password due to the one-way nature of the functions involved.

The “knowledge entrusted to the server” during registration refers to the pair of StoredKey and ServerKey.

Diagram: Password registration

During Authentication

SCRAM authentication consists of two message exchanges initiated by the client.

The first exchange constructs AuthMessage, which functions as a challenge for both client and server. It includes a client-generated and server-generated nonce, ensuring the message is unique per session.

The second exchange performs the main authentication, with response computation, transmission, and verification.

The client sends ClientProof, asserting it has the same knowledge as during registration.

The server sends ServerSignature, asserting it possesses the entrusted knowledge.

Creating and Verifying ClientProof

The client, knowing the password, can compute SaltedPassword, ClientKey, and StoredKey (*2). Using AuthMessage, it calculates ClientSignature, then derives ClientProof via XOR with ClientKey, and sends this to the server.

Diagram: Client creating ClientProof

The server, knowing StoredKey and AuthMessage, computes ClientSignature, then XORs it with the received ClientProof to recover ClientKey. It re-derives StoredKey and verifies it against the stored value.
If this matches the stored StoredKey, the server can conclude that the client in this authentication session was able to compute the correct ClientKey — that is, the client knows ClientKey.
Since computing ClientKey requires the original password, the server interprets this as evidence that the client still possesses the same knowledge as at the time of password registration.
Of course, this interpretation is based on the assumption that knowing ClientKey is equivalent to knowing the password. This is considered an intentional design trade-off in SCRAM.

Diagram: Server verifying ClientProof

Creating and Verifying ServerSignature

The server computes ServerSignature from its stored ServerKey and the session's AuthMessage.

Diagram: Server creating ServerSignature

The client, using its computed ServerKey and AuthMessage, calculates ServerSignature and compares it to the received one. A match confirms that the server knows ServerKey and is thus authentic.

Diagram: Client verifying ServerSignature

The Implications of the Server Being Able to Derive `ClientKey`

Although ClientKey is not stored on the server, it is reconstructed during successful authentication. Possession of ClientKey allows the server to compute valid ClientSignature and ClientProof for any session — effectively making ClientKey equivalent to the password in terms of credential power.

What the Server Can Do with `ClientKey`

Once the server reconstructs ClientKey, it can theoretically:

Log in to itself as the user
Log in to a different server (B) as the user, if the same password, salt, and iteration count are used on both servers A and B

In theory, it also allows:

Server A impersonating user X to itself — Since this is a legitimate connection, user X may not be able to deny this connection
User X denying a connection to server A for the same reason

Is Server Use of `ClientKey` an Abuse?

Although password reuse is a user responsibility, matching salts and iteration-counts are rare (*3). Logging into another server using ClientKey typically occurs in intentionally credential-sharing environments, like server clusters.

Thus, such usage may not constitute abuse.

Is This a SCRAM Vulnerability?

The fact that ClientKey is reconstructible is a consequence of its role in authentication. The ability to generate ClientProof from ClientKey is an expected feature, not a flaw.

Questions and Answers

Why Doesn’t the Server Store `ClientKey`?

If the server ends up reconstructing ClientKey, why not store it instead of StoredKey?

A: StoredKey is stored long-term, whereas ClientKey is only briefly reconstructed. Because ClientKey is a full credential, storing StoredKey instead limits exposure risk.

Why Not Just Send `ClientSignature`?

Why does the client need to send ClientProof instead of just ClientSignature?

A: Accepting ClientSignature as proof would mean treating knowledge of StoredKey as equivalent to client identity. Since StoredKey is long-term and stored on the server, this weakens security — akin to storing passwords directly. ClientProof, requiring knowledge of ClientKey, avoids this issue.

Why Not Use MAC with `ClientKey` Instead?

Why not just use ClientKey to MAC the AuthMessage directly and treat that as "ClientSignature"?

A: In that case, the server couldn’t verify it, since it doesn’t know ClientKey.
So how can a server that only knows the StoredKey verify that the client in this authentication session knows the upstream value (ClientKey) of the one-way function? To solve this problem, the bidirectionality of the XOR operation*4 is used. It is not possible to calculate the inverse function of a one-way function, but the bidirectionality of the XOR operation makes it possible to calculate the upstream value.

Why Is `ServerSignature` Needed?

Why not use ClientSignature for server authentication too?

A: ClientSignature requires StoredKey or ClientKey. The client’s ClientProof alone doesn’t allow the server to recover either. Therefore, the ability to compute ClientSignature implies possession of StoredKey. Still, using a distinct ServerKey and ServerSignature clarifies the structure.

Conclusion

SCRAM authentication never requires the client to reveal the plaintext password—either during registration or authentication.

However, it’s important to note that ClientKey, a secret that fully serves as a credential, can be reconstructed by a legitimate server (that knows StoredKey) during authentication. This differs from public-key challenge-response schemes.

On the other hand, a non-legitimate server (without StoredKey) cannot obtain ClientKey. This sharply distinguishes SCRAM from hash-based authentication schemes that indiscriminately expose credentials to all servers.

Footnotes
*1: The SCRAM standard requires both to be stored. If the client remembers them, storage on the server isn’t strictly necessary for mutual authentication.

*2: The salt and iteration count are sent from the server during the first exchange.

*3: This can happen with improper implementations, such as using the user name as a salt.
*4: XOR has the reversible property: X XOR Y = Z implies X XOR Z = Y and Y XOR Z = X.

DEV Community: supi sula

SCRAM Authentication in PostgreSQL and SCRAM Pass-Through Authentication

Introduction

Using SCRAM Authentication in PostgreSQL

Password Storage Format

Password Registration

Authentication

What is SCRAM Pass-Through Authentication?

SCRAM Pass-Through Authentication in PostgreSQL

Using SCRAM Pass-Through Authentication in postgresql_fdw

Setting Passwords for Remote Server Users

Creating User Mappings

Connect to the source server

Storage and Use of ClientKey in Pass-Through Authentication

Conclusion

Why Does SCRAM Authentication Take This Form?

Introduction

Overview of SCRAM Authentication

Computation Flow of Key Values in SCRAM

Password Registration

During Authentication

Creating and Verifying ClientProof

Creating and Verifying ServerSignature

The Implications of the Server Being Able to Derive ClientKey

What the Server Can Do with ClientKey

Is Server Use of ClientKey an Abuse?

Is This a SCRAM Vulnerability?

Questions and Answers

Why Doesn’t the Server Store ClientKey?

Why Not Just Send ClientSignature?

Why Not Use MAC with ClientKey Instead?

Why Is ServerSignature Needed?

Conclusion

Using SCRAM Pass-Through Authentication in `postgresql_fdw`

Storage and Use of `ClientKey` in Pass-Through Authentication

The Implications of the Server Being Able to Derive `ClientKey`

What the Server Can Do with `ClientKey`

Is Server Use of `ClientKey` an Abuse?

Why Doesn’t the Server Store `ClientKey`?

Why Not Just Send `ClientSignature`?

Why Not Use MAC with `ClientKey` Instead?

Why Is `ServerSignature` Needed?