PG 18beta1 透传客户端口令｜ 更安全的跨库访问

在PostgreSQL进行跨库访问需要使用Foreign Data Wrapper外部数据包装器，也简称为FDW。通过FDW访问外部数据的模型如下：

FDW接口可以对同构PostgreSQL，或者异构Oracle/MySQL/SQLserver进行读写操作。不过经常被诟病的一点是，需要在PostgreSQL本地明文存储外部数据源的用户口令。

针对该问题，社区也有一定的改进方案：postgre_fdw插件从13版本开始，超级用户在创建用户映射时可设置password_required选项，设置password_required=true来使用trust认证可绕过口令认证，以此提升安全性。

CREATE USER MAPPING FOR SERVER options (user 'xxx',password_required 'false');

注意：设置password_required选项必须要超级用户权限才可操作。

但口令认证方式并未提供安全的解决方案，相比openGauss很早就提供了key文件加密用户口令密文存储的方式：

$ gs_guc generate -S XXX -D $GAUSSHOME/bin -o usermapping

创建用户映射前执行该步骤即可加密存储用户口令。

而今年PostgreSQL 18提供了SCRAM pass-through透传口令的方式，使用postgre_fdw和dblink插件时可享受更安全的远程访问。

接下来进行实操演示：

postgres_fdw插件

远程用户信息为192.168.20.22:1800:postgres:admin:xxx，并将该连接信息配置到.pgpass文件，后面可透传给FDW插件。

远程用户admin下有一张表t1(id int,info varchar)：

postgres=> \dt admin.t1
        List of tables
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 admin  | t1   | table | admin
(1 row)

本地使用postgres超级用户进行如下操作：

postgres=# create database mydb;
CREATE DATABASE
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".

CREATE EXTENSION postgres_fdw ;

CREATE SERVER server1 
  FOREIGN DATA WRAPPER postgres_fdw 
  OPTIONS (
  host '192.168.20.22',
  port '1800', 
  dbname 'postgres'
) ;

CREATE USER MAPPING FOR public 
  SERVER server1 
  OPTIONS (
  user 'admin',
  use_scram_passthrough 'true'
) ;

CREATE FOREIGN TABLE f1 (
  id int,
  info varchar
) SERVER server1
  OPTIONS (
  schema_name 'admin' ,
  table_name 't1'
) ;

做好上面的配置之后，接着进行外部表的访问测试：

$ psql -U postgres mydb -c "insert into f1 values(1,'test')"
INSERT 0 1

$ psql -U postgres mydb -c "select * from f1"
 id | info 
----+------
  1 | test
(1 row)

$ psql -U postgres mydb -c "\deux+"
List of user mappings
-[ RECORD 1 ]--------
Server      | server1
User name   | public
FDW options | ("user" 'admin', use_scram_passthrough 'true')

从测试结果看，外部表f1可正常进行读写操作，且系统表并未明文记录口令。

dblink插件

dblink在PostgreSQL有两种使用方式，其实是建立远程连接有两种方式：第一种是常规方式，先使用dblink_connect建立连接，然后执行dblink函数进行查询或者dblink_exec进行修改操作，最后使用dblink_disconnect关闭连接；第二种方式是FDW的方式。

SCRAM pass-through透传的方式也适用于FDW的使用方式，下面继续进行操作演示：

远程用户信息与上面一致，本地使用postgres超级用户进行如下操作：

\c mydb

CREATE EXTENSION dblink ;

CREATE SERVER server2 
  FOREIGN DATA WRAPPER dblink_fdw 
  OPTIONS (
  host '192.168.20.22',
  port '1800', 
  dbname 'postgres',
  use_scram_passthrough 'true'
) ;

CREATE USER MAPPING FOR public 
  SERVER server2 
  OPTIONS (
  user 'admin'
) ;

做好上面的配置之后，接着进行查询与插入测试：

mydb=# select * from dblink('server2','select * from t1;') as foo(id int , info text);
 id | info 
----+------
  1 | test
(1 row)

mydb=# select dblink_exec('server2', 'insert into t1 select generate_series(10,20), ''hello''');
 dblink_exec 
-------------
 INSERT 0 11
(1 row)

测试总结

• SCRAM pass-through透传方式可用于postgre_fdw及dblink插件，避免明文存储口令。
• use_scram_passthrough选项可设置在SERVER或者USER MAPPING上。

推荐阅读

PG 18beta1 真正的AIO来了｜发展历程&测试
PG 18beta1 真正的AIO来了｜感慨&环境准备
PG 18beta1 今日发布