SqlMedConnectionManager

摘自 PostgreSQL Wiki
跳转至导航跳转至搜索
构建中...

这份设计草案描述了 PostgreSQL 中与 SQL/MED 兼容的连接管理器。最初的方式是仅提供连接信息管理，同时将实际连接处理留给客户端应用程序。


我们为什么要这么做？

  • 通过内置连接定义存储库在不同模块之间共享。
  • 对远程连接进行访问控制和记录。
  • 能更好地控制哪些人实际创建连接。
  • 可以使连接无效，这对于 pl/proxy 来说非常有用。
  • 这是进一步实施 SQL/MED 功能的先决条件。


设计注释

服务器
服务器描述与远程数据库的连接。所有连接详细信息（例如，主机名、端口、数据库名称等）均通过通用选项提供。
用户映射
将本地数据库用户映射到远程，通用选项中提供了可能的远程凭据（可能包含敏感数据）。
外来数据包装程序
FDW 处理外来数据访问的所有方面，包括连接管理、数据类型转换等。每个外来服务器都与一个 FDW 精确关联，但 FDW 可用于访问多个外来服务器。

对于连接管理器，FDW 主要有两项职责：一，解析和验证服务器和用户映射的通用选项；二，为应用程序提供连接信息。

FDW 以符合特定 API 的共享库形式实现。最终，每种连接类型（如 dbi、oci、odbc 等）都将使用 FDW。至少需要一个采用直通模式运行的 FDW -- 选项未验证，连接查找返回原始服务器和用户映射。它还可以作为未来 FDW 实现的参考样例。

SQL 语法

<foreign-data wrapper definition> ::=
    CREATE FOREIGN DATA WRAPPER <foreign-data wrapper name>
    [ LIBRARY <library name specification> ] <language clause> [ <generic options> ]
<alter foreign-data wrapper statement> ::=
    ALTER FOREIGN DATA WRAPPER <foreign-data wrapper name>
    [ <library name specification> ] [ <alter generic options> ]
<drop foreign-data wrapper statement> ::=
    DROP FOREIGN DATA WRAPPER <foreign-data wrapper name> <drop behavior>

<foreign server definition> ::=
    CREATE SERVER <foreign server name>
    [ TYPE <server type> ] [ VERSION <server version> ]
    FOREIGN DATA WRAPPER <foreign-data wrapper name> [ <generic options> ]
<alter foreign server statement> ::=
    ALTER SERVER <foreign server name> [ <new version> ] [ <alter generic options> ]
<drop foreign server statement> ::=
    DROP SERVER <foreign server name> <drop behavior>

<user mapping definition> ::=
    CREATE USER MAPPING FOR <authorization identifier | USER | CURRENT_USER | PUBLIC>
    SERVER <foreign server name> [ <generic options> ]
<alter user mapping statement> ::=
    ALTER USER MAPPING <specific or generic authorization identifier>
    SERVER <foreign server name> <alter generic options>
<drop user mapping statement> ::=
    DROP USER MAPPING FOR <specific or generic authorization identifier>
    SERVER <foreign server name>

CREATE FOREIGN DATA WRAPPER pgsql LIBRARY 'pgsql_fdw';
CREATE SERVER foo FOREIGN DATA WRAPPER pgsql OPTIONS (host 'remotehost', dbname 'remotedb');
CREATE USER MAPPING FOR PUBLIC SERVER foo OPTIONS (username 'bob', password 'secret');
  • 添加外来数据包装程序和外部服务器至具有 USAGE 权限的对象列表中。
  • 最初，只允许超级用户操作 FDW
  • CREATE USER MAPPING 会在服务器日志中留下纯文本密码（CREATE USER 亦是如此）。
  • 由于 SQL/MED 没有被广泛采用，因此我们可以适当放宽一些语法规则，并舍弃无关的子句。

其他系统目录

为 fdw、服务器和用户映射引入系统目录。这些内容只能对超级用户可见。为普通用户提供视图或访问函数。

Table "pg_catalog.pg_foreign_data_wrapper"
    Column    |   Type    | Modifiers 
--------------+-----------+-----------
 fdwname      | name      | not null
 fdwnamespace | oid       | not null
 fdwowner     | oid       | not null
 fdwlibrary   | text      | 
 fdwacl       | aclitem[] | 
 fdwoptions   | text[]    | 
Indexes:
    "pg_foreign_data_wrapper_name_nsp_index" UNIQUE, btree (fdwname, fdwnamespace)
    "pg_foreign_data_wrapper_oid_index" UNIQUE, btree (oid)

template1=# \d pg_foreign_server
 Table "pg_catalog.pg_foreign_server"
    Column    |   Type    | Modifiers 
--------------+-----------+-----------
 srvname      | name      | not null
 srvnamespace | oid       | not null
 srvowner     | oid       | not null
 srvfdw       | oid       | not null
 srvacl       | aclitem[] | 
 srvoptions   | text[]    | 
Indexes:
    "pg_foreign_server_name_nsp_index" UNIQUE, btree (srvname, srvnamespace)
    "pg_foreign_server_oid_index" UNIQUE, btree (oid)

template1=# \d pg_foreign_user_mapping
Table "pg_catalog.pg_foreign_user_mapping"
   Column   |  Type  | Modifiers 
------------+--------+-----------
 usesysid   | oid    | not null
 usesrv     | oid    | not null
 useoptions | text[] | 
Indexes:
    "pg_foreign_user_mapping_usesrvsysid_index" UNIQUE, btree (usesrv, usesysid)
  • 在pg_foreign_user_mapping InvalidOid表示 PUBLIC 访问。

外部数据包装器 API

GetConnectionInfo(fdw、server、user)
提供指定服务器和用户映射的连接详细信息。
List *
GetConnectionInfo(ForeignDataWrapper *fdw, ForeignServer *server,
                    ForeignUserMapping *um)
validateOptionList(fdw、what、options)
验证针对FDW, 服务器或下列内容的通用选项用户映射.
void
validateOptionList(ForeignDataWrapper *fdw, GenericOptionFlags flags,
                   List *options)

针对虚拟 FDW，函数大多为 nops -- 验证总是成功，连接查找返回服务器和用户映射的原始选项列表。这允许应用程序使用连接管理器，而无需提供自己的 FDW 库。缺点在于应用程序必须能够从原始选项构建连接字符串 -- 并非总是如此简单。

对于更复杂的 FDW（如 pgsql），验证例程将检查提供给服务器的选项是否为有效的 libpq conninfo 参数。可能还会检查 server 选项中是否未出现与用户映射相关的参数。连接查找将服务器选项和用户映射选项合并为单个连接字符串。

连接管理器 C API

为存储的 C 函数、过程语言处理程序等提供连接管理器界面。

GetForeignServer(serverid)
查找外部服务器定义。
ForeignServer *GetForeignServer(Oid serverid);
GetForeignUserMapping(userid、serverid)
查找本地用户的远程映射。
ForeignUserMapping *GetForeignUserMapping(Oid userid, Oid serverid);
GetForeignDataWrapper(fdwid)
查找外部数据包装器。
ForeignDataWrapper *GetForeignDataWrapper(Oid fdwid);
GetForeignDataWrapperInterface(fdw)
从缓存中获取 FDW 界面。加载外部函数。
ForeignDataWrapperInterface *GetForeignDataWrapperInterface(ForeignDataWrapper *fdw);
GetRemoteConnectionInfo(serverid、userid)
结合上述所有信息来获取连接详细信息。
List *GetRemoteConnectionInfo(Oid serverid, Oid userid);

SQL API

为客户端，我们提供一个可以访问 SQL 的函数，用于查找服务器和用户映射，然后调用 FDW 以获取连接详细信息。类似以下内容

# select * from pg_get_remote_connection_info('remotedb');

key         value
----------- ----------------------------------------
datasource  dbname=remotedb host=remotehost user=...

虽然其他 FDW（如 dbi）也可能包括用户名和密码的单独元组

key         value
----------- ------------------------------------------------------------
datasource  dbi:mysql:database=sakila;host=localhost
username    root
password    salakala
  • 权限检查和用户映射查找使用当前有效的用户 ID 执行。在某些情况下，这需要的是会话用户或角色。对于这个目的，我们提供了一个由 2 个参数组成的函数版本，它还接受用户名。在此版本中，将针对提供的用户检查权限。
  • 最初，只有超级用户才能访问此函数。使用它的客户端模块还必须归特权用户所有并且定义为SECURITY DEFINER。然后可以将客户端模块访问权限授予普通用户，而不会泄露连接详细信息。

权限

superuser SERVER 所有者 FDW USAGE SERVER USAGE
CREATE FDW x
ALTER FDW x
DROP FDW x
GRANT USAGE ON FDW x
CREATE SERVER x x
ALTER SERVER x x
DROP SERVER x x
GRANT USAGE ON SERVER x x
CREATE USER MAPPING x x
ALTER USER MAPPING x x
DROP USER MAPPING x x
GetRemoteConnectionInfo() x x x
pg_get_remote_connection_info() x


  • 目前只有超级用户才能创建外部数据包装器。禁止 FDW 所有者变更。
  • 基本上的 GRANT USAGE on FDW 授予在该 FDW 上创建服务器的权限。
  • SERVER 不需要 FDW USAGE。
  • GetRemoteConnectionInfo() 和 pg_get_remote_connection_info() 检查所提供用户的权限。
  • 允许非超级用户调用 GetRemoteConnectionInfo() 应该是安全的 - 调用函数使用不可信语言，因而只能由超级用户部署。

psql 支持

  • 需要额外的 \dX 命令来显示远程连接信息。
  • 新语法的制表符补全。
  • 帮助

pg_dump 支持

  • 需要能够转储 SERVER/USER MAPPING/FDW。
  • 普通用户无法转储连接定义。
  • 小心转储中的明文密码。

使用和统计信息日志记录

记录对远程连接的访问。

示例

pgsql

CREATE SERVER foodb FOREIGN DATA WRAPPER pgsql
    OPTIONS (dbname 'foodb', host 'bar');

CREATE USER MAPPING FOR public SERVER foodb
    OPTIONS (username 'bob', password 'salakala');

ERROR:  Invalid option "username" to user mapping
HINT:  Valid user mapping options are: user, password

CREATE USER MAPPING FOR public SERVER foodb
    OPTIONS (user 'bob', password 'salakala');

select * from pg_get_remote_connection_info('foodb');

   option   |                      value                       
------------+--------------------------------------------------
 datasource | dbname=foodb host=bar user=bob password=salakala
(1 row)

select * from pg_get_remote_connection_info('foodb', 'bob');

ERROR:  permission denied for foreign server foodb

GRANT USAGE ON SERVER foodb TO bob;

select * from pg_get_remote_connection_info('foodb', 'bob');
   option   |                      value                       
------------+--------------------------------------------------
 datasource | dbname=foodb host=bar user=bob password=salakala
(1 row)

"default" FDW

CREATE SERVER bazdb FOREIGN DATA WRAPPER "default"
    OPTIONS (tnsname 'ORCL');

CREATE USER MAPPING FOR bob SERVER bazdb OPTIONS (lusername 'scott', passw0rd 'tiger');
GRANT USAGE ON SERVER bazdb TO bob;

select * from pg_get_remote_connection_info('bazdb', 'bob');

  option   | value 
-----------+-------
 tnsname   | ORCL
 lusername | scott
 passw0rd  | tiger
(3 rows)

DBI 和 Excel

CREATE FOREIGN DATA WRAPPER dbi LIBRARY 'default_fdw';

CREATE SERVER dbdtest FOREIGN DATA WRAPPER dbi
    OPTIONS (
        datasource 'DBI:Excel:file=dbdtest.xls',
        attributes '{xl_vtbl => {TESTV => { ... } }}'
    );

CREATE USER MAPPING FOR current_user SERVER dbdtest;

select * from pg_get_remote_connection_info('dbdtest');

   option   |              value               
------------+----------------------------------
 datasource | DBI:Excel:file=dbdtest.xls
 attributes | {xl_vtbl => {TESTV => { ... } }}
(2 rows)

plproxy 群集

如何描述 plproxy 群集的一个示例。实际上，“default_fdw”将由实际的 plproxy 库替换。

CREATE FOREIGN DATA WRAPPER plproxy LIBRARY 'default_fdw';

CREATE SERVER userdb TYPE 'plproxy_cluster'
    FOREIGN DATA WRAPPER plproxy
    OPTIONS (
        server1 'dbname=userdb_p0 host=127.0.0.1 port=6000',
        server2 'dbname=userdb_p1 host=127.0.0.1 port=6000',
        server3 'dbname=userdb_p2 host=127.0.0.1 port=6000',
        server4 'dbname=userdb_p3 host=127.0.0.1 port=6000',
        connection_lifetime=3600
    );

CREATE USER MAPPING FOR current_user SERVER userdb;

select * from pg_get_remote_connection_info('userdb');
       option        |                   value                   
---------------------+-------------------------------------------
 server1             | dbname=userdb_p0 host=127.0.0.1 port=6000
 server2             | dbname=userdb_p1 host=127.0.0.1 port=6000
 server3             | dbname=userdb_p2 host=127.0.0.1 port=6000
 server4             | dbname=userdb_p3 host=127.0.0.1 port=6000
 connection_lifetime | 3600
(5 rows)

dblink

dblink 如何与外部服务器配合使用的一个示例。

CREATE SERVER foo FOREIGN DATA WRAPPER pgsql OPTIONS (host 'localhost', port '5432', dbname 'test');

CREATE USER MAPPING FOR current_user SERVER foo OPTIONS (user 'test', password 'test');

SELECT * FROM pg_get_remote_connection_info('foo');
 option_name |                         option_value                         
-------------+--------------------------------------------------------------
 datasource  | host=localhost port=5432 dbname=test user=test password=test
(1 row)

SELECT * FROM dblink_connect_s('c1', 'foo');
 dblink_connect_s
-----------------
 OK
(1 row)

SELECT * FROM dblink('c1', 'select proname, prosrc from pg_proc') 
    AS t1(proname name, prosrc text) WHERE proname LIKE 'byteal%';
  proname  |  prosrc   
-----------+-----------
 bytealt   | bytealt
 byteale   | byteale
 bytealike | bytealike
(3 rows)

Alternatively:

SELECT * FROM dblink_s('foo', 'select proname, prosrc from pg_proc') 
    AS t1(proname name, prosrc text) WHERE proname LIKE 'byteal%';
  proname  |  prosrc   
-----------+-----------
 bytealt   | bytealt
 byteale   | byteale
 bytealike | bytealike
(3 rows)

问题

  • 一些...

链接

检索自 "https://wiki.postgresql.ac.cn/index.php?title=SqlMedConnectionManager&oldid=12185"