本文简单介绍了PostgreSQL的权限和用户管理基础知识,原文详见[PostgreSQL Privileges & User Management - What You Should Know](https://severalnines.com/blog/postgresql-privileges-user-management-what-you-should-know),有所删减和调整. **Roles** PostgreSQL使用基于角色的权限管理系统. PostgreSQL中的用户user和角色role是一回事,区别是在创建用户时具备了LOGIN权限而角色没有,因此以下不再提及用户均以角色描述. ``` testdb=# create role testrole with password 'test'; CREATE ROLE testdb=# create user testuser with password 'test'; CREATE ROLE ``` 退出psql,分别以testrole和testuser登录 ``` testdb=# \q [pg12@localhost ~]$ psql -U testrole -d testdb psql: error: could not connect to server: FATAL: role "testrole" is not permitted to log in [pg12@localhost ~]$ psql -U testuser -d testdb psql (12beta1) Type "help" for help. testdb=> ``` 在创建角色时,以下权限是常用的选项: SUPERUSER - 超级用户,SUPERUSER可创建新的SUPERUSER,SUPERUSER可跳过所有的权限检查. CREATEDB - 可创建databases. CREATEROLE - 可创建其他角色. LOGIN - 可登录. 事实上,如果没有LOGIN权限,那么就算是SUPERUSER也登录不了 ``` testdb=# create role user1 with password 'test' SUPERUSER CREATEROLE NOLOGIN; CREATE ROLE testdb=# \q [pg12@localhost ~]$ psql -U user1 -d testdb psql: error: could not connect to server: FATAL: role "user1" is not permitted to log in [pg12@localhost ~]$ ``` 在psql下,使用\du命令可查看角色信息 ``` testdb=# \du List of roles Role name | Attributes | Member of ------------+------------------------------------------------------------+----------- pg12 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} replicator | Replication | {} testrole | Cannot login | {} testuser | | {} user1 | Superuser, Create role, Cannot login | {} Informational (options: S = show system objects, + = additional detail) ... \du[S+] [PATTERN] list roles ... ``` **pg_hba.conf** 配置服务器与客户端之间的连接,查询pg_setting视图可检索当前的hba文件在什么地方 ``` testdb=# SELECT name, setting testdb-# FROM pg_settings WHERE name LIKE '%hba%'; name | setting ----------+--------------------------------- hba_file | /data/pgsql/pg12db1/pg_hba.conf (1 row) ``` hba文件的条目形如以下的设置 ``` local database user address auth-method [auth-options] ``` 其中: 第一项是指连接方式,local是Unix-domain sockets,host是TCP/IP连接 第二项是数据库,all表示所有 第三项是用户,all表示所有 第四项是地址,如192.168.0.0/16 第五项auth-method是认证方法,包括trust,reject,scram-sha-256,md5,password,gss,sspi,ident,peer,ldap,radius,cert,pam,bsd.详见的,trust表示不需要password,password表示明文密码,md5表示使用md5加密密码传输等 通过查询pg_hba_file_rules视图可查看当前的hba配置 ``` testdb=# SELECT * FROM pg_hba_file_rules; line_number | type | database | user_name | address | netmask | auth_method | options | error -------------+-------+---------------+-----------+---------------+-----------------------------------------+-------------+---------+------- 84 | local | {all} | {all} | | | trust | | 86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 87 | host | {all} | {all} | 192.168.0.0 | 255.255.0.0 | md5 | | 89 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | 92 | local | {replication} | {all} | | | trust | | 93 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 94 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | 95 | host | {replication} | {all} | 192.168.26.26 | 255.255.255.255 | trust | | 96 | host | {replication} | {all} | 192.168.26.27 | 255.255.255.255 | trust | | 97 | host | {replication} | {all} | 192.168.26.29 | 255.255.255.255 | trust | | (10 rows) ``` 修改pg_hba.conf文件后,可通过pg_ctl reload命令刷新配置文件到pg_hba_file_rules中. 比如删除line_number = 97的条目,刷新 ``` host replication all 192.168.26.26/32 trust host replication all 192.168.26.27/32 trust ~ :x [pg12@localhost pg12db1]$ pg_ctl reload server signaled testdb=# SELECT * FROM pg_hba_file_rules; line_number | type | database | user_name | address | netmask | auth_method | options | error -------------+-------+---------------+-----------+---------------+-----------------------------------------+-------------+---------+------- 84 | local | {all} | {all} | | | trust | | 86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 87 | host | {all} | {all} | 192.168.0.0 | 255.255.0.0 | md5 | | 89 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | 92 | local | {replication} | {all} | | | trust | | 93 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 94 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | 95 | host | {replication} | {all} | 192.168.26.26 | 255.255.255.255 | trust | | 96 | host | {replication} | {all} | 192.168.26.27 | 255.255.255.255 | trust | | (9 rows) ``` **Database, Table, and Column level privileges** Role一旦创建,具备LOGIN权限,并且在hba中配置可以访问数据库,那么就具备了操纵数据库的权限包括创建数据表/插入数据等DDL/DML的权限,但如果需要访问其他owner创建的对象,则需要授权. 比如用户pg12创建了数据表t1,但没有授权给demouser,虽然demouser可以访问t1,但无法查询 ``` [pg12@localhost ~]$ psql -h 192.168.26.28 -U demouser -d testdb Password for user demouser: psql (12beta1) Type "help" for help. testdb=> create table t2(id int); CREATE TABLE testdb=> drop table t2; DROP TABLE testdb=> \d+ t1 Table "public.t1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | | | plain | | c1 | integer | | | | plain | | c2 | integer | | | | plain | | Access method: heap testdb=> select * from t1; psql: ERROR: permission denied for table t1 ``` 另外,PostgreSQL为了实现精细化的权限管理,提供了列级的访问授权,其GRANT语句语法如下,其中column_name部分可指定列权限: ``` GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) } ON [ TABLE ] table_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] ``` 指定t1.id可以给demouser访问: ``` testdb=# GRANT SELECT (id) ON TABLE t1 TO demouser; GRANT ``` demouser可以访问id列 ``` testdb=> select * from t1; psql: ERROR: permission denied for table t1 testdb=> select id from t1; id ---- (0 rows) ``` **参考资料** [PostgreSQL Privileges & User Management - What You Should Know](https://severalnines.com/blog/postgresql-privileges-user-management-what-you-should-know) [CREATE ROLE](https://www.postgresql.org/docs/11/sql-createrole.html)