全部產品
Search

搜尋本產品

    ApsaraDB RDS：RDS PostgreSQL許可權管理最佳實務

    文件中心

    ApsaraDB RDS：RDS PostgreSQL許可權管理最佳實務

    更新時間：Jun 19, 2024

    本文介紹如何對RDS PostgreSQL執行個體中的帳號許可權進行精細化管理。

    配置原則

    建立角色（Role）作為許可權集合，對許可權管理的細分操作集中在Role上。Role沒有登入許可權。在Role的基礎上建立user帳號來作為登入帳號使用。使用user = role + login許可權的帳號許可權管理模型，當Role的許可權變化時，user帳號許可權會自動跟隨Role變化。

    許可權管理設計模型

    該模型簡單有效，推薦絕大多數客戶使用。

    • 1個RDS PostgreSQL高許可權帳號，具有所有許可權，由少量資深DBA掌握。
    • 專案或者團隊維度，1個資源owner帳號，2個基本Role：{project}_role_readwrite、{project}_role_readonly。
      說明 如果有更多細分需求，可以根據需要建立Role。
    • 在Role的基礎上建立業務帳號：業務帳號user = role + login許可權。
    • 1個專案或團隊可以有多個Schema。許可權分配盡量以Schema或Role為單位。
    • 業務表請勿放到schema public中。因為PostgreSQL預設所有使用者對schema public都有CREATE和USAGE許可權。

    許可權規劃樣本

    本樣本以專案維度進行許可權管理樣本，同樣也適用於團隊維度。
    • DBA擁有RDS PostgreSQL執行個體的高許可權帳號，名稱是dbsuperuser。
    • 商務專案名稱是rdspg，建立schema名稱是rdspg、rdspg_1。
    專案中新增的資源owner帳號和Role規劃如下：
    user/Roleschema中表許可權schema中預存程序許可權
    rdspg_owner (user)，是唯一的專案資源owner帳號
    • DDL：CREATE、DROP、ALTER
    • DQL：SELECT
    • DML：UPDATE、INSERT、DELETE
    • DDL：CREATE、DROP、ALTER
    • DQL：SELECT，調用預存程序
    rdspg_role_readwrite (role)
    • DQL：SELECT
    • DML：UPDATE、INSERT、DELETE
    		DQL（SELECT，調用預存程序） ，若預存程序有DDL操作，會拋出許可權相關錯誤。
    rdspg_role_readonly (role)DQL(SELECT) DQL（SELECT，調用預存程序），若預存程序有DDL或者DML操作，會拋出許可權相關錯誤。
    新增業務帳號時，根據不同需求，採用如下管理員模式建立：
    • rdspg_readwrite = rdspg_role_readwrite + login許可權
    • rdspg_readonly = rdspg_role_readonly + login許可權

    配置步驟

    1. 建立專案資源owner帳號rdspg_owner和專案Role。
      DBA使用dbsuperuser高許可權帳號執行如下操作。
      --- rdspg_owner 是專案管理帳號,此處密碼僅為樣本，請注意修改。
CREATE USER rdspg_owner WITH LOGIN PASSWORD 'asdfy181BASDfadasdbfas';

CREATE ROLE rdspg_role_readwrite;
CREATE ROLE rdspg_role_readonly;

--- 設定: 對於rdspg_owner 建立的表，rdspg_role_readwrite 有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）許可權。
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON TABLES TO rdspg_role_readwrite;

--- 設定: 對於rdspg_owner 建立的SEQUENCES，rdspg_role_readwrite 有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）許可權。
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON SEQUENCES TO rdspg_role_readwrite;

--- 設定: 對於 rdspg_owner 建立的表， rdspg_role_readonly 只有 DQL（SELECT）許可權。
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT SELECT ON TABLES TO rdspg_role_readonly;
    2. 建立rdspg_readwrite、rdspg_readonly業務帳號。
      DBA使用dbsuperuser高許可權帳號執行如下操作。
      --- rdspg_readwrite只有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）許可權。
CREATE USER rdspg_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
GRANT rdspg_role_readwrite TO rdspg_readwrite;

--- rdspg_readonly只有 DQL（SELECT）許可權。
CREATE USER rdspg_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
GRANT rdspg_role_readonly TO rdspg_readonly;
    3. 建立schema rdspg，並授權給專案Role。
      DBA使用dbsuperuser高許可權帳號執行如下操作。
      --- schema rdspg的owner是 rdspg_owner帳號
CREATE SCHEMA rdspg AUTHORIZATION rdspg_owner;

--- 授權ROLE相關SCHEMA存取權限。
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readonly;
      說明 rdspg_readwrite和rdspg_readonly自動繼承了相關Role的許可權變更，不需要再額外操作。

    應用情境樣本

    情境1：使用rdspg_owner帳號：對schema rdspg中的表進行DDL（CREATE、DROP、ALTER）操作
    CREATE TABLE rdspg.test(id bigserial primary key, name text);
CREATE INDEX idx_test_name on rdspg.test(name);

    情境2：使用 rdspg_readwrite/rdspg_readonly 帳號進行業務開發

    業務開發遵循最小許可權原則，盡量使用rdspg_readonly帳號，需要DML操作的地方才使用rdspg_readwrite帳號。這樣也方便在業務層做讀寫分離。
    說明
    • 業務層做讀寫分離，避免了自動讀寫分離中介軟體proxy帶來的額外成本和效能損耗。
    • 即使目前還沒有使用唯讀執行個體，也建議區分 readonly用戶端、readwrite用戶端，為使用唯讀執行個體做準備。readonly用戶端建議使用readonly帳號，最小許可權原則，規避許可權誤用。
      • readonly用戶端，使用readonly帳號，設定JDBC URL：唯讀執行個體1地址,唯讀執行個體2地址,讀寫執行個體地址。
      • readwrite用戶端，使用readwrite帳號，設定JDBC URL：讀寫執行個體地址。
    • 使用rdspg_readwrite帳號，對schema rdspg中的表進行DQL（SELECT）、DML（UPDATE、INSERT、DELETE）操作：
      INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
SELECT id,name FROM rdspg.test LIMIT 1;

--- rdspg_readwrite沒有 DDL（CREATE、DROP、ALTER）許可權
CREATE TABLE rdspg.test2(id int);
ERROR:  permission denied for schema rdspg
LINE 1: create table rdspg.test2(id int);

DROP TABLE rdspg.test;
ERROR:  must be owner of table test

ALTER TABLE rdspg.test ADD id2 int;
ERROR:  must be owner of table test

CREATE INDEX idx_test_name on rdspg.test(name);
ERROR:  must be owner of table test
    • 使用rdspg_readonly帳號，對schema rdspg中的表進行DQL（SELECT）操作：
      INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
ERROR:  permission denied for table test

SELECT id,name FROM rdspg.test LIMIT 1;
 id | name
----+-------
  1 | name0
(1 row)

    情境3：不同專案交叉授權

    如果有另外1個專案employee，需求為帳號employee_readwrite增加rdspg專案的表唯讀許可權。DBA使用dbsuperuser高許可權帳號做如下操作：
    --- 給帳號 employee_readwrite 加上 rdspg_role_readonly 許可權集合。
GRANT rdspg_role_readonly TO employee_readwrite;

    情境4：專案新增 schema rdspg_2，並授權給專案Role

    rdspg_readwrite、rdspg_readonly、employee_readwrite帳號自動繼承了相關Role的許可權變更，不需要再額外操作。DBA使用dbsuperuser 高許可權帳號做如下操作：
    CREATE SCHEMA rdspg_1 AUTHORIZATION rdspg_owner;

--- 授權ROLE相關SCHEMA存取權限。
--- CREATE 使得 rdspg_role_admin 對schema rdspg_1中的表有 DDL（CREATE、DROP、ALTER）許可權。
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readonly;

    帳號許可權查詢

    通過本文介紹的帳號許可權管理模型建立的帳號，可以通過如下方式查詢具體許可權資訊。
    • 使用PostgreSQL用戶端命令列終端串連RDS PostgreSQL資料庫，具體請參見串連PostgreSQL執行個體。然後使用\du命令查看：\du查看

      從上述查詢結果樣本中可以看出：employee_readwrite帳號的Member of列中，內容為rdspg_role_readonly,employee_role_readwrite，因此，此帳號對employee專案表具有DQL和DML許可權，對rdspg專案表具有DQL許可權。

    • 使用SQL查詢：
      SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;