postgresql用戶與許可權管理

pg使用角色的概念管理資料庫訪問許可權，角色是一系列相關許可權的集合。為了管理方便，通常把一系列先關的許可權賦予給一個角色，如果哪個用戶需要這些許可權，就把這些角色賦予給響應的用戶。 由於用戶也擁有一系列的相關許可權，為了簡化管理，在PG中，角色與用戶是沒有區別的，一個用戶也是一個角色，我們可以把一個用戶的許可權賦值給另一個用戶。

用戶和角色在整個資料庫實例中是全局的，在同一個實例的不同資料庫中，看到的用戶都是相同的。

資料庫初始化時會創建一個與作業系統同名的超級用戶postgres，也可以使用-U:指定超級用戶

（一）創建用戶和角色

創建用戶和角色的語法如下：

-- 創建用戶
CREATE USER name [[ WITH ] option [...]]

-- 創建角色
CREATE ROLE name [[ WITH ] option [...]]

備註：在PG中，用戶與角色是沒有區別的，角色默認沒有login許可權，無法登陸，如果授予login之後，也可以像用戶一樣登陸。

option常用選項如下：

  • SUPERUSER | NOSUPERUSER：創建出來的用戶是否為超級用戶
  • CREATEDB | NOCREATEDB：創建出來的用戶是否有create database的許可權
  • CREATEROLE | NOCREATEROLE：創建出來的用戶是否有創建其它角色的許可權
  • CREATEUSER | NOCREATEUSER：創建出來的用戶是否有創建其它用戶的許可權
  • INHERIT | NOINHERIT：確定角色是否繼承其它角色的許可權
  • LOGIN | NOLOGIN：創建出來的角色是否有登錄許可權
  • CONNECTION LIMIT n：創建出來的角色並發連接數限制數量，默認值是「-1」,表示沒有限制
  • VALID UNTIL 'timestamp'：密碼失效時間

（二）許可權的管理

在postgresql資料庫中，任何邏輯對象(包括資料庫)都是有所有者的，也就是說資料庫對象都是屬於某個用戶的，所以，無需把對象的許可權賦予所有者，因為所有者默認就擁有所有的許可權，在PG資料庫中，刪除及其修改對象的許可權都不能賦予別的用戶，它是所有者的固有許可權，不能賦予或撤銷，所有者也隱式地擁有把操作該對象的許可權授予其他用戶的權利。

（2.1）兩類許可權

用戶的許可權分兩類，一類是在創建用戶時就指定的許可權，有：

  • 超級用戶的許可權
  • 創建資料庫的許可權
  • 是否允許login的許可權
  • 更多見\help create role

這些許可權是創建用戶時指定的，後面可以使用alter role來修改。

另一類是有GRANT和REVOKE命令來管理的，有：

  • 在資料庫中創建schema的許可權

  • 在指定的資料庫中創建臨時表的許可權

  • 連接某個資料庫的許可權

  • 在某個資料庫中創建資料庫對象的許可權，如表、視圖、函數等

  • 在一些表中做SELECT 、INSERT、UPDATE、DELETE等操作的許可權

  • 在一張表的列上做 SELECT 、UPDATE、DELETE等操作的許可權

  • 對序列進行查詢(執行序列的currval函數)、使用(執行序列的currval和nextval函數)、更新的許可權

  • 在表上創建觸發器的許可權

  • 把表、索引創建到指定表空間的許可權

（2.2）GRANT 和REVOKE許可權

（2.2.1）角色的授予與回收

-- 將角色授予給另外一個角色
GRANT role_name[,...] TO role_name[,...] WITH ADMIN OPTION;

-- 角色回收
REVOKE role_name[,...] FROM role_name[,...][CASCADE | RESTRICT]

（2.2.2）許可權的授予與回收

許可權的授予

db1=# \help grant
命令：       GRANT
描述：       定義存取許可權
語法：
-- 表相關許可權
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] 表名 [, ...]
         | ALL TABLES IN SCHEMA 模式名稱 [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 列相關許可權
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( 列名稱 [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( 列名稱 [, ...] ) }
    ON [ TABLE ] 表名 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 序列相關許可權
GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE 序列名稱 [, ...]
         | ALL SEQUENCES IN SCHEMA 模式名稱 [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 資料庫相關許可權
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE 資料庫名稱 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN 域_名稱 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER 外部數據封裝器的名稱 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER 伺服器名稱 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 函數、存儲過程相關許可權
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } 程式名稱 [ ( [ [ 參數模式 ] [ 參數名稱 ] 參數類型 [, ...
] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA 模式名稱 [, ...] }

（2.2.3）許可權授予回收總結

postgresql中的許可權是按照以下幾個層次進行管理的：

1.首先管理賦予用戶的特殊許可權，如超級用戶的許可權，創建資料庫的許可權、創建用戶的許可權、login許可權等

2.然後是在資料庫中創建schema的許可權

3.接著是在schema中創建資料庫對象(如表、索引)的許可權

4.之後是對錶進行操作(insert、update、delete、select)的許可權

5.最後是操作(update、delete、select)表中某些欄位的許可權

（2.3）許可權操作常用命令

（2.3.1）授予增刪查改許可權

GRANT update,delete,insert,select ON ALL TABLES IN SCHEMA public TO signalmap_rw;

（2.3.2）回收public許可權

在初始化資料庫實例後，資料庫中默認就會存在一個名稱為「public」的schema，任何用戶都有在public schema上的create 許可權，通常我們需要把這個許可權回收回來

revoke create on schema public from public;

（2.3.3）創建只讀用戶

-- 回收public模式的create許可權
revoke create on schema public from public;
-- 創建只讀用戶，密碼為readonly
create user readonly password 'readonly';
-- 授權public模式給readonly
grant usage on schema public to readonly;
-- 授權public模式的所有表許可權給readonly用戶
grant select on all tables in schema public to readonly;
-- 切換到readonly測試
\c - readonly 
-- 切換到postgres用戶
\c - postgres
-- 將public模式的默認表查詢許可權授予readonly用戶
alter default privileges in schema public grant select on tables to readonly;

（2.3.4）查看用戶許可權

-- 查看某用戶的系統許可權
SELECT * FROM  pg_roles WHERE rolname='postgres';

-- 查看某用戶的表級別許可權
select * from information_schema.table_privileges where grantee='postgres';

-- 查看某用戶的usage許可權
select * from information_schema.usage_privileges where grantee='postgres';

-- 查看某用戶在存儲過程函數的執行許可權
select * from information_schema.routine_privileges where grantee='postgres'; 

-- 查看某用戶在某表的列上的許可權
select * from information_schema.column_privileges where grantee='postgres'; 

-- 查看當前用戶能夠訪問的數據類型
select * from information_schema.data_type_privileges ; 

-- 查看用戶自定義類型上授予的USAGE許可權
select * from information_schema.udt_privileges where grantee='postgres';
Tags:

VirMach 便宜 VPS