PG18 Hacktober: 31 Days of New Features : postgres_fdw: SCRAM authentication without storing the password

In today’s post of the PG18 Hacktober segment!, we’re going to discuss the  postgres_fdw SCRAM authentication pass throughout.

Introduction :

PostgreSQL 18 introduces SCRAM authentication pass-through support for the postgres_fdw (Foreign Data Wrapper), making cross-server authentication both simpler and more secure.

Previously, when connecting to a foreign server using postgres_fdw, users were required to provide a password in both the user mapping and the server connection options. With this enhancement, the password no longer needs to be stored in the user mapping.

Traditionally, dblink_connstr_check and dblink_security_check only verified whether a password was included in the connection options. Now, when SCRAM pass-through authentication is enabled, the password can be securely passed using the option require_auth=scram-sha-256.

This new capability eliminates the need to store plain-text passwords in user mappings, simplifying authentication management and strengthening security for PostgreSQL cross-server connections.

What is a SCRAM?

SCRAM (Salted Challenge Response Authentication Mechanism) It is a secure authentication method that protects passwords during transmission and storage. PostgreSQL started supporting SCRAM in version 10, and now version 18 expands that support to postgres_fdw.

Note: Make sure you have installed the “postgres_fdw”, “dblink” extensions before you go ahead and execute the following example.

Let’s make sure :

samdb=# CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE EXTENSION
samdb=# CREATE EXTENSION dblink;
CREATE EXTENSION

List of Extensions:

samdb=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+--------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.2 | public | foreign-data wrapper for remote PostgreSQL servers
(3 rows)

Before PostgreSQL 18: Creating User Mapping with a password

In PostgreSQL versions before 18, when using postgres_fdw to connect to a foreign server, it was necessary to explicitly specify the password in the user mapping.

samdb=# CREATE USER MAPPING FOR postgres SERVER foreign_server
OPTIONS (user 'postgres', password 'postgres');
CREATE USER MAPPING

Explanation:

foreign_server is the foreign data wrapper connection to another PostgreSQL server.

The username and password (postgres) are provided directly in the mapping.

This raises security concerns, as passwords are stored in system catalogs and visible to superusers.

PostgreSQL 18: Creating a Foreign Server with SCRAM Pass-through Authentication

With PostgreSQL 18, you can now use SCRAM authentication pass-through with postgres_fdw, eliminating the need to store a password in the user mapping. This makes cross-database connections both more secure and easier to manage.

Step 1: Creating a Foreign Server and User Mapping without Password

samdb=# CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.232.146', dbname 'sam', require_auth 'scram-sha-256');
CREATE SERVER

Explanation:

host: IP address or hostname of the foreign server.

dbname: The database name on the foreign server.

require_auth=’scram-sha-256′: Enables SCRAM authentication pass-through from the local user PostgreSQL server to the foreign one.

Tell us PostgreSQL 18: “Use SCRAM authentication to pass the already-authenticated user session to the foreign server securely.

Step 2: Create the User Mapping (Without a Password)

samdb=#CREATE USER MAPPING FOR sam SERVER foreign_server
OPTIONS (user 'sam');
CREATE USER MAPPING

• No password is needed here.
• The mapping assumes the role sam and will connect to the remote sam user using the same SCRAM-authenticated session.

Security Checks

Two PostgreSQL functions have been updated to support this:

• dblink_connstr_check
• dblink_security_check

They now understand that SCRAM pass-through is secure even without a visible password in the connection string.

Why This Matters

Improved security – no password storage in FDW user mappings.

Less config management – fewer secrets to rotate.

Better integration – aligns with enterprise security practices.

Summary:

PostgreSQL 18’s SCRAM authentication pass-through for postgres_fdw is a small but powerful improvement. It enhances security by eliminating the need to store passwords in user mappings while still allowing secure cross-database access.

What’s next?

In Day 23 of PG18 Hacktober, we explore one of the most developer-friendly features introduced in PostgreSQL 18: psql pipeline queries. With this new capability, psql can now send multiple SQL statements to the server in true pipelined mode, meaning it no longer waits for each response before sending the next query. Using \startpipeline, \syncpipeline, and \endpipeline, you can build efficient, low-latency client interactions ideal for bulk operations, scripting, or automation tasks.

Think of it as client-side batching with full protocol-level control, and a big win for performance in interactive and DevOps workflows. Tomorrow, we’ll break down how pipelining works, when to use it, and why it’s a stealthy but powerful tool for Postgres power users.

Stay tuned!