PostgreSQL18-FDW連接的 SCRAM 直通身份驗證

PostgreSQL 18 為使用 postgres_fdw 或 dblink_fdw 的人帶來了很好的改進：SCRAM 直通身份驗證。設置外部服務器連接時，您不再需要在“用戶映射”選項中存儲純文本密碼。

這是實現它的提交：

commit 761c79508e7fbc33c1b11754bdde4bd03ce9cbb3
Author: Peter Eisentraut <peter@eisentraut.org>
Date:   Wed Jan 15 17:55:18 2025 +0100postgres_fdw: SCRAM authentication pass-throughThis enables SCRAM authentication for postgres_fdw when connecting toa foreign server without having to store a plain-text password on usermapping options.This is done by saving the SCRAM ClientKey and ServeryKey from theclient authentication and using those instead of the plain-textpassword for the server-side SCRAM exchange.  The new foreign-serveror 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

正如提交消息本身所說，當PostgreSQL服務器連接到FOREIGN SERVER時，如果設置了use_scram_passthrough，它將使用原始客戶端連接中的SCRAM密鑰，而不需要純文本密碼。它更安全，避免了混亂的憑據重復。

要使用此功能，請確保：

• 外部服務器需要 scram-sha-256 身份驗證（否則它只會失敗）。
• 只有“客戶端”（您使用 postgres_fdw 或 dblink_fdw）需要是 PostgreSQL 18+。
• 兩臺服務器必須為用戶設置相同的 SCRAM 密鑰。這意味著哈希值、鹽值和迭代次數確實完全相同。
• 從客戶端到主服務器的初始連接也必須使用 SCRAM（因此"直通"：必須使用 SCRAM 進出）。

如何使用postgres_fdw進行設置

我們將使用兩個 Postgres 服務器：一個充當"傳入"（fdw 客戶端），一個充當"外部"服務器。

請注意，對于這些示例，我將使用 psql Postgres 客戶端。

1. 在兩臺服務器上創建相同的用戶

CREATE USER example;

在外部服務器上，創建一個示例表以供以后查詢：

CREATE TABLE fdw_table AS SELECT g as a, b+2 as b FROM generate_series(1,100) g(g);

退出 psql 并使用新創建的用戶重新登錄，然后在兩臺服務器上設置密碼

\password

2. 更新 pg_hba.conf 以需要 SCRAM

必須將兩臺服務器配置為強制執行 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

您可以使用以下方法找到 pg_hba.conf 的路徑：

SHOW hba_file;

3. 同步加密密碼（SCRAM 密鑰）

從傳入服務器獲取加密密碼：

SELECT rolpassword FROM pg_authid WHERE rolname = 'example';

現在在外部服務器上設置完全相同的密碼（SCRAM 哈希）：

ALTER ROLE example PASSWORD 'scram-sha-256$...'; -- paste the whole thing

這一步至關重要——機密必須完全匹配。

4. 設置postgres_fdw

在傳入服務器上：

CREATE EXTENSION IF NOT EXISTS postgres_fdw;CREATE SERVER foreign_fdwFOREIGN DATA WRAPPER postgres_fdwOPTIONS (host 'localhost', dbname 'postgres', use_scram_passthrough 'true');CREATE USER MAPPING FOR exampleSERVER foreign_fdwOPTIONS (user 'example');

注意：無需在映射中設置密碼！

5. 導入國外表

IMPORT FOREIGN SCHEMA public LIMIT TO (fdw_table)FROM SERVER foreign_fdw INTO public;

現在只需運行：

SELECT * FROM fdw_table;

繁榮 💥 — 我們正在使用 SCRAM 直通跨服務器進行查詢。

dblink_fdw呢？

所有設置步驟都是相同的，但你不會導入表，而是直接調用 dblink_fdw（） ：

SELECT * FROM dblink('foreign_fdw', 'SELECT * FROM fdw_table')AS fdw_table(a int, b int);

最后的思考

SCRAM 直通是 PostgreSQL 服務器之間安全、無憑據連接的一項重要功能。它在您聯合訪問多個數據庫并且不想在用戶映射中處理密碼的設置中特別有用。

更少的樣板，更多的安全。這是一個勝利。