【实战干货】磐维数据库_外表访问权限

原创磐维数据库 2025-02-27

一、场景说明

用户U_A创建了外部服务器server_A,创建了表T_A,现在用户U_B要访问外表T_A需要哪一些权限？

二、场景准备

1.创建用户U_A

db_name=# CREATE USER U_A PASSWORD 'xxx@pwd';
db_name=# ALTER USER U_A SYSADMIN;
db_name=# \du U_A
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 U_A       | Sysadmin   | {}复制

2.切换到U_A下创建外部服务器

[omm@panwei203 ~]$ gsql -d db_name -U U_A
db_name=> CREATE SERVER U_SERVER FOREIGN DATA WRAPPER POSTGRES_FDW OPTIONS(host 'xxx.xxx.xxx.xxx',port '12345',dbname 'db_name');
CREATE SERVER复制

--查看外部服务器

\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复制

select * from pg_catalog.pg_foreign_server;
db_name=> select * from pg_catalog.pg_foreign_server;
    srvname     | srvowner | srvfdw | srvtype | srvversion | srvacl |                    srvoptions
----------------+----------+--------+---------+------------+--------+--------------------------------------------------
 pwaudit_server |        0 |  19820 |         |            |        |
 fore_server    |    20725 |  20716 |         |            |        | {host=xxx.xxx.xxx.xxx,port=12345,dbname=db_name}
 test_server    |    20730 |  20716 |         |            |        | {host=xxx.xxx.xxx.xxx,port=12345,dbname=db_name}
 fore_server    |    20740 |  20716 |         |            |        | {host=xxx.xxx.xxx.xxx,port=12345,dbname=db_name}
 u_server       |    20757 |  20716 |         |            |        | {host=xxx.xxx.xxx.xxx,port=12345,dbname=db_name}复制

需要sysadmin权限才能创建外部服务器，否则会提示权限不足。

db_name=> CREATE SERVER U_SERVER FOREIGN DATA WRAPPER POSTGRES_FDW OPTIONS(host 'xxx.xxx.xxx.xxx',port '12345',dbname 'db_name');
ERROR:  permission denied for foreign-data wrapper postgres_fdw
DETAIL:  N/A复制

3.创建user mapping

user mapping是创建本地服务器的用户映射到外部服务器用户，外部服务器用户需要具有所需访问表的权限。

db_name=> CREATE USER MAPPING FOR USER SERVER U_SERVER OPTIONS (user 'xxx_user', password 'xxx_pwd');
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=密码密文}
 20736 | 20734 | test_server |  20730 | loacluser1 | {user=xxx_user,password=密码密文}
 20747 | 20744 | fore_server |  20725 | u1         | {user=u1,password=密码密文}
 20748 | 20744 | fore_server |  20740 | u2         | {user=u1,password=密码密文}
 20749 | 20717 | fore_server |  20725 | u1         | {user=u1,password=密码密文}
 20756 | 20717 | fore_server |  20740 | u2         | {user=u1,password=密码密文}
 20762 | 20761 | u_server    |  20757 | U_A        | {user=xxx_user,password=密码密文}复制

4.创建外表

说明：创建外表的用户需要映射到外部服务器的用户才能建表

db_name=> CREATE FOREIGN TABLE F_A(
id INT,
name VARCHAR(10)
) SERVER U_SERVER OPTIONS (SCHEMA_NAME 'xxx_user',TABLE_NAME 'extt1');
CREATE FOREIGN TABLE复制

--查看表

db_name=> \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   |

db_name=> select * from F_A;
 id | name
----+------
  1 | fw
(1 row)复制

三、让U_B用户访问U_A下的表

1.创建用户U_B

db_name=# CREATE USER U_B PASSWORD 'xxx@pwd';复制

2.用U_A用户给U_B用户授权

db_name=>  GRANT USAGE ON SCHEMA  U_A TO U_B;
db_name=> GRANT SELECT ON ALL TABLES IN SCHEMA U_A TO U_B;复制

3.切换到U_B用户连接数据库并尝试访问U_A.F_A表

--授完权后，可以看看U_A下的表结构

db_name=> \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 'xxx_user', table_name 'extt1')
FDW permition: write only复制

--但是访问表数据时会提示U_B也需要创建映射

db_name=> select * from U_A.F_A;
ERROR:  user mapping not found for "U_B"复制

根据提示建立user mapping.那user mapping需要在哪一个用户下建立，还是都一样呢？

1）先尝试在U_B用户下建立user mapping

db_name=> CREATE USER MAPPING FOR USER SERVER U_SERVER OPTIONS (user 'xxx_user', password 'xxx_pwd');
ERROR:  server "u_server" does not exist复制

但是在U_B下创建user mapping时会提示U_SERVER不存在，那我们先到U_A下创建测试看看

2）在U_A下为U_B创建映射

db_name=> CREATE USER MAPPING FOR U_B SERVER U_SERVER OPTIONS (user 'xxx_user', password 'xxx_pwd');
CREATE USER MAPPING
db_name=> select * from pg_user_mappings;
 umid  | srvid |   srvname   | umuser |  usename   |                                              umoptions
-------+-------+-------------+--------+------------+-----------------------------------------------------------------------------------------------------
 20718 | 20717 | fore_server |     10 | omm        | {user=u1,password=密码密文}
 20736 | 20734 | test_server |  20730 | loacluser1 | {user=xxx_user,password=密码密文}
 20747 | 20744 | fore_server |  20725 | u1         | {user=u1,password=密码密文}
 20748 | 20744 | fore_server |  20740 | u2         | {user=u1,password=密码密文}
 20749 | 20717 | fore_server |  20725 | u1         | {user=u1,password=密码密文}
 20756 | 20717 | fore_server |  20740 | u2         | {user=u1,password=密码密文}
 20762 | 20761 | u_server    |  20757 | U_A        | {user=xxx_user,password=密码密文}
 20770 | 20761 | u_server    |  20766 | U_B        | {user=xxx_user,password=密码密文}
(8 rows)复制

再次尝试访问表U_A.F_A,还是会提示u_server服务器不存在。

db_name=> select * from U_A.F_A;
ERROR:  server "u_server" does not exist复制

--在U_B下可以查看到u_server,但是不知道它属主

db_name=> \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)复制

db_name=> select * from pg_catalog.pg_foreign_server;
    srvname     | srvowner | srvfdw | srvtype | srvversion | srvacl |                    srvoptions
----------------+----------+--------+---------+------------+--------+--------------------------------------------------
 pwaudit_server |        0 |  19820 |         |            |        |
 fore_server    |    20725 |  20716 |         |            |        | {host=xxx.xxx.xxx.xxx,port=12345,dbname=db_name}
 test_server    |    20730 |  20716 |         |            |        | {host=xxx.xxx.xxx.xxx,port=12345,dbname=db_name}
 fore_server    |    20740 |  20716 |         |            |        | {host=xxx.xxx.xxx.xxx,port=12345,dbname=db_name}
 u_server       |    20757 |  20716 |         |            |        | {host=xxx.xxx.xxx.xxx,port=12345,dbname=db_name}
(5 rows)复制

在磐维数据库中，只有外部服务器的owner才能访问到外部服务器。

此时我们可以在U_B下创建一个同名的外部服务器，但是U_B用户并没有sysadmin权限，我们要怎么创建外部服务器呢？

我们可以先用具有sysadmin权限的用户创建一个server,再通过修改owner方式让U_B拥有一个同名的外部服务器。

omm用户下：

db_name=# CREATE SERVER U_SERVER FOREIGN DATA WRAPPER POSTGRES_FDW OPTIONS(host 'xxx.xxx.xxx.xxx',port '12345',dbname 'db_name');
CREATE SERVER
db_name=# \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
db_name=# ALTER SERVER U_SERVER OWNER TO U_B;
ALTER SERVER
db_name=# \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

db_name=> \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


db_name=> 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权限和表权限

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

wzf0072

29天前

一、场景说明用户U_A创建了外部服务器server_A,创建了表T_A,现在用户U_B要访问外表T_A需要哪一些权限？二、场景准备 1.创建用户U_A db_name=# CREATE USER U_A PASSWORD 'xxx@pwd'; db_name=# AL