PostgreSQL 18 brings a nice improvement for folks using postgres_fdw or dblink_fdw: SCRAM pass-through authentication. You no longer need to store plain-text passwords in your USER MAPPING options when setting up a foreign server connection.
Here’s the commit that made it happen:
commit 761c79508e7fbc33c1b11754bdde4bd03ce9cbb3
Author: Peter Eisentraut <peter@eisentraut.org>
Date: Wed Jan 15 17:55:18 2025 +0100
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 ServeryKey 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.
Co-authored-by: Matheus Alcantara <mths.dev@pm.me>
Co-authored-by: Peter Eisentraut <peter@eisentraut.org>
Discussion: https://www.postgresql.org/message-id/flat/27b29a35-9b96-46a9-bc1a-914140869dac@gmail.com
As the commit message itself say, when the PostgreSQL server connects to a FOREIGN SERVER, if use_scram_passthrough is set, it uses the SCRAM keys from the original client connection instead of needing a plain-text password. It's more secure and avoids messy credential duplication.
To use this feature, make sure:
- The foreign server requires
scram-sha-256auth (otherwise it'll just fail). - Only the "client side" (where you're using postgres_fdw or dblink_fdw) needs to be PostgreSQL 18+.
- Both servers must have the same SCRAM secret for the user. That means literally the same hash, salt, and iteration count.
- The initial connection from the client to the main server must also be using SCRAM (Hence “pass-through”: SCRAM must be used going in and out).
How to set it up with postgres_fdw
We're going to use two Postgres servers: one acting as the "incoming" (fdw client) and one as the "foreign" server.
Note that for these examples I'll use psql Postgres client.
1. Create the same user on both servers
CREATE USER example;
On the foreign server, create a sample table to query later:
CREATE TABLE fdw_table AS SELECT g as a, b+2 as b FROM generate_series(1,100) g(g);
Exit psql and log in again using the new created user and then set the passwords on both servers
\password
2. Update pg_hba.conf to require SCRAM
Both servers must be configured to enforce scram-sha-256:
local all all scram-sha-256
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
You can find the path to pg_hba.conf using:
SHOW hba_file;
3. Sync the encrypted password (SCRAM secret)
Grab the encrypted password from the incoming server:
SELECT rolpassword FROM pg_authid WHERE rolname = 'example';
Now set the exact same password (SCRAM hash) on the foreign server:
ALTER ROLE example PASSWORD 'scram-sha-256$...'; -- paste the whole thing
This step is crucial — the secrets must match exactly.
4. Set up postgres_fdw
On the incoming server:
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER foreign_fdw
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'postgres', use_scram_passthrough 'true');
CREATE USER MAPPING FOR example
SERVER foreign_fdw
OPTIONS (user 'example');
Notice: No need to set a password in the mapping!
5. Import the foreign table
IMPORT FOREIGN SCHEMA public LIMIT TO (fdw_table)
FROM SERVER foreign_fdw INTO public;
Now just run:
SELECT * FROM fdw_table;
Boom 💥 — we're querying across servers with SCRAM pass-through.
What about dblink_fdw?
All setup steps are the same, but instead of importing tables, you'll call dblink_fdw() directly:
SELECT * FROM dblink('foreign_fdw', 'SELECT * FROM fdw_table')
AS fdw_table(a int, b int);
Final Thoughts
SCRAM pass-through is a great feature for secure, credential-less connections between PostgreSQL servers. It's particularly useful in setups where you're federating access to multiple databases and don't want to juggle passwords in USER MAPPING.
Less boilerplate, more safety. That’s a win.