王丁丁-搞暈了這讓人摳腦殼的PG權限把ta嚼爛！.pdf

《王丁丁-搞暈了這讓人摳腦殼的PG權限把ta嚼爛！.pdf》由會員分享，可在線閱讀，更多相關《王丁丁-搞暈了這讓人摳腦殼的PG權限把ta嚼爛！.pdf（23頁珍藏版）》請在三個皮匠報告上搜索。

1、/2025.01.04/搞暈了，這讓人摳腦殼的PG權限，把ta嚼爛！王丁丁 PostgreSQL ACEBOE工業互聯網數據庫專家Subject 1用戶和角色Subject 2三權分立的基本思想Subject 3PG權限概述Subject 4PG權限管理CONTENTPART 01用戶和角色/2025.01.04/1.用戶和角色4n 用戶：是指被授權訪問和管理數據庫系統的實體，即使用和管理數據庫資源的人。每個數據庫管理系統（DBMS）都有自己的用戶管理策略，用于管理數據庫用戶的訪問權限。n 角色：是一種用于管理和組織數據庫用戶權限的重要概念。角色允許數據庫管理員將一組權限分配給一個角色，然后將

2、該角色分配給一個或多個數據庫用戶。這樣可以簡化權限管理，提高安全性和可維護性。create usercreate rolelogin-ROLE創建時缺省不具有LOGIN權限CREATE ROLE hefei PASSWORD hefei;-USER創建時缺省就具備了LOGIN權限CREATE USER beijing PASSWORD beijing;ROLE不具有缺省的LOGIN權限，這就是ROLE/USER的區別PART 02PG的三權分立/2025.01.04/目的默認情況下擁有Superuser屬性的系統管理員，具備系統最高權限。但在實際業務管理中，為了避免系統管理員擁有過度集中的權利

3、帶來高風險，可以設置三權分立。分類三權分立的基本思想是將系統中關鍵操作的權限分配給不同的角色，來避免單個用戶或角色能夠完全控制整個系統，這通常包括以下三個角色：1.數據庫管理員（DBA）：負責數據庫運維管理任務、如創建用戶、表空間、schema管理等。2.安全管理員（SA）：負責數據庫安全策略的制定和實施，包括用戶權限分配和審計等。3.應用管理員（AA）：負責數據庫應用的開發和維護，包括表結構設計、查詢優化和數據加載等。2.PG的三權分立PART 03PG權限概述/2025.01.04/3.PG權限概述n 權限的分類對象權限對象創建后，默認只有對象所有者或者系統管理員可以查詢、修改和銷毀對象，

4、以及通過GRANT將對象的權限授予其他用戶。對象權限指數據庫對象(表和視圖、指定字段、數據庫、函數、模式、表空間等)的相關權限(創建、刪除、修改等)。GRANT命令將數據庫對象的特定權限授予一個或多個角色,這些權限會追加到已有的權限上。系統權限系統權限又稱為用戶屬性，包括Superuser、CREATE DB、CREATE ROLE、Replication和LOGIN。系統權限一般通過CREATE/ALTER ROLE語法來指定。其中，Superuser權限可以通過GRANT/REVOKE ALL PRIVILEGE授予或撤銷。但系統權限無法通過ROLE和USER的權限被繼承，也無法授予PUB

5、LIC。Schema/2025.01.04/3.PG權限概述GRANT使用GRANT語句將系統權限，對象權限與權限授予該用戶schema授權:grant usage on schema sch1 to user1;REVOKEn 行級訪問行級訪問控制特性將數據庫訪問控制精確到數據表行級別，使數據庫達到行級訪問控制的能力。不同用戶執行相同的SQL查詢操作，讀取到的結果是不同的。用戶可以在數據表創建行訪問控制（Row Level Security）策略-打開行訪問控制策略開關postgres=#ALTER TABLE test ENABLE ROW LEVEL;-創建行訪問控制策略，當前用戶只能查

6、看用戶自身的數據postgres=#CREATE ROW LEVEL SECURITY POLICY test_rls ON test USING(role=CURRENT_USER);-創建表test，包含不同用戶數據信息postgres=#CREATE TABLE test(id int,role varchar(100),data varchar(100);REVOKE：使用REVOKE命令進行權限回收schema授權收回:revoke usage on schema sch1 from user1;/2025.01.04/3.PG權限概述n 列級訪問在一些業務場景中，某些列存儲敏感信息

7、，需要對用戶不可見，但其他列的數據又需要用戶能夠查看或操作，此時就需要針對數據表的特定列做訪問控制，實現針對用戶的列級別的訪問控制。GRANT SELECT(customer_sk,first_name)ON customer TO jack;postgres=#c-jackpostgres=#select customer_sk,first_name from customer;customer_sk|first_name-+-2345|Grace(1 row)postgres=#select*from customer;ERROR:permission denied for table c

8、ustomer例：授予用戶jack對customer_t1表的c_customer_sk和c_first_name列的查詢權限例：回收用戶jack對customer表的customer_sk和first_name列的查詢權限：REVOKE SELECT(customer_sk,first_name)ON customer FROM jack;/2025.01.04/3.PG權限概述n 權限繼承可以通過權限繼承來實現用戶之間的權限傳遞，在這種情況下，每個角色或用戶都可視為擁有一個或多個數據庫權限的集合,當授權的角色或用戶發生變更或被撤銷時，所有繼承該角色或用戶權限的用戶擁有的權限都會隨之發生變更

9、。pWITH ADMIN OPTION對于角色，當聲明了WITH ADMIN OPTION，被授權的用戶可以將該角色再授予其他角色/用戶，或從其他角色/用戶postgres=#CREATE ROLE manager PASSWORD xxxxxxxxx;postgres=#GRANT joe TO manager WITH ADMIN OPTION;postgres=#CREATE ROLE senior_manager PASSWORD xxxxxxxxx;postgres=#GRANT manager TO senior_manager;postgres=#REVOKE manager F

10、ROM joe;postgres=#REVOKE senior_manager FROM manager;postgres=#DROP USER manager;p范例1.創建角色manager，將joe的權限授權給manager，并允許該角色將權限授權給其他人2.創建用戶senior_manager，將用戶manager的權限授權給該用戶3.撤銷權限，并清理用戶/2025.01.04/3.PG權限概述n 權限繼承可以通過權限繼承來實現用戶之間的權限傳遞，在這種情況下，每個角色或用戶都可視為擁有一個或多個數據庫權限的集合,當授權的角色或用戶發生變更或被撤銷時，所有繼承該角色或用戶權限的用戶擁有

11、的權限都會隨之發生變更。pWITH ADMIN OPTION對于角色，當聲明了WITH ADMIN OPTION，被授權的用戶可以將該角色再授予其他角色/用戶，或從其他角色/用戶postgres=#CREATE ROLE manager PASSWORD xxxxxxxxx;postgres=#GRANT joe TO manager WITH ADMIN OPTION;postgres=#CREATE ROLE senior_manager PASSWORD xxxxxxxxx;postgres=#GRANT manager TO senior_manager;postgres=#REVOK

12、E manager FROM joe;postgres=#REVOKE senior_manager FROM manager;postgres=#DROP USER manager;p范例1.創建角色manager，將joe的權限授權給manager，并允許該角色將權限授權給其他人2.創建用戶senior_manager，將用戶manager的權限授權給該用戶3.撤銷權限，并清理用戶/2025.01.04/3.PG權限概述n 默認權限通過提供ALTER DEFAULT PRIVILEGES功能來設置應用于將來創建的對象的權限，這不會影響分配到已有對象中的權限。如果開發人員希望對整庫所有表的查

13、詢權限均開放，而且以后生產庫中新建的表也需要默認的開放此帳號的查詢權限，這時如何處理？這時，今天學習到的命令即可解決這個問題，命令為 ALTER DEFAULT PRIVILEGESpostgres=#ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO jack;ALTER DEFAULT PRIVILEGES將myschema下的所有表的查詢權限授予用戶jack回收該權限：postgres=#ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT

14、 ON TABLES FROM jack;注：上述語句只回收了默認權限，已授予的對象上的select權限還在，如果需要撤銷需要手動進行回收。注意事項：1.進行default privilege授權時，一般登錄對應schema下的owner賬號進行授權2.for role的含義是修改某個具體用戶后續創建的數據庫對象的默認權限修改某個具體用戶后續創建的數據庫對象的默認權限，如果不指定 for 子句，則會更改當前用戶創建的對象的默認權限alterdefaultprivilegesforroleuser1grantselectontablestouser2;后續 user1 新建的表默認授予查詢權限給

15、 user2/2025.01.04/3.PG權限概述各類型對象的權限列表：/2025.01.04/3.PG權限概述權限參數說明：PART 04PG權限管理/2025.01.04/4.PG權限管理17n 權限架構數據庫集簇schema管理1.允許多個用戶使用同一數據庫而不相互干擾,數據庫嚴格分開的，不允許跨庫訪問2.schema可以將數據庫對象組織成易于管理的邏輯組，用戶根據其對schema的權限，可以訪問所連接數據庫的schema中的對象3.表空間是一個目錄，僅是起到了物理隔離的作用，可以跨數據庫，其管理功能依賴于文件系統。4.ALL代表所有訪問權限，PUBLIC代表所有用戶5.數據庫在創建后

16、，會自動創建名為public的schema，這個schema的all權限已經賦予給public角色6.擁有者（Owner）對相應的數據庫對象擁有完全的控制權用戶LOGIN數據庫CONNECT模式USAGE、SELECT行級列級最終訪問表/2025.01.04/4.PG權限管理18n 數據庫權限數據庫屬主，表示數據庫的擁有者，擁有數據庫的所有權限，默認創建數據庫的用戶為數據庫屬主。n如何授權？1.CREATE ON DATABASE:允許在數據庫里創建新的模式2.CONNECT ON DATABASE:允許用戶連接到指定的數據庫。3.ALTER ON DATABASE:允許用戶修改數據庫中指定對

17、象的屬性，但不包括修改對象的所有者和修改對象所在的模式。4.DROP ON DATABASE:允許用戶刪除數據庫中指定的對象5.ALL PRIVILEGES ON DATABASE:一次性給指定用戶/角色賦予所有可賦予的權限只有系統管理員有權執行GRANT ALL PRIVILEGES。注意點：數據庫在創建后，允許public角色連接，即允許任何人連接需要revoke connect on database xxx from public再顯式執行grant connect on database xxx to xxx數據庫授與所有權限grant all privileges on datab

18、ase mydb to user;將數據庫mydb的屬主修改為jackalter database mydb owner to jack;/2025.01.04/4.PG權限管理19一個數據庫可以包含一個或多個已命名的schema，schema又包含表及其他數據庫對象，包括數據類型、函數、操作符等。同一對象名可以在不同的schema中使用而不會引起沖突,和數據庫不同，schema不是嚴格分離的。數據庫創建初始，默認具有一個名為public的Schema，默認情況下，所有角色都擁有在public模式上的USAGE權限，一般建議普通用戶不可以在public模式上的CREATE權限。如果普通用戶具有

19、在public模式上的CREATE權限則建議通過如下語句撤銷該權限postgres=#REVOKE CREATE ON SCHEMA public FROM PUBLIC;n如何授權？1.用戶如果要創建模式需要有CREATE ON DATABASE權限postgres=#grant create on database db_name to jack;2.模式屬主表示模式的擁有者，擁有模式的所有權限postgres=#alter schema myschema owner to jack3.用戶只能訪問屬于自己的schema中的數據庫對象，即使用的權限postgres=#GRANT USAGE

20、 ON schema myschema TO jack;可以設置search_path配置參數指定尋找對象可用schema的順序執行如下命令將搜索路徑設置為myschema、public，首先搜索myschemaSET SEARCH_PATH TO myschema,public;n schema權限/2025.01.04/4.PG權限管理20n 表的權限表是建立在數據庫中的，在不同的數據庫中可以存放相同的表，甚至可以通過使用不同模式在同一個數據庫中創建相同名稱的表。n如何授權？1.創建表需要有CREATE ON SCHEMA的權限postgres=#GRANT USAGE,CREATE ON

21、 schema myschema TO jack;2.修改表、查看表首先需要有USAGE的權限postgres=#GRANT USAGE ON schema myschema TO jack;postgres=#GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA myschema TO jack;3.需要針對數據表的特定列做訪問控制，實現針對用戶的列級別的訪問控制。GRANT SELECT(customer_sk,first_name)ON customer TO jack;postgres=#c-jackpostgres=#se

22、lect customer_sk,first_name from customer;customer_sk|first_name-+-2345|Grace(1 row)postgres=#select*from customer;ERROR:permission denied for table customer/2025.01.04/4.PG權限管理21表空間是一個目錄，可以存在多個，里面存儲的是它所包含的數據庫的各種物理文件。由于表空間是一個目 錄，僅是起到了物理隔離的作用，其管理功能依賴于文件系統。PG自帶了兩個表空間,即默認表空間pg_default共享表空間pg_globaln如何授

23、權？1.表空間屬主，表示表空間的擁有者postgres=#CREATE TABLESPACE mytbs RELATIVE LOCATION tablespace/mytbs;postgres=#alter tablespace mytbs owner to jack;2.在表空間中創建對象,需要以下權限postgres=#GRANT CREATE ON TABLESPACE mytbs TO jack;查詢表空間權限檢查pg_tablespace系統表，如下命令可查到系統和用戶定義的全部表空間/2025.01.04/22n 權限插件1.set_user：PostgreSQL權限控制(acl)

24、增強插件，解決細粒度安全控制需求https:/ SYSTEM命令-set_user.block_alter_system=打開阻止復制程序命令-set_user.block_copy_program=on阻止SET log_statement命令-set_user.block_log_statement=on允許角色列表升級為超級用戶-set_user.super_whitelist=，2.pg_permissions：如果你想比較一下當前分配的權限是否是你所想要的權限，pg_permissions就是為了解決這些問題而設計的。pg_permissions擴展會在數據庫中創建8個視圖，1張表，

25、一個函數postgres=#dv*permissions提供查詢用戶在數據庫，模式，表，視圖，列，函數以及序列對象上的權限3.pg_restrict：訪問控制權限 精細化配置，用于限制PostgreSQL上的一些SQL命令。它引入了類似于超級用戶的主角色概念。即使是超級用戶也可以被禁止刪除數據庫和角色。pg_restrict.nonremovable_databases（string）：將此處列出的DROP數據庫限制為主角色（即使當前角色是數據庫所有者或超級用戶）。默認值為postgres、template1、template0。pg_restrict.nonremovable_roles（string）：將此處列出的DROP角色限制為主角色（即使當前角色具有CREATEROLE權限或是超級用戶）。默認值為postgres。4.PG權限管理THANK YOU18110961771 CONTACT INFORMATION公眾號：IT邦德