PostgreSQL技術大講堂 - Part 6：PG使用者與角色管理

PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色許可權、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

Part 6：PG使用者與角色管理

內容1:PostgreSQL 建立使用者

內容2:PostgreSQL 建立角色

內容3:PostgreSQL 許可權介紹

內容4:PostgreSQL 給使用者授權

內容5:PostgreSQL 給角色授權

內容6:PostgreSQL 回收許可權

6.1、使用者角色概述

使用者與角色：? 資料庫使用者用來訪問、管理資料庫中的物件(表、索引…)

? 資料庫角色用來管理資料庫訪問許可權，簡化許可權的管理

? 使用者和角色在整個資料庫集簇中是全域性性的，不是針對某個單一資料庫，只要有足夠的許可權，使用者可以訪問所有資料庫的物件。

? 資料庫使用者可以分為兩類：

超級使用者--postgres

普通使用者--根據需要建立 使用者與角色的區別：? user：擁有login登陸資料庫許可權的role

? role:可以擁有資料庫物件，如表、索引，也可以把這些物件上的許可權賦予其它角色，以控制哪些使用者對哪些物件擁有哪些許可權

? group：不擁有replication/noreplication、connectionlimit屬性的role

? 在PG8.1之前，user與group是不同型別的實體，現在可以被看作是role，任意一個role均可自由的在user與group間轉換

6.2、建立使用者

建立使用者? 方式1：在系統命令列使用create user命令

create user username create user -U postgres -p 7788 user1 (--forwindows)

? 方式2：在psql命令列使用create user(role)指令

CREATE user[ROLE] rolename;

CREATE USER name [[WITH] option[...]]

這裡的option可以是：

SUPERUSER | NOSUPERUSER |CREATEDB | NOCREATEDB |CREATEROLE | NOCREATEROLE |CREATEUSER | NOCREATEUSER |INHERIT | NOINHERIT#角色是其他角色的成員，這些子句決定新角色是否從那些角色中“繼承”特權 |LOGIN | NOLOGIN |REPLICATION | NOREPLICATION |BYPASSRLS | NOBYPASSRLS#決定是否一個角色可以繞過每一條行級安全性(RLS)策略。|CONNECTIONLIMIT connlimit |[ENCRYPTED | UNENCRYPTED] PASSWORD 'password' |VALIDUNTIL 'timestamp' | INROLE role_name [,...] |INGROUP role_name[,...] | ROLErole_name[,...]|ADMINrole_name[,...]#ADMIN子句與ROLE相似，讓它們能夠把這個角色中的成員關係授予給其他人。|USER role_name[,...] |SYSID uid

建立使用者示例：

CREATE USER u1 SUPERUSER PASSWORD 'u1';--建立一個具有超級使用者許可權的使用者

CREATE USER u2 CREATEDB PASSWORD 'u2';--建立一個具有建庫許可權的使用者

CREATE USER u3 LOGIN;--建立一個具有登入許可權的使用者，預設值

CREATE ROLE u4 encrypted PASSWORD '123456' VALIDUNTIL '2018-08-16';--建立一個帶有加密密碼且具有有效時間的使用者

注：屬性LOGIN、SUPERUSER和CREATEROLE被視為特殊許可權，它們不會像其它資料庫物件的普通許可權那樣被繼承。

6.3、建立角色

CREATE ROLE name [[WITH]option[...]] 這裡的option可以是：

SUPERUSER | NOSUPERUSER|CREATEDB | NOCREATEDB|CREATEROLE | NOCREATEROLE|CREATEUSER | NOCREATEUSER|INHERIT | NOINHERIT|LOGIN | NOLOGIN|REPLICATION | NOREPLICATION|CONNECTIONLIMIT connlimit|[ENCRYPTED | UNENCRYPTED] PASSWORD 'password'|VALIDUNTIL 'timestamp' | INROLE role_name[,...]|INGROUP role_name[,...] | ROLE role_name[,...]|ADMIN role_name[,...] | USER role_name[,...] | SYSID uid

建立角色示例

CREATE ROLE manager;--建立一個角色

CREATE ROLE dev createdb;--建立一個具有建庫許可權的角色

CREATE ROLE r1 LOGIN;--建立一個具有登入許可權的角色，類似於使用者

CREATE ROLE u4 encrypted PASSWORD ‘123456’ VALIDUNTIL ‘2018-08-16’;--建立一個帶有加密密碼且具有有效時間的角色

6.4、檢視使用者與角色

檢視使用者與角色資訊postgres=#\du 指令顯示使用者和角色屬性

透過資料庫字典表來檢視使用者資訊postgres=# \d pg_user

透過資料庫字典表來檢視

透過資料庫字典表來檢視角色資訊

postgres=# \d pg_roles

透過資料庫字典表來檢視角色資訊

6.5、修改使用者和角色

修改使用者屬性postgres=# \h alter user

命令：ALTER USER

描述：更改資料庫角色

語法：

ALTER USER role_specification [ WITH ] 選項 [ ... ]

選項可以是

SUPERUSER | NOSUPERUSER| CREATEDB | NOCREATEDB| CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN| REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT 連線限制| [ ENCRYPTED ] PASSWORD '口令' | PASSWORD NULL | VALID UNTIL '時間戳'

ALTER USER 名稱 RENAME TO 新的名稱

……

修改使用者示例

ALTER USER u2 RENAME TO u22; --修改使用者的名字

ALTER USER u22 PASSWORD ‘u22’; --修改使用者的密碼

ALTER USER u22 CREATEROLE; --修改使用者的許可權

ALTER USER u22 IN DATABASE testdb RESET ALL; --修改資料庫testdb中的引數重設 為預設值

修改角色示例

ALTER ROLE dev RENAME TO dev1; --修改角色的名字

ALTER ROLE dev1 SUPERUSER; --修改角色的許可權

ALTER ROLE dev1 LOGIN; --修改角色的許可權

6.6、刪除使用者

刪除使用者方法：

? 方式1:在系統命令列使用drop user命令刪除使用者 drop user –U postgres –p 7788 username;

? 方式2:在psql命令列使用drop刪除

drop role rolename; 或 drop user username;

DROP ROLE IF EXISTS role_name;

注意事項：1、只用超級使用者能夠刪除超級使用者

2、只有具有create role許可權的使用者能刪除非超級使用者

3、刪除使用者前，需要先刪除依賴該使用者的物件、許可權等資訊 

 4、任何屬於該組角色的物件都必須先被刪除或者將物件的所有者賦予其它角色， 任何賦予該組角色的許可權也都必須被撤消。

5、刪除組role只會刪除組的role本身，組的成員並不會被刪除 刪除使用者與角色示例：

DROP USER u22;

DROP USER IF EXISTS u3;

DROP ROLE IF EXISTS u4;

注意：刪除使用者和角色所用命令可以通用

6.7、啟用角色賦予的許可權

啟用某個角色許可權：

分配給使用者的角色在使用者登入時其許可權不會自動生效，需要啟用該角色。命令：

Set role manager;

注意：此時登陸的使用者名稱變成manager。在此期間建立的物件也是屬於manager。

6.8、許可權管理

許可權管理

? 每個資料庫物件都有一個所有者，預設情況下，所有者擁有該物件的所有許可權

? 在資料庫中所有的許可權都和角色掛鉤

? 對超級使用者postgres不做許可權檢查，其它使用者走ACL(Access ControL List)

? 對於資料庫物件，開始只有所有者和超級使用者可以做任何操作，其它走ACL

許可權管理結構層次圖

以上就是Part 6 - PG使用者與角色管理 的內容，歡迎進群一起探討交流，釘釘交流群：35，82，24，60，釘釘群專門講解，往期影片也可聯絡cuug諮詢老師