在 Postgres 建立一個 Read Only Role(Create Read Only Role in Postgres)

前言

資料庫的使用者眾多,權限控管是個難題,用 Role 來決定使用者權限是很常被使用的方法,本篇紀錄如何建立一個 Read Only 的 Role ,然後綁到 User 身上。

在 Postgres 的世界中,可以用給 User 不同的 Role,而 Role 所綁定的權限同時也代表 User 可以做的事。

直接建立 User 然後給權限也能達到一樣的效果,但不是理想的做法,當使用者數量增長,不適合幫每個 User 做細節設定,此時如果使用 Role ,可以從 Role 的內容迅速知道 User 的權限,也可以輕鬆的綁定在其他 User 身上。

步驟

特別注意:如果希望擁有 Role A 的 User2, User3 ... UserN 都能對 User1 的未來 Table 擁有權限,在給 Role 權限時一定要用 User 1 來給。 參考資料:How can I grant permissions on tables created in the future to members of a particular role?


  1. 建立一個名為 read_only_role 的 Role 。

    CREATE ROLE read_only_role;
    
  2. 給 Role 連線到目標資料庫的權限。

    GRANT CONNECT ON DATABASE <db-name> TO read_only_role;
    
  3. 因為 Postgres 在 Database 和 Table 間還有一層 Schema,所以也要給 Role 使用 Schema 的權限。

    GRANT USAGE ON SCHEMA <schema-1> TO read_only_role;
    GRANT USAGE ON SCHEMA <schema-2> TO read_only_role;
    ...
    
  4. 由於是 View Only 的需求,給予 SELECT 的權限,不同 Schema 要個別設定,下方的範例為給予權限撈同個 Schema 底下的 Table。

    GRANT SELECT ON ALL TABLES IN SCHEMA <schema-1> TO read_only_role;
    GRANT SELECT ON ALL TABLES IN SCHEMA <schema-2> TO read_only_role;
    ...
    
  5. 而 Table 可能未來會有新的,因此也要加權限給未來的 Table。

    ALTER DEFAULT PRIVILEGES IN SCHEMA <schema-1> GRANT SELECT ON TABLES TO read_only_role;
    ALTER DEFAULT PRIVILEGES IN SCHEMA <schema-2> GRANT SELECT ON TABLES TO read_only_role;
    ...
    
  6. 完成後,用下方的 sql 來看一下這個 Role 底下的權限。

     SELECT grantee, table_name, privilege_type
     FROM information_schema.table_privileges
     WHERE grantee = 'read_only_role';
    
  7. 接著,建立一個要用的 user,然後把剛設定好的 Role 綁給他。

     CREATE USER <username> WITH PASSWORD '<your-password>';
     GRANT read_only_role TO <username>;
    
  8. 最後,用下方 sql 來確認是否綁定成功。

    SELECT
        r.rolname AS username,
        m.roleid AS roleid,
        r2.rolname AS rolename
    FROM
        pg_roles r
    JOIN
        pg_auth_members m ON r.oid = m.member
    JOIN
        pg_roles r2 ON m.roleid = r2.oid;
    

完成! 以後新增一個 User 而且需要同樣權限時,直接 GRANT 給他就行了。

備註

移除權限

如果不小心給錯權限,可以用 REVOKE 來拔掉。

REVOKE USAGE ON SCHEMA <schema-1> FROM read_only_role;
REVOKE SELECT ON ALL TABLES IN SCHEMA general FROM read_only_role CASCADE;

幫 Role 改名

ALTER ROLE read_only_role RENAME TO new_name;

刪除 Role

DROP ROLE read_only_role;

查找某個 Role 有哪些 User 有

SELECT 
    r.rolname AS username
FROM 
    pg_roles r
JOIN 
    pg_auth_members m ON r.oid = m.member
JOIN 
    pg_roles r2 ON m.roleid = r2.oid
WHERE 
    r2.rolname = 'read_only_role';

參考資料

Postgresql - Create Role

Tags:
# postgres
# database
# backend