本文簡(jiǎn)單介紹了PostgreSQL的權(quán)限和用戶管理基礎(chǔ)知識(shí),原文詳見(jiàn) PostgreSQL Privileges & User Management - What You Should Know ,有所刪減和調(diào)整.

10年積累的成都網(wǎng)站建設(shè)、成都網(wǎng)站制作經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問(wèn)題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先網(wǎng)站設(shè)計(jì)后付款的網(wǎng)站建設(shè)流程，更有譙城免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

Roles
PostgreSQL使用基于角色的權(quán)限管理系統(tǒng).
PostgreSQL中的用戶user和角色role是一回事,區(qū)別是在創(chuàng)建用戶時(shí)具備了LOGIN權(quán)限而角色沒(méi)有,因此以下不再提及用戶均以角色描述.

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

在創(chuàng)建角色時(shí),以下權(quán)限是常用的選項(xiàng):
SUPERUSER - 超級(jí)用戶,SUPERUSER可創(chuàng)建新的SUPERUSER,SUPERUSER可跳過(guò)所有的權(quán)限檢查.
CREATEDB - 可創(chuàng)建databases.
CREATEROLE - 可創(chuàng)建其他角色.
LOGIN - 可登錄.

事實(shí)上,如果沒(méi)有LOGIN權(quán)限,那么就算是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
配置服務(wù)器與客戶端之間的連接,查詢pg_setting視圖可檢索當(dāng)前的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文件的條目形如以下的設(shè)置

local database user address auth-method [auth-options]

其中:
第一項(xiàng)是指連接方式,local是Unix-domain sockets,host是TCP/IP連接
第二項(xiàng)是數(shù)據(jù)庫(kù),all表示所有
第三項(xiàng)是用戶,all表示所有
第四項(xiàng)是地址,如192.168.0.0/16
第五項(xiàng)auth-method是認(rèn)證方法,包括trust,reject,scram-sha-256,md5,password,gss,sspi,ident,peer,ldap,radius,cert,pam,bsd.詳見(jiàn)的,trust表示不需要password,password表示明文密碼,md5表示使用md5加密密碼傳輸?shù)?p>通過(guò)查詢pg_hba_file_rules視圖可查看當(dāng)前的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文件后,可通過(guò)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一旦創(chuàng)建,具備LOGIN權(quán)限,并且在hba中配置可以訪問(wèn)數(shù)據(jù)庫(kù),那么就具備了操縱數(shù)據(jù)庫(kù)的權(quán)限包括創(chuàng)建數(shù)據(jù)表/插入數(shù)據(jù)等DDL/DML的權(quán)限,但如果需要訪問(wèn)其他owner創(chuàng)建的對(duì)象,則需要授權(quán).
比如用戶pg12創(chuàng)建了數(shù)據(jù)表t1,但沒(méi)有授權(quán)給demouser,雖然demouser可以訪問(wèn)t1,但無(wú)法查詢

[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為了實(shí)現(xiàn)精細(xì)化的權(quán)限管理,提供了列級(jí)的訪問(wèn)授權(quán),其GRANT語(yǔ)句語(yǔ)法如下,其中column_name部分可指定列權(quán)限:

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

指定t1.id可以給demouser訪問(wèn):

testdb=# GRANT SELECT (id) ON TABLE t1 TO demouser;
GRANT

demouser可以訪問(wèn)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
CREATE ROLE