前言
資料庫的使用者眾多,權限控管是個難題,用 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?
建立一個名為
read_only_role
的 Role 。CREATE ROLE read_only_role;
給 Role 連線到目標資料庫的權限。
GRANT CONNECT ON DATABASE <db-name> TO read_only_role;
因為 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; ...
由於是 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; ...
而 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; ...
完成後,用下方的 sql 來看一下這個 Role 底下的權限。
SELECT grantee, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'read_only_role';
接著,建立一個要用的 user,然後把剛設定好的 Role 綁給他。
CREATE USER <username> WITH PASSWORD '<your-password>'; GRANT read_only_role TO <username>;
最後,用下方 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';