postgresql 11 的默认角色

Java 发表评论


os: centos 7.4
db: postgresql 11.1

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# su - postgres -c "psql -c \"select version();\""
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

默认角色

postgres=# select * from pg_roles order by oid;
          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid  
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------
 postgres                  | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |   10
 pg_monitor                | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 3373
 pg_read_all_settings      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 3374
 pg_read_all_stats         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 3375
 pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 3377
 pg_signal_backend         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4200
 pg_read_server_files      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4569
 pg_write_server_files     | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4570
 pg_execute_server_program | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4571
(9 rows)

postgres=# \duS+
                                                                            List of roles
         Role name         |                         Attributes                         |                          Member of                           | Description 
---------------------------+------------------------------------------------------------+--------------------------------------------------------------+-------------
 pg_execute_server_program | Cannot login                                               | {}                                                           | 
 pg_monitor                | Cannot login                                               | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} | 
 pg_read_all_settings      | Cannot login                                               | {}                                                           | 
 pg_read_all_stats         | Cannot login                                               | {}                                                           | 
 pg_read_server_files      | Cannot login                                               | {}                                                           | 
 pg_signal_backend         | Cannot login                                               | {}                                                           | 
 pg_stat_scan_tables       | Cannot login                                               | {}                                                           | 
 pg_write_server_files     | Cannot login                                               | {}                                                           | 
 postgres                  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}                                                           | 

postgres=# \dgS+
                                                                            List of roles
         Role name         |                         Attributes                         |                          Member of                           | Description 
---------------------------+------------------------------------------------------------+--------------------------------------------------------------+-------------
 pg_execute_server_program | Cannot login                                               | {}                                                           | 
 pg_monitor                | Cannot login                                               | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} | 
 pg_read_all_settings      | Cannot login                                               | {}                                                           | 
 pg_read_all_stats         | Cannot login                                               | {}                                                           | 
 pg_read_server_files      | Cannot login                                               | {}                                                           | 
 pg_signal_backend         | Cannot login                                               | {}                                                           | 
 pg_stat_scan_tables       | Cannot login                                               | {}                                                           | 
 pg_write_server_files     | Cannot login                                               | {}                                                           | 
 postgres                  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}                                                           | 

postgres=# 

简单说明下各角色的作用
postgres 这个角色的 rolcanlogin 为 true,说明已经是可以登录的用户,且是超级用户。
pg_signal_backend 这个角色表示可以给其他后端发送信号(比如: 取消查询、终止)。

pg_monitor 读取/执行各种监视视图和函数。 此角色是pg_read_all_settings、 pg_read_all_stats和 pg_stat_scan_tables的成员。
pg_read_all_settings 阅读所有配置变量,即使那些通常只对超级用户可见的配置变量。
pg_read_all_stats 阅读所有pg_stat_*视图并使用各种统计相关的扩展,甚至那些通常只对超级用户可见的扩展。
pg_stat_scan_tables 执行可能对表进行可能需要很长时间ACCESS SHARE锁定的监视功能。

下面是 postgresql 11 新加的角色:

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

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

pg_execute_server_program 具有执行数据库服务端的程序权限

新增加的三个角色涉及到读写数据库服务端文件,权限较大,分配权限时三思后授权。

授予默认角色

postgres=# create user peiyb with login password 'rootroot';
CREATE ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 peiyb     |                                                            | {}        | 
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 

postgres=# 
postgres=# create database peiybdb owner =  peiyb;
CREATE DATABASE
postgres=# \q

$ psql -h 192.168.56.111 -U peiyb peiybdb
Password for user peiyb: 
psql (11.1)
Type "help" for help.

peiybdb=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 peiyb     |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

peiybdb=> create table tmp_t0(c0 varchar(100),c1 varchar(100));
CREATE TABLE
peiybdb=> insert into tmp_t0(c0,c1) select id::varchar,md5(id::varchar) from generate_series(1,10000) as id;
INSERT 0 10000
peiybdb=> copy tmp_t0 to '/tmp/tmp_t0.data';
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.
peiybdb=> 

提示 需要授权 pg_write_server_files,其实这样的提示非常人性,提示缺少什么权限。直接授权即可

postgres=# grant pg_write_server_files to peiyb;
GRANT ROLE

再次执行导出

peiybdb=> copy tmp_t0 to '/tmp/tmp_t0.data';
COPY 10000

两外两个角色也是在缺少时会明确提示。

参考:
https://www.postgresql.org/docs/11/default-roles.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注

昵称 *