PgSQL · 答疑解惑 · PostgreSQL 用戶組權限管理 · 數據庫內核月報

## 背景 RDS上的PG沒有開放超級用戶，這給很多云上的客戶使用PG帶來了困難。因此有必要給大家講講PG的用戶權限管理的一些小知識，它可以很好的幫助用戶順利的從之前的 DB 管理方式過度到云上。 1. PG 的 superuser 擁有幾乎全部的數據庫權限，甚至可以直接修改系統表，潛在風險相當大； 2. RDS PG 使用 superuser 運維 DB，例如管理用、管理流復制、備份等，這些操作用戶是不需要關心的，換句話說它應該完全的交給云服務來處理； 3. 對于用戶而言，PG 的普通用戶權限是完全夠用的。使用普通用戶，可以管理自己在云上的數據（對表進行DDL、DML、創建修改和管理其他數據庫對象）。之前的 PG 用戶習慣使用 superuser 進行日常運維，一方面 superuser 完全不用做任何的授權，使用方便；另一方面這樣的操作帶來了潛在的風險，容易誤操作。最近碰到了較多的用戶反饋，沒有了超級用戶，無法使用 RDS 的普通用戶，管理其他普通用戶創建的對象，經常出現操作對象無權限的問題，尤其存在多用戶的情況下。通常情況下： 1. 一個普通用戶只能在自己 owner 的DB下創建 schema； 2. DB 下的對象有一個所屬的 schema，普通用戶可以在 public 下創建對象（如 table），在其他 schema 下創建對象需要 schema 的 owner 是當前用戶或特別的授權； 3. 管理一個數據庫對象，需要是超級用戶、或對象的 owner 是當前用戶（在用戶組權限之外）。很多用戶，使用了很多個 user 在 public 模式下創建了多張表，但是單個用戶卻沒有權限同時管理他們，更沒有權限切換他們的 owner 到一個統一的用戶下做統一處理，十分惱火。 ## 解決方法在這里，我們提供一種通用的方法，可以使用 PG 的用戶組和繼承特性，做到使用一個普通用戶管理多個其他用戶。 1. 使用 RDS 的根賬號創建一個用于管理數據庫和用戶的管理員賬號，他具有創建用戶和創建 DB 的權限 ~~~ postgres=# create user admin createdb createrole login password 'pgsql'; CREATE ROLE postgres=# \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------+----------- admin | Create role, Create DB | {} test | Superuser | {} ~~~ 2. 使用這個 admin 登陸 postgres，創建用于存放數據的DB ~~~ postgres=> create database dbadmin; CREATE DATABASE ~~~ 3. 創建子賬戶subuser1 subuser2，并把他們的權限給admin ~~~ dbadmin=> create user subuser1 ; CREATE ROLE dbadmin=> create user subuser2 ; CREATE ROLE dbadmin=> grant subuser1 to admin; GRANT ROLE dbadmin=> grant subuser2 to admin; GRANT ROLE ~~~ 4. 我們看到 admin 用戶組管理了下列2個用戶，也就是admin 擁有 subuser1，subuser2 的所有權限 ~~~ dbadmin=> \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------+--------------------- admin | Create role, Create DB | {subuser1,subuser2} subuser1 | | {} subuser2 | | {} ~~~ 5. 使用 subuser1 或 subuser2 登陸數據庫 dbadmin，public schema上創建 owner 是自己的表對象和其他對象。分別用3個用戶創建3張表a b c用作測試，另外也可以使用 admin 用戶創建分別屬于 subuser1 和 subuser2 的 schema ，再創建 table ~~~ dbadmin=> \d List of relations Schema | Name | Type | Owner --------+------------+-------+---------- public | t_admin | table | admin public | t_subuser1 | table | subuser1 public | t_subuser2 | table | subuser2 ~~~ 6. 使用 admin 用戶，可以對這3張表做管理，例如: ~~~ dbadmin=> select user; current_user -------------- admin (1 row) dbadmin=> drop table t_admin,t_subuser1,t_subuser2; DROP TABLE ~~~ 7. 但是 subuser1 無法刪除 t_admin 和 t_subuser2，同理 subuser2 也無法刪除 t_admin 和 t_subuser1 ~~~ dbadmin=> select user; current_user -------------- subuser1 (1 row) dbadmin=> drop table t_admin; ERROR: must be owner of relation t_admin dbadmin=> drop table t_subuser1; DROP TABLE dbadmin=> drop table t_subuser2; ERROR: must be owner of relation t_subuser2 ~~~ 8. 同理，可以使用 admin 用戶，重置這3張表的 owner 到3個用戶中的一個，而使用 subuser1 和 subuser2 則沒有權限操作，例如: ~~~ dbadmin=> select user; current_user -------------- admin (1 row) dbadmin=> alter table t_admin owner to subuser1; ALTER TABLE dbadmin=> alter table t_subuser2 owner to subuser1; ALTER TABLE dbadmin=> alter table t_subuser1 owner to subuser2; ALTER TABLE dbadmin=> select user; current_user -------------- subuser1 (1 row) dbadmin=> \d List of relations Schema | Name | Type | Owner --------+------------+-------+---------- public | t_admin | table | subuser1 public | t_subuser1 | table | subuser2 public | t_subuser2 | table | subuser1 (3 rows) dbadmin=> alter table t_admin owner to admin; ERROR: must be member of role "admin" dbadmin=> alter table t_subuser1 owner to subuser1; ERROR: must be owner of relation t_subuser1 ~~~ 9. 最后，提一個完成上述功能的假設，admin 用戶默認具有 INHERIT 權限，INHERIT 權限決定一個角色是否“繼承”它所在組的角色的權限。一個帶有 INHERIT 屬性的角色可以自動使用已經賦與它直接或間接所在組的任何權限。沒有 INHERIT，其它角色的成員關系只賦與該角色 SET ROLE 成其它角色的能力；其它角色的權限只是在這么做了之后才能獲得。如果沒有聲明，缺省是 INHERIT。通過系統表， pg_roles 可以獲得該用戶的權限信息 ~~~ dbadmin=> select rolname,rolinherit from pg_roles ; rolname | rolinherit -------------+------------ subuser1 | t subuser2 | t admin | t ~~~ ## 總結使用 PG 用戶組可以做到使用一個 PG 用戶組管理所在組內的所有其他普通用戶和他們的對象。RDS 用戶設置一個用戶組，既可以管理多個用戶的對象，又可以實現一定程度上的權限隔離，同時權限又不會過大，推薦在云上使用該方式管理自己的數據庫。如果要實現更加細粒度的權限控制，則需要使用 grant 和 revoke 語句，請參考官方[sql-createrole](http://www.postgresql.org/docs/9.5/static/sql-createrole.html)?和?[sql-grant](http://www.postgresql.org/docs/9.5/static/sql-grant.html)。