Postgresql 數據庫權限功能的使用總結

Posted on by

Postgresql數據庫支持靈活的權限管理，可以控制一個角色（組、用戶）對某張表的讀、寫、更新、刪除等操作權限、執行某個函數的權限以及操作（使用、更新等）視圖、序列的權限。

PG的權限管理功能比較強大，可以細化到對一張表的各個字段，比如禁止用戶訪問一張表裡的密碼字段等，在稍後的內容中給出詳細的解釋。

雖然在PG數據庫中把用戶、角色統一叫做角色，甚至創建語句都為create role XXX,但用戶和角色之間仍有一定的區別。在這裡我們統一把擁有登錄權限的叫做用戶，沒有登錄權限的叫做角色，用此方式加以區分。

實際上，在PgAdmin管理工具中，可以看到用戶和角色的區別，沒有登錄權限的被放在組角色下，有登錄權限的被放在登錄角色下。

基本權限

用戶和角色都可以被賦予基本權限，比如創建數據庫權限、超級用戶權限、創建角色權限等。

比如創建用戶的語句為：

CREATE ROLE guest LOGIN
 NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

註意上述Role guest擁有LOGIN的權限，所以叫它用戶。

創建角色的語句為：

CREATE ROLE "group"
 NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

註意這裡沒有LOGIN權限，所以是角色。

上述角色和用戶的創建語句中，都沒有賦予超級用戶、創建數據庫等權限。

操作數據庫對象權限

隻能把數據庫對象的操作權限賦予沒有登錄權限的角色，而不能直接賦予擁有登錄權限的用戶。

那麼這樣就帶來一個問題，怎麼樣控制登錄用戶操作數據庫對象的權限呢？

答案是讓用戶成為角色的成員，此時用戶即可擁有角色的權限，進一步限制瞭登錄用戶操作數據庫對象的權限。

如把上述角色group賦予guest用戶：

GRANT "group" TO guest;

之後，guest用戶就擁有瞭group角色所擁有的數據庫對象權限。

比如控制group角色隻能對class表執行Insert操作：

GRANT INSERT ON TABLE class TO "group";

此時使用guest用戶登錄數據後，就隻能對表class執行insert操作，無法執行delete、update等操作。

示例代碼如下，使用guest用戶登錄，訪問TEST數據庫下的class表。

Server [localhost]:
Database [postgres]:
Port [5433]:
Username [postgres]: guest
用戶 guest 的口令：
psql (9.4.5)
輸入 "help" 來獲取幫助信息.
postgres=> \c TEST
您現在已經連線到數據庫 "TEST",用戶 "guest".
TEST=> select * from class;
ERROR: permission denied for relation class
TEST=> insert into class values(2,'class1');
INSERT 0 1

從上述結果中可以看到，guest用戶沒有權限查詢class表，但是可以插入數據庫。原因就是隻對group角色賦予瞭class表的insert權限，然後guest用戶也就隻有class表的insert權限。

前面說到PG的權限管理可以細化到表的某個字段，現在繼續用class表和guest用戶做實驗。

TEST=> \c postgres postgres;
您現在已經連線到數據庫 "postgres",用戶 "postgres".
postgres=# \c TEST;
您現在已經連線到數據庫 "TEST",用戶 "postgres".
TEST=# grant select(num) on class to "group";
GRANT
TEST=# \echo 切換到postgres用戶連接TEST數據庫，對class表的num字段的select權限賦予group角色
切換到postgres用戶連接TEST數據庫，對class表的num字段的select權限賦予group角色
TEST=# \c TEST guest
用戶 guest 的口令：
您現在已經連線到數據庫 "TEST",用戶 "guest".
TEST=> \echo 切換回guest用戶登錄TEST數據庫
切換回guest用戶登錄TEST數據庫
TEST=> select * from class;
ERROR: permission denied for relation class
TEST=> select num from class;
 num
-----
  1
  2
(2 行記錄)

從上述結果中可以看到，guest用戶依然沒有查詢class表的權限，但是卻有瞭查詢class表裡的num字段的權限。

在PG數據庫中不單單可以控制操作表的權限，其他數據庫對象，比如序列、函數、視圖等都可以控制。

所以PG的權限控制功能非常強大。

補充：Postgres用戶對數據庫的權限

用戶對數據庫的權限（登錄、超級用戶權限）

（1）查看當前數據庫中有用戶highgo和用戶a

highgo=#\du
               List of roles
 Role name |          Attributes          | Member of
-----------+------------------------------------------------+-----------
 a     |                        | {}
 highgo  | Superuser, Create role, Create DB, Replication | {}

（2）查看確認當前連接的用戶為超級用戶highgo，且該用戶後創建角色和數據庫的權限等

highgo=#select current_user;
 current_user
--------------
 highgo
(1row)

（3）查看當前集群中的數據庫

highgo=#\l
               List of databases
  Name  | Owner | Encoding | Collate |  Ctype  | Access privileges
-----------+--------+----------+------------+------------+-------------------
 highgo  | highgo | UTF8   | zh_CN.utf8 |zh_CN.utf8 |
 template0 | highgo | UTF8   | zh_CN.utf8 | zh_CN.utf8 | =c/highgo    +
      |    |     |      |      | highgo=CTc/highgo
 template1 | highgo | UTF8   | zh_CN.utf8 | zh_CN.utf8 | =c/highgo    +
      |    |     |      |      | highgo=CTc/highgo
(3rows)

（4）使用普通用戶a連接數據庫正常

highgo=#\c highgo a
Youare now connected to database "highgo" as user "a".
highgo=>select current_user;
 current_user
--------------
 a
(1row)
（5）使用超級用戶highgo連接數據庫正常
highgo=>\c highgo highgo
Youare now connected to database "highgo" as user "highgo".
highgo=#select current_user;
 current_user
--------------
 highgo
(1row)

（6）在超級用戶連接highgo後，設置不允許普通用戶a連接數據庫

highgo=#alter role a nologin;
ALTER ROLE
highgo=#\c highgo a
致命錯誤: 不允許角色"a" 進行登錄
Previousconnection kept
highgo=#

（7）在超級用戶連接highgo後，設置不允許普通用戶a連接數據庫後，賦予用戶a超級用戶權限後仍然無法連接數據庫

highgo=#alter role a superuser;
ALTERROLE
highgo=#\du
               List of roles
 Role name |          Attributes          | Member of
-----------+------------------------------------------------+-----------
 a    | Superuser, Cannot login            | {}
 highgo  | Superuser, Create role, Create DB, Replication | {}
 
highgo=#\c highgo a
致命錯誤: 不允許角色"a" 進行登錄
Previousconnection kept

（8）將登錄數據庫的權限賦予用戶a後，用戶a可登錄數據庫

highgo=#alter role a login;
ALTERROLE
highgo=#\c highgo a
Youare now connected to database "highgo" as user "a".
highgo=#select current_user;
 current_user
--------------
 a
(1row)

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：