PostgreSQL實現了基于角色的存取控制機制。角色是權限的集合?？梢詫嘞拶x給用戶，也可以將權限賦給角色?？梢詫⒔巧x給一個用戶，該用戶將擁有角色的所有權限。也可以將角色賦給其它的角色。PostgreSQL中的用戶和角色使用同一個名字空間。數據庫中不能有兩個用戶同名，不能有兩個角色同名，也不能有一個用戶和一個角色同名。


數據庫中存在一個內置的超級用戶postgres，postgres可以在數據庫中創建新的超級用戶，也可以把一個超級用戶變成普通用戶。同時數據庫中也有一個內置的名為PUBLIC的角色, 任何用戶都可以給PUBLIC授權和回收權限。數據庫中的所有用戶和角色都自動擁有PUBLIC角色擁有的一切權限。

2.1 數據庫權限

PostgreSQL中的權限分為系統權限和對象權限。系統權限只能賦給用戶，不能賦給角色。對象權限既能賦給用戶，又能賦給角色。 系統權限有以下三種：


權限名稱


權限功能

CREATEDB


創建數據庫

CREATEROLE


創建、刪除和修改角色或用戶


對象權限與具體的數據庫對象有關，不同的數據庫對象有不同的權限。 PostgreSQL中的對象表(table)、序列(sequnce)、數據庫(database)、函數(function)、語言(languange)、模式(schema)和表空間(tablespace)都有自己的權限類型。


（1）表權限如下表



權限名稱


權限功能

SELECT


查詢表

INSERT


表中插入新記錄

UPDATE


更新表

DELETE


刪除表中的記錄

REFERENCES


可以在表中創建外鍵約束, 必須在被外鍵引用的表上同時有該權限才能創建外鍵約束

TRIGGER


可以在表上創建觸發器





（2）序列權限如下表



權限名稱


權限功能

SELECT


對序列使用currval函數

USAGE


對序列使用currval 函數和nextval函數

UPDATE


對序列使用nextval 函數和setval函數





（3）數據庫權限如下表



權限名稱


權限功能

CONNECT


可以連接數據庫

TEMPORAR


可以在數據庫中創建臨時表

TEMP


同上，可以在數據庫中創建臨時表

CREATE


可以在數據庫中創建新的模式



PUBLIC角色自動擁有新建的數據庫上的CONNECT權限。



（4）函數權限如下表



權限名稱


權限功能

EXECUTE


可以調用函數，也可以使用任何在該函數基礎上實現的運算符





（5）語言權限如下表



權限名稱


權限功能

USAGE


可以使用該語言創建函數





（6）模式權限如下表



權限名稱


權限功能

CREATE


在模式里面創建新的數據庫對象，如果要重命令模式里面的一個數據庫對象，執行命令的用戶必須對該模式有CREATE權限，同時必須是并重命名的對象的所有者。

USAGE


允許訪問模式里面的數據庫對象





（7）表空間權限如下



權限名稱


權限功能

CREATE


在表空間里面創建表、臨時表和索引，新創建的數據庫可以將該表空間作為它的默認表空間。 如果該權限被回收，在該表空間中被創建的數據庫對象仍然被存放在該表空間中。








2.2 數據庫用戶和角色


2.2.1創建數據庫用戶



使用下面的命令來創建數據庫用戶：

CREATE USER? name? [ [ WITH ]? option [ ... ] ]? [ WITH ] { ENCRYPTED | UNENCRYPTED } PASSWORD ‘password’[ [ WITH ]? option [ ... ] ]



Option可以是:

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CONNECTION LIMIT connlimit

| VALID UNTIL ’timestamp’





執行該命令的用戶必須具有CREATEROLE權限或者是超級用戶，其中WITH關鍵字可以省略。

SUPERUSER表示新創建的用戶是超級用戶，NOSUPERUSER表示新創建的用戶不是超級用戶，默認是NOSUPERUSER，只有postgres才能創建超級用戶。

CREATEDB表示新創建的用戶有CREATEDB權限，NOCREATEDB表示新創建的用戶沒有CREATEDB權限，默認是NOCREATEDB。

CREATEROLE表示新新創建的用戶有CREATEROLE權限，NOCREATEROLE表示新創建的用戶沒有CREATEROLE權限，默認是NOCREATEROLE。



CONNECTION LIMIT connlimit 設定該用戶能在數據庫中建立的并發連接的上限，默認是沒有上限。

{ ENCRYPTED | UNENCRYPTED } PASSWORD ‘password’設定用戶的密碼，指明ENCRYPTED,則用戶的密碼用加密的方式(MD5方法加密)存在數據庫中，指明UNENCRYPTED則用戶的密碼用明文的方式存在數據庫中。

VALID UNTIL ‘timestamp’ 指定密碼的過期時間，timestamp格式是”年-月-日”，例如” 2010-09-23”，默認是永不過期。



下面是一些實際的例子：?

(1) CREATE USER? li_ming? WITH? PASSWORD ‘jw8s0F4’? CREATEDB CREATEROLE CONNECTION LIMIT 100 LOGIN;?

該命令創建一個名為li_ming的數據庫用戶，它不是超級用戶，它的密碼是jw8s0F4， 密碼是用加密方式存放在數據庫中，它具有CREATEDB、 CREATEROLE 和LOGIN權限， 它在數據庫中建立的并發連接數目不能超過100。



(2) CREATE USER? li_zhe? WITH? PASSWORD ‘hjkkoi’?? SUPERUSER? VALID UNTIL ‘2010-09-09’;

該命令創建一個名為li_zhe的數據庫用戶，它是超級用戶，它的密碼是hjkkoi， 密碼是用加密方式存放在數據庫中，它在數據庫中建立的并發連接數目沒有上限，它的密碼將在2010年09月09日過期。




2.2.2 修改數據庫用戶的屬性



使用下面的命令來修改數據庫用戶的屬性：

ALTER USER name [ [ WITH ] option [ ... ] ]

其中option可以是:

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CONNECTION LIMIT connlimit

| { ENCRYPTED | UNENCRYPTED } PASSWORD ’password’

| VALID UNTIL ’timestamp’



具有CREATEROLE權限的用戶或者超級用戶才能執行該命令，option選項的含義參見CREATE USER命令。數據庫超級用戶可以修改任何普通用戶的屬性。只用postgres才能修改其它的超級用戶的屬性。一個用戶只能修改自己的密碼，不能修改自己的其它屬性。



下面是一些實際的例子:

（1）ALTER USER? li_ming? NOCREATEDB? NOCREATEROLE? NOLOGIN;

這條命令將使數據庫用戶li_ming失去CREATEDB、CREATEROLE和LOGIN權限。



（2）ALTER? USER? li_ming? password ‘hjkop3’;

這條命令將數據庫用戶li_ming的密碼改為hjkop3。




2.2.3 刪除用戶?



使用下面的命令來刪除數據庫用戶：

DROP USER [ IF EXISTS ] name [, ...]



DROP USER命令刪除一個數據庫用戶。執行命令的用戶必須有具有CREATEROLE 權限或者是超級用戶。如果被刪除的用戶是超級用戶，執行命令的用戶必須是postgres。一個用戶被刪除以后，該用戶擁有的所有數據庫對象都會被自動刪除，所有依賴于該用戶擁有的數據庫對象的數據庫對象也會被自動刪除。下面是一個例子：

（1）DROP USER user1;

刪除用戶user1。


2.2.4 創建角色

使用下面的命令來創建數據庫角色：

CREATE ROLE name



具有CREATEROLE權限的用戶才能執行該命令。下面是一個例子：

（1）CREATE ROLE role1;

這條命令在數據庫中創建一個名為role1的角色。


2.2.5 刪除角色

使用下面的命令來刪除數據庫角色：

DROP ROLE [ IF EXISTS ] name [, ...]



具有CREATEROLE權限的用戶才能執行該命令。下面是一個例子：

（1）DROP ROLE role1;

刪除角色role1。


2.2.6 給角色或用戶授予對象權限



可以使用下面的命令來給角色或用戶授予對象權限：

（1）GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }

[,...] | ALL [ PRIVILEGES ] }

ON [ TABLE ] tablename [, ...]

TO {? rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]



（2）GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }

ON SEQUENCE sequencename [, ...]

TO {? role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]



（3）GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }

ON DATABASE dbname [, ...]

TO {? role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]



（4）GRANT { EXECUTE | ALL [ PRIVILEGES ] }

ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]

TO {? role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]



（5）GRANT { USAGE | ALL [ PRIVILEGES ] }

ON LANGUAGE langname [, ...]

TO {? role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]



（6）GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }

ON SCHEMA schemaname [, ...]

TO {? role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]



（7）GRANT { CREATE | ALL [ PRIVILEGES ] }

ON TABLESPACE tablespacename [, ...]

TO {? role_or_user_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]



執行該命令的用戶必須滿足下面的條件中的一個：

（1）是超級用戶。

（2）是數據庫對象的所有者。

（3）對權限有GRANT OPTION, 必須是直接擁有，從擁有的角色那里得到的GRANT OPTION是無效的。



USAGE、SELECT、UPDATE、CREATE和EXECUTE的含義已經在上面解釋過。 ALL PRIVILEGES或者ALL表示該數據庫對象上的所有權限。PUBLIC表示將權限賦給數據庫中的所有角色和用戶，即使是以后新建立的用戶和角色也會自動擁有這些權限。GRANT OPTION 表示接收權限

的用戶可以將該權限再賦給其它用戶和角色， PUBLIC和GRANT OPTION不能同時使用。





下面是一些具體的例子：

（1）GRANT SELECT on? salary? to user1, role1;

這條命令將表salary上的SELECT權限賦給用戶user1和角色role1。



（2）GRANT SELECT on? salary, UPDATE on salary to role1 ;

這條命令將表salary上的SELECT和UPDATE權限賦給角色role1。



（3）GRANT ALL PRIVILEGES on? salary? to role1 ;

這條命令將表salary上的所有權限賦給角色role1。



（4）GRANT USAGE? on? SEQUENCE id_counter to role1 ;

這條命令將序列id_counter上的USAGE權限賦給角色role1。



（5）GRANT USAGE? on? SEQUENCE id_counter to PUBLIC;

這條命令將序列id_counter上的USAGE權限賦給數據庫中的所有角色和用戶。



（6）GRANT USAGE? on? SEQUENCE id_counter to user1,role1 WITH GRANT OPTION;

這條命令將序列id_counter上的USAGE權限賦給角色或用戶user1和角色role1。用戶user1可以將該權限再賦給其它用戶或角色。




2.2.7 從角色或用戶手中回收對象權限



使用下面的命令來從角色或用戶手中回收對象權限：

（1）REVOKE [ GRANT OPTION FOR ]

{ { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }

[,...] | ALL [ PRIVILEGES ] }

ON [ TABLE ] tablename [, ...]

FROM {? role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]



（2）REVOKE [ GRANT OPTION FOR ]

{ { USAGE | SELECT | UPDATE }

[,...] | ALL [ PRIVILEGES ] }

ON SEQUENCE sequencename [, ...]

FROM { [? role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]



（3）REVOKE [ GRANT OPTION FOR ]

{ { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }

ON DATABASE dbname [, ...]

FROM { role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]



（4）REVOKE [ GRANT OPTION FOR ]

{ EXECUTE | ALL [ PRIVILEGES ] }

ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]

FROM {? role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]



（5）REVOKE [ GRANT OPTION FOR ]

{ USAGE | ALL [ PRIVILEGES ] }

ON LANGUAGE langname [, ...]

FROM { role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]



（6）REVOKE [ GRANT OPTION FOR ]

{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }

ON SCHEMA schemaname [, ...]

FROM { role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]



（7）REVOKE [ GRANT OPTION FOR ]

{ CREATE | ALL [ PRIVILEGES ] }

ON TABLESPACE tablespacename [, ...]

FROM {? role_or_user_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]



執行該命令的用戶必須曾經向被回收權限的用戶或角色授予過被回收的權限。如果執行該命令的用戶沒有向被回收權限的用戶或角色授予過被回收的權限，但該用戶是超級用戶，或者是權限引用的數據庫對象的所有者，或者擁有被回收的權限和該權限的GRANT OPTION，數據庫不會報錯，被回收權限的用戶或角色仍然擁有被回收的權限。

PUBLIC表示從從PUBLIC角色手中回收權限。GRANT OPTION FOR表示只回收權限的GRANT OPTION，不回收權限。REVOKE ALL PRIVILEGES 表示回收指定的數據庫對象上的所有權限。CASCADE表示如果被回收權限的用戶或角色將該權限又賦給了其它用戶和角色，該權限也會從這些用戶和角色手中被回收掉。RESTICT的含義與CASCADE相反，表示如果被回收權限的用戶或角色將該權限又賦給了其它用戶和角色，該權限不會從這些用戶和角色手中被回收掉。如果CASCADE 和 RESTRICT 都沒有指定，默認是RESTRICT。



下面是一些具體的例子：

（1）REVOKE SELECT on? salary? from PUBLIC, role1;

這條命令將表salary上的SELECT權限從角色PUBLIC，角色role1手中回收掉。



（2）REVOKE? GRANT OPTION FOR? SELECT on? salary? from? user1;

這條命令將salary上的SELECT權限的GRANT OPTION從用戶user1手中回收掉， user1仍然擁有SELECT權限，但他不能將該權限再賦給其它用戶或角色。



（3）REVOKE? SELECT on? salary? from? user1 CASCADE;

這條命令將表salary上的SELECT權限從用戶user1手中回收掉，如果user1將該權限賦給了其它用戶或角色，這些用戶或角色也會失去該權限。




2.2.7 將角色賦給角色和用戶



使用下面的命令將角色賦給角色和用戶：

GRANT role_name [, ...] TO role_or_user_name [, ...]



執行該命令的用戶應該滿足下面的兩個條件中的一個:

（1）具有CREATEROLE權限。

（2）是超級用戶。



下面是一些具體的例子：

（1）GRANT role1 to role2, user1;

這條命令將角色role1賦給角色role2和用戶user1。



（2）GRANT role1 to role2, user1 WITH ADMIN OPTION;

這條命令將角色role1賦給角色role2和用戶user1，role2的用戶成員可以將role1賦給其它用戶或角色，user1也可以將role1賦給其它用戶或角色。




2.2.8 從角色和用戶手中回收角色



使用下面的命令從角色和用戶手中回收角色：

REVOKE role [, ...] FROM role_or_user_name [,...]



執行該命令的用戶應該滿足下的兩個條件中的一個:

（1）具有CREATEROLE權限。

（2）是超級用戶。





注意: 如果多個不同的用戶將同一權限obj_priv賦給了同一個用戶或角色role1，如果有一個授權者從role1手中回收權限obj_priv，那么role1仍然擁有權限obj_priv。只有所有的授權者對role1執行了回收權限obj_priv的操作， 而且role1擁有的所有角色都不擁有權限obj_priv，role1才會真正地失去權限obj_priv。



下面是一個例子：

（1）RERVOKE role1 from role2, user1

這條命令從角色role2, 用戶user1手中回收角色role1, role2和user1將失去從role2得來的權限

轉載于:https://www.cnblogs.com/loveyue/archive/2011/06/04/2072531.html

總結

以上是生活随笔為你收集整理的转：PostgreSQL角色、用户、权限和数据库安全的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。