PostgreSQL11: 新增三个默认角色

PostgreSQL 11 新增三个默认系统角色,如下:

  • pg_read_server_files
  • pg_write_server_files
  • pg_execute_server_program

这三个角色主要涉及数据库服务端文件的读写权限,例如使用copy命令或file_fdw模块读写数据库端文件的权限。

这些权限之前版本只有超级用户才具备,这三个默认角色的出现,使得可以将数据库服务端的文件的访问权限(目前仅包含copy命令或file_fdw模块)下放给普通用户。

Release说明

Add default roles which control file system access (Stephen Frost)
Specifically, the new roles are: pg_read_server_files, pg_write_server_files, pg_execute_server_program. These roles now also control who can use COPY and extension file_fdw. Previously only superusers could use these functions, and that is still the default behavior.

手册中说明很清楚,下面演示这三种角色的权限。

pg_read_server_files

pg_read_server_files 角色具有数据库服务端文件的读权限,例如使用copy命令或file_fdw模块读数据库端文件的权限。

在数据库主机 pghost2 家目录创建 t_copy.txt 文件并写入两行数据,如下:

1
2
1       a
2 b

以 francs 用户登录数据库 francs 创建测试表 t_copy 如下:

1
2
3
4
5
6
[pg11@pghost2 ~]$ psql francs francs
psql (11beta3)
Type "help" for help.

francs=> CREATE TABLE t_copy(id int4, name text);
CREATE TABLE

创建 role11 用户,如下

1
2
postgres=# CREATE ROLE role11 NOSUPERUSER PASSWORD 'role11' LOGIN;
CREATE ROLE

以 role11 用户登录到 francs 数据库,执行 copy 命令,尝试将数据库服务端文件 t_copy.txt 文件的数据加载到表 t_copy 中,如下:

1
2
3
4
5
6
7
[pg11@pghost2 ~]$ psql francs role11
psql (11beta3)
Type "help" for help.

francs=> COPY t_copy FROM '/home/pg11/t_copy.txt';
ERROR: must be superuser or a member of the pg_read_server_files role to COPY from a file
HINT: Anyone can COPY to stdout or from stdin. psql s \copy command also works for anyone.

以上报错,提示需要超级用户或具有pg_read_server_files权限才能使用 COPY 命令读取数据库服务端文件。

给 role11 用户赋 pg_read_server_files 角色权限,如下:

1
2
3
4
5
6
7
8
9
10
11
francs=> \c francs postgres
You are now connected to database "francs" as user "postgres".

francs=# GRANT pg_read_server_files TO role11;
GRANT ROLE

francs=# GRANT USAGE ON SCHEMA francs TO role11;
GRANT

francs=# GRANT INSERT ON francs.t_copy TO role11;
GRANT

francs库中创建了模式 francs ,因此也需要将模式的使用权限赋给 role11,否则访问表时会报没有使用模式权限的错误;之后再赋予表的写权限。

再次测试成功,如下。

1
2
3
4
5
6
[pg11@pghost2 ~]$ psql francs role11
psql (11beta3)
Type "help" for help.

francs=> COPY francs.t_copy FROM '/home/pg11/t_copy.txt';
COPY 2

pg_write_server_files

pg_write_server_files 角色具有数据库服务端文件的写权限,例如使用copy命令或file_fdw模块写数据库端文件的权限,接着演示。

以 role11 用户登录数据库 francs ,尝试导出表数据到数据库服务端。

1
2
3
4
5
6
7
[pg11@pghost2 ~]$ psql francs role11;
psql (11beta3)
Type "help" for help.

francs=> COPY francs.t_copy TO '/home/pg11/t_copy2.txt';
ERROR: must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT: Anyone can COPY to stdout or from stdin. psql s \copy command also works for anyone.

赋权如下:

1
2
3
4
5
6
7
8
9
[pg11@pghost2 ~]$ psql francs postgres
psql (11beta3)
Type "help" for help.

francs=# GRANT pg_write_server_files TO role11;
GRANT ROLE

francs=# GRANT SELECT ON francs.t_copy TO role11;
GRANT

再次测试成功,如下

1
2
3
4
5
6
7
8
9
10
[pg11@pghost2 ~]$ psql francs role11;
psql (11beta3)
Type "help" for help.

francs=> COPY francs.t_copy TO '/home/pg11/t_copy2.txt';
COPY 2

francs=> \! cat '/home/pg11/t_copy2.txt'
1 a
2 b

可见,已将数据导出到数据库服务端上的文件。

pg_execute_server_program

pg_execute_server_program 角色具有执行数据库服务端的程序权限,以file_fdw外部表举例如下。

首先准备数据文件,将 t_copy 文件进行压缩,如下:

1
2
3
4
5
[pg11@pghost2 ~]$ cat t_copy.txt 
1 a
2 b

[pg11@pghost2 ~]$ gzip t_copy.txt

创建 file_fdw 外部扩展和外部表,以超级用户postgres登录francs库,如下:

1
2
3
4
5
6
7
8
9
10
11
12
[pg11@pghost2 ~]$ psql francs postgres
psql (11beta3)
Type "help" for help.

francs=# CREATE EXTENSION file_fdw;
CREATE EXTENSION

francs=# CREATE SERVER srv_file FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER

francs=# GRANT USAGE ON FOREIGN SERVER srv_file TO role11;
GRANT

以普通用户role11登录francs库,创建带OPTIONS(program)选项的外部表,如下:

1
2
3
4
5
6
[pg11@pghost2 ~]$ psql francs role11
psql (11beta3)
Type "help" for help.

francs=> CREATE FOREIGN TABLE ft_t_copy(id int4,name text) SERVER srv_file OPTIONS(program 'gunzip < /home/pg11/t_copy.txt.gz');
ERROR: only superuser or a member of the pg_execute_server_program role may specify the program option of a file_fdw foreign table

以上报错,提示需要 superuser 或 pg_execute_server_program 权限才有权限指定 file_fdw 外部表的 program 选项。

将 pg_execute_server_program 角色赋予 role11用户,注意以下以postgres超级用户执行。

1
2
francs=# GRANT pg_execute_server_program TO role11;
GRANT ROLE

再次以role11用户登录francs库测试,如下:

1
2
3
4
5
6
7
8
9
francs=> CREATE FOREIGN TABLE ft_t_copy(id int4,name text) SERVER srv_file OPTIONS(program 'gunzip < /home/pg11/t_copy.txt.gz');
CREATE FOREIGN TABLE

francs=> SELECT * FROM ft_t_copy ;
id | name
----+------
1 | a
2 | b
(2 rows)

创建带带OPTIONS(program)选项的外部表成功。

总结

pg_read_server_files、pg_write_server_files、pg_execute_server_program 角色涉及到读写数据库服务端文件,权限较大,分配此角色权限给数据库用户时需谨慎考虑。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%