本文簡單介紹了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=> 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)

PostgreSQL DBA(44) - Privileges & User Management - What You Should Know

相關文章