- [前言](#前言) - [步驟](#步驟) - [備註](#備註) - [移除權限](#移除權限) - [幫 Role 改名](#幫-role-改名) - [刪除 Role](#刪除-role) - [查找某個 Role 有哪些 User 有](#查找某個-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?](https://dba.stackexchange.com/questions/192529/how-can-i-grant-permissions-on-tables-created-in-the-future-to-members-of-a-part) --- 1. 建立一個名為 `read_only_role` 的 Role 。 ```sql CREATE ROLE read_only_role; ``` 2. 給 Role 連線到目標資料庫的權限。 ```sql GRANT CONNECT ON DATABASE TO read_only_role; ``` 3. 因為 Postgres 在 Database 和 Table 間還有一層 Schema，所以也要給 Role 使用 Schema 的權限。 ```sql GRANT USAGE ON SCHEMA TO read_only_role; GRANT USAGE ON SCHEMA TO read_only_role; ... ``` 4. 由於是 View Only 的需求，給予 `SELECT` 的權限，不同 Schema 要個別設定，下方的範例為給予權限撈同個 Schema 底下的 Table。 ```sql GRANT SELECT ON ALL TABLES IN SCHEMA TO read_only_role; GRANT SELECT ON ALL TABLES IN SCHEMA TO read_only_role; ... ``` 5. 而 Table 可能未來會有新的，因此也要加權限給未來的 Table。 ```sql ALTER DEFAULT PRIVILEGES IN SCHEMA GRANT SELECT ON TABLES TO read_only_role; ALTER DEFAULT PRIVILEGES IN SCHEMA GRANT SELECT ON TABLES TO read_only_role; ... ``` 6. 完成後，用下方的 sql 來看一下這個 Role 底下的權限。 ```sql SELECT grantee, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'read_only_role'; ``` 7. 接著，建立一個要用的 user，然後把剛設定好的 Role 綁給他。 ```sql CREATE USER WITH PASSWORD ''; GRANT read_only_role TO ; ``` 8. 最後，用下方 sql 來確認是否綁定成功。 ```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` 來拔掉。 ```sql REVOKE USAGE ON SCHEMA FROM read_only_role; REVOKE SELECT ON ALL TABLES IN SCHEMA general FROM read_only_role CASCADE; ``` ### 幫 Role 改名 ```sql ALTER ROLE read_only_role RENAME TO new_name; ``` ### 刪除 Role ```sql DROP ROLE read_only_role; ``` ### 查找某個 Role 有哪些 User 有 ```sql 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](https://www.postgresql.org/docs/current/sql-createrole.html)

iterm2 tips

tab

split