Postgresql 資料庫使用者權限授權（使用者角色配置設定模式）

資料庫角色授權模式，使用者按需配置設定

環境要求

資料庫版本: >= pg9.4

實作案例目标,使用者的資料庫角色權限配置設定架構

參考案例-隻讀角色的建立與授權

pg 為使用者建立隻讀角色，所有需要配置隻讀賬号的隻要授予隻讀角色即可

初始化資料庫

-- 建立測試庫
CREATE DATABASE testdb;
-- 建立測試表
CREATE TABLE "test_1" ("id" bigserial NOT NULL, "txt" text NOT NULL,   PRIMARY KEY ("id") );           

建立角色，給角色授權

-- 一定要切換到 testdb 庫執行相關的指令，否則無效
-- \c testdb
-- 建立角色并授予登陸的權限，預設擁有public的部分權限，隻能檢視資料庫名詞和表名，其他不能查閱
CREATE ROLE db_role1 WITH 
  LOGIN
  NOSUPERUSER
  NOINHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD '123456';    
-- 添加注釋 
COMMENT ON ROLE db_role1 IS '隻讀使用者';

-- 授予隻讀權限
GRANT select ON all tables in schema public TO db_role1;
-- 檢視權限授權作用，會發現目前庫裡的所有表的權限都已授權，但是重新建立的表，則沒有對應的 權限，還需要執行 ALTER...GRANT... 才能動态建構新建立表的權限授權
select * from information_schema.table_privileges where grantee='db_role1';           

grant 【指令】之參數組合參考 ，例如： grant select 隻讀授權

來源:

https://www.postgresql.org/docs/14/ddl-priv.html

建立表自動授權

-- 為後續建立的表賦予相關權限，則必須使用 ALTER  ... GRANT ... 的文法授權
-- 函數授權
GRANT USAGE ON SCHEMA public TO db_role1;
-- 每建立一個新表，則賦予對象查詢的權限
ALTER DEFAULT PRIVILEGES for role postgres IN SCHEMA public
GRANT select ON TABLES TO db_role1;           

角色授權給使用者

-- 建立使用者，預設可檢視到所有的資料庫名和表名
CREATE USER pguser_1 WITH PASSWORD '123456';

-- 将角色1授權給使用者1
grant db_role1 to pguser_1; 

--- 檢視使用者的權限，會發現是空的
select * from information_schema.table_privileges where grantee='pguser_1';           

-- 正确的查詢使用者權限的方式是通過使用者的代理角色和使用者本身查詢相應的權限
-- 可參考函數表達式使用方式(pg12的相對寫得較全，新版本14的反而比較模糊，可通過頂部去切換目前資訊載體的版本，便于對照):
https://www.postgresql.org/docs/12/functions-info.html
-- 直接查詢使用者權限
 SELECT * from information_schema.table_privileges
 where grantee in (
   select rolname from pg_roles where pg_has_role('pguser_1',oid,'member')
 )           

删除使用者角色，以此收回權限

----------------------------- 權限删除 ---------------------
-- 回收使用者的角色即可回收使用者權限
revoke db_role1 from pguser_1;            

接下來我們給其他使用者授權，就隻需要配置設定給新使用者角色即可快速授權，可以高效地管理資料庫權限，以此達到高效的安全管控

grant 【自定義或系統角色】to  【新使用者】; 
           

其他場景和指令參考

-- 回收所有權限
revoke all on database "testdb" from db_role1;
revoke all on all tables in schema public from db_role1;
revoke all ON SCHEMA public from db_role1;
-- 回收預設權限
alter DEFAULT PRIVILEGES for role postgres IN SCHEMA public
revoke all on tables from db_role1;
drop role db_role1; -- 删除角色
drop user pguser_1; -- 删除使用者

-------------------------- 其他權限查詢參考 -------------
-- 檢視視圖權限
select * from information_schema.table_privileges where grantee='db_role1';
-- 檢視函數授權
select * from information_schema.usage_privileges where grantee='db_role1';
-- 檢視存儲過程函數相關權限表
select * from information_schema.routine_privileges where grantee='db_role1';