一、场景说明
用户U_A创建了外部服务器server_A,创建了表T_A,现在用户U_B要访问外表T_A需要哪一些权限？
二、场景准备
1.创建用户U_A
panweidb=# CREATE USER U_A PASSWORD 'panwei$123';
panweidb=# ALTER USER U_A SYSADMIN;
panweidb=# \du U_A
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
U_A | Sysadmin | {}
2.切换到U_A下创建外部服务器
[omm@panwei203 ~]$ gsql -d panweidb -U U_A
panweidb=> CREATE SERVER U_SERVER FOREIGN DATA WRAPPER POSTGRES_FDW OPTIONS(host '192.168.110.91',port '17700',dbname 'panweidb');
CREATE SERVER
--查看外部服务器
1）\des
List of foreign servers
Name | Owner | Foreign-data wrapper
----------------+------------+----------------------
fore_server | u1 | postgres_fdw
fore_server | u2 | postgres_fdw
pwaudit_server | | file_fdw
test_server | loacluser1 | postgres_fdw
u_server | U_A | postgres_fdw
2) select * from pg_catalog.pg_foreign_server;
panweidb=> select * from pg_catalog.pg_foreign_server;
srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
----------------+----------+--------+---------+------------+--------+--------------------------------------------------
pwaudit_server | 0 | 19820 | | | |
fore_server | 20725 | 20716 | | | | {host=192.168.110.91,port=17700,dbname=panweidb}
test_server | 20730 | 20716 | | | | {host=192.168.110.91,port=17700,dbname=panweidb}
fore_server | 20740 | 20716 | | | | {host=192.168.110.91,port=17700,dbname=panweidb}
u_server | 20757 | 20716 | | | | {host=192.168.110.91,port=17700,dbname=panweidb}
需要sysadmin权限才能创建外部服务器，否则会提示权限不足。
panweidb=> CREATE SERVER U_SERVER FOREIGN DATA WRAPPER POSTGRES_FDW OPTIONS(host '192.168.110.91',port '17700',dbname 'panweidb');
ERROR: permission denied for foreign-data wrapper postgres_fdw
DETAIL: N/A
3.创建user mapping
user mapping是创建本地服务器的用户映射到外部服务器用户，外部服务器用户需要具有所需访问表的权限。
panweidb=> CREATE USER MAPPING FOR USER SERVER U_SERVER OPTIONS (user 'extuser1', password 'panwei#123');
CREATE USER MAPPING
user_name
要映射到外部服务器的一个现有用户的名称。 CURRENT_USER和USER匹配当前用户的名称。 当PUBLIC被指定时，一个所谓的公共映射会被创建，当没有 特定用户的映射可用时将会使用它。
OPTIONS ( option 'value' [, ... ] )
这个子句指定用户映射的选项。这些选项通常定义该映射实际的用户名和 口令。选项名必须唯一。允许的选项名和值与该服务器的外部数据包装器 有关。
--查看user mapping信息
select * from pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+-------------+--------+------------+-----------------------------------------------------------------------------------------------------
20718 | 20717 | fore_server | 10 | omm | {user=u1,password=encryptOptD5SGGRWOLjRHtWoKdB3y6O/9vjCnxsSnPHwbgqKa0EWM0HVNX3XzBDI9GaMcgnvD}
20736 | 20734 | test_server | 20730 | loacluser1 | {user=extuser1,password=encryptOpt4DegfR/awOY9CyqD77Jgg9NE8lS9TvsN62CCcNkFIZNQb7qJigbNjfxr1aUbnhIR}
20747 | 20744 | fore_server | 20725 | u1 | {user=u1,password=encryptOptXMRNNzLTd0a+eaMPN5RJMuRp8/Qlnk2z2YRGogM2GPVXAZym3OvAzyEiebS5arzE}
20748 | 20744 | fore_server | 20740 | u2 | {user=u1,password=encryptOptPAAWy1w4GPwTZZEa2ZQEJxzStdBH0XRFc68F9tk3p0dLHdumXMjWRSlW9rhleBuh}
20749 | 20717 | fore_server | 20725 | u1 | {user=u1,password=encryptOptciJH8dveiSspVR9TWTV8GN1gQvQayqGqhXokXG0XIubtBXzuIa59lB1o6DyZix3W}
20756 | 20717 | fore_server | 20740 | u2 | {user=u1,password=encryptOptcJzwV2kitpvaf+zY2dcxDe8A9APn8Ec81kZxcF1igYiA9vfedyIuEWul5fkzl4el}
20762 | 20761 | u_server | 20757 | U_A | {user=extuser1,password=encryptOptH/v9WtG7Ocb8mJokILIcLoZcXk1fQhUN+5oxIzUUrb5wCyC3YtmsiQZOIUICA0Dv}
4.创建外表
说明：创建外表的用户需要映射到外部服务器的用户才能建表
panweidb=> CREATE FOREIGN TABLE F_A(
id INT,
name VARCHAR(10)
) SERVER U_SERVER OPTIONS (SCHEMA_NAME 'extuser1',TABLE_NAME 'extt1');
CREATE FOREIGN TABLE
--查看表
panweidb=> \d
List of relations
Schema | Name | Type | Owner | Storage
--------+-------------------------------+---------------+-------+---------
U_A | F_A | foreign table | U_A |
public | F_B | foreign table | omm |
public | pg_type_nonstrict_basic_value | view | omm |
public | pw_login_info | view | omm |
panweidb=> select * from F_A;
id | name
----+------
1 | fw
(1 row)
三、让U_B用户访问U_A下的表
1.创建用户U_B
panweidb=# CREATE USER U_B PASSWORD 'panwei$123';
2.用U_A用户给U_B用户授权
panweidb=> GRANT USAGE ON SCHEMA U_A TO U_B;
panweidb=> GRANT SELECT ON ALL TABLES IN SCHEMA U_A TO U_B;
2.切换到U_B用户连接数据库并尝试访问U_A.F_A表
--授完权后，可以看看U_A下的表结构
panweidb=> \d U_A.*
Foreign table "U_A.F_A"
Column | Type | Modifiers | FDW Options | Attalias
--------+-------------+-----------+-------------+----------
id | integer | | |
name | varchar(10) | | |
Server: u_server
FDW Options: (schema_name 'extuser1', table_name 'extt1')
FDW permition: write only
--但是访问表数据时会提示U_B也需要创建映射
panweidb=> select * from U_A.F_A;
ERROR: user mapping not found for "U_B"
根据提示建立user mapping.那user mapping需要在哪一个用户下建立，还是都一样呢？
1）先尝试在U_B用户下建立user mapping
panweidb=> CREATE USER MAPPING FOR USER SERVER U_SERVER OPTIONS (user 'extuser1', password 'panwei#123');
ERROR: server "u_server" does not exist
但是在U_B下创建user mapping时会提示U_SERVER不存在，那我们先到U_A下创建测试看看
2）在U_A下为U_B创建映射
panweidb=> CREATE USER MAPPING FOR U_B SERVER U_SERVER OPTIONS (user 'extuser1', password 'panwei#123');
CREATE USER MAPPING
panweidb=> select * from pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+-------------+--------+------------+-----------------------------------------------------------------------------------------------------
20718 | 20717 | fore_server | 10 | omm | {user=u1,password=encryptOptD5SGGRWOLjRHtWoKdB3y6O/9vjCnxsSnPHwbgqKa0EWM0HVNX3XzBDI9GaMcgnvD}
20736 | 20734 | test_server | 20730 | loacluser1 | {user=extuser1,password=encryptOpt4DegfR/awOY9CyqD77Jgg9NE8lS9TvsN62CCcNkFIZNQb7qJigbNjfxr1aUbnhIR}
20747 | 20744 | fore_server | 20725 | u1 | {user=u1,password=encryptOptXMRNNzLTd0a+eaMPN5RJMuRp8/Qlnk2z2YRGogM2GPVXAZym3OvAzyEiebS5arzE}
20748 | 20744 | fore_server | 20740 | u2 | {user=u1,password=encryptOptPAAWy1w4GPwTZZEa2ZQEJxzStdBH0XRFc68F9tk3p0dLHdumXMjWRSlW9rhleBuh}
20749 | 20717 | fore_server | 20725 | u1 | {user=u1,password=encryptOptciJH8dveiSspVR9TWTV8GN1gQvQayqGqhXokXG0XIubtBXzuIa59lB1o6DyZix3W}
20756 | 20717 | fore_server | 20740 | u2 | {user=u1,password=encryptOptcJzwV2kitpvaf+zY2dcxDe8A9APn8Ec81kZxcF1igYiA9vfedyIuEWul5fkzl4el}
20762 | 20761 | u_server | 20757 | U_A | {user=extuser1,password=encryptOptH/v9WtG7Ocb8mJokILIcLoZcXk1fQhUN+5oxIzUUrb5wCyC3YtmsiQZOIUICA0Dv}
20770 | 20761 | u_server | 20766 | U_B | {user=extuser1,password=encryptOptcC0FiibY2iUIY7l9/sM9fu2k93oPVuLGtint4x/4dnQFc7muaVEKqNwX+szWhUAN}
(8 rows)
再次尝试访问表U_A.F_A,还是会提示u_server服务器不存在。
panweidb=> select * from U_A.F_A;
ERROR: server "u_server" does not exist
--在U_B下可以查看到u_server,但是不知道它属主
panweidb=> \des
List of foreign servers
Name | Owner | Foreign-data wrapper
----------------+-------+----------------------
fore_server | | postgres_fdw
fore_server | | postgres_fdw
pwaudit_server | | file_fdw
test_server | | postgres_fdw
u_server | | postgres_fdw
(5 rows)
panweidb=> select * from pg_catalog.pg_foreign_server;
srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
----------------+----------+--------+---------+------------+--------+--------------------------------------------------
pwaudit_server | 0 | 19820 | | | |
fore_server | 20725 | 20716 | | | | {host=192.168.110.91,port=17700,dbname=panweidb}
test_server | 20730 | 20716 | | | | {host=192.168.110.91,port=17700,dbname=panweidb}
fore_server | 20740 | 20716 | | | | {host=192.168.110.91,port=17700,dbname=panweidb}
u_server | 20757 | 20716 | | | | {host=192.168.110.91,port=17700,dbname=panweidb}
(5 rows)
在磐维数据库中，只有外部服务器的owner才能访问到外部服务器。
此时我们可以在U_B下创建一个同名的外部服务器，但是U_B用户并没有sysadmin权限，我们要怎么创建外部服务器呢？
我们可以先用具有sysadmin权限的用户创建一个server,再通过修改owner方式让U_B拥有一个同名的外部服务器。
omm用户下：
panweidb=# CREATE SERVER U_SERVER FOREIGN DATA WRAPPER POSTGRES_FDW OPTIONS(host '192.168.110.91',port '17700',dbname 'panweidb');
CREATE SERVER
panweidb=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
----------------+------------+----------------------
fore_server | u1 | postgres_fdw
fore_server | u2 | postgres_fdw
pwaudit_server | | file_fdw
test_server | loacluser1 | postgres_fdw
u_server | U_A | postgres_fdw
u_server | omm | postgres_fdw
panweidb=# ALTER SERVER U_SERVER OWNER TO U_B;
ALTER SERVER
panweidb=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
----------------+------------+----------------------
fore_server | u1 | postgres_fdw
fore_server | u2 | postgres_fdw
pwaudit_server | | file_fdw
test_server | loacluser1 | postgres_fdw
u_server | U_A | postgres_fdw
u_server | U_B | postgres_fdw
此时在切换到U_B下，查看外部服务器及访问外表U_A.F_A
panweidb=> \des
List of foreign servers
Name | Owner | Foreign-data wrapper
----------------+-------+----------------------
fore_server | | postgres_fdw
fore_server | | postgres_fdw
pwaudit_server | | file_fdw
test_server | | postgres_fdw
u_server | | postgres_fdw
u_server | U_B | postgres_fdw
panweidb=> select * from U_A.F_A;
id | name
----+------
1 | fw
(1 row)
至此，U_B用户成功访问U_A下的外表F_A.
给大家留个思考：
在上面如果我们先在U_B下创建完外部服务器，然后在U_B下创建为U_B创建user mapping是否能让U_B访问到U_A的外表呢？
四、总结
U_B访问U_A下的外部表，需要具备哪些步骤：
1）在U_B上创建同名的服务器，这时才能访问到U_A的外部服务器
2）需要在U_A下创建U_B到外部服务器的user mapping
3) 给U_B 访问U_A的schema权限和表权限
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
