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

• 前言
• 步驟
• 備註
  • 移除權限
  • 幫 Role 改名
  • 刪除 Role
  • 查找某個 Role 有哪些 User 有
• 參考資料

前言

資料庫的使用者眾多，權限控管是個難題，用 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