背景

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 的权限

    1.  postgres=# create user admin createdb createrole login password 'pgsql';
    2.  CREATE ROLE
    3.  postgres=# \du
    4.                                List of roles
    5.    Role name  |                   Attributes                   | Member of 
    6.  -------------+------------------------------------------------+-----------
    7.   admin       | Create role, Create DB                         | {}
    8.   test        | Superuser                                      | {}

  2. 使用这个 admin 登陆 postgres，创建用于存放数据的DB

    1.  postgres=> create database dbadmin;
    2.  CREATE DATABASE

  3. 创建子账户subuser1 subuser2，并把他们的权限给admin

    1.  dbadmin=> create user subuser1 ;
    2.  CREATE ROLE
    3.  dbadmin=> create user subuser2 ;
    4.  CREATE ROLE
    5.  dbadmin=> grant subuser1 to admin;
    6.  GRANT ROLE
    7.  dbadmin=> grant subuser2 to admin;
    8.  GRANT ROLE

  4. 我们看到 admin 用户组管理了下列2个用户，也就是admin 拥有 subuser1，subuser2 的所有权限

    1.  dbadmin=> \du
    2.                                     List of roles
    3.    Role name  |                   Attributes                   |      Member of
    4.  -------------+------------------------------------------------+---------------------
    5.   admin       | Create role, Create DB                         | {subuser1,subuser2}
    6.   subuser1    |                                                | {}
    7.   subuser2    |                                                | {}

  5. 使用 subuser1 或 subuser2 登陆数据库 dbadmin，public schema上创建 owner 是自己的表对象和其他对象。分别用3个用户创建3张表a b c用作测试，另外也可以使用 admin 用户创建分别属于 subuser1 和 subuser2 的 schema ，再创建 table

    1.  dbadmin=> \d
    2.             List of relations
    3.   Schema |    Name    | Type  |  Owner
    4.  --------+------------+-------+----------
    5.   public | t_admin    | table | admin
    6.   public | t_subuser1 | table | subuser1
    7.   public | t_subuser2 | table | subuser2

  6. 使用 admin 用户，可以对这3张表做管理，例如:

    1.  dbadmin=> select user;
    2.   current_user
    3.  --------------
    4.   admin
    5.  (1 row)
    6.  dbadmin=> drop table t_admin,t_subuser1,t_subuser2;
    7.  DROP TABLE

  7. 但是 subuser1 无法删除 t_admin 和 t_subuser2，同理 subuser2 也无法删除 t_admin 和 t_subuser1

    1.  dbadmin=> select user;
    2.   current_user
    3.  --------------
    4.   subuser1
    5.  (1 row)
    6.  dbadmin=> drop table t_admin;
    7.  ERROR:  must be owner of relation t_admin
    8.  dbadmin=> drop table t_subuser1;
    9.  DROP TABLE
    10.  dbadmin=> drop table t_subuser2;
    11.  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

  1.  dbadmin=> select user;
  2.   current_user
  3.  --------------
  4.   subuser1
  5.  (1 row)
  6.  dbadmin=> \d
  7.             List of relations
  8.   Schema |    Name    | Type  |  Owner
  9.  --------+------------+-------+----------
  10.   public | t_admin    | table | subuser1
  11.   public | t_subuser1 | table | subuser2
  12.   public | t_subuser2 | table | subuser1
  13.  (3 rows)
  14.  dbadmin=> alter table t_admin owner to admin;
  15.  ERROR:  must be member of role "admin"
  16.  dbadmin=> alter table t_subuser1 owner to subuser1;
  17.  ERROR:  must be owner of relation t_subuser1
  18. ```

  1. 最后，提一个完成上述功能的假设，admin 用户默认具有 INHERIT 权限，INHERIT 权限决定一个角色是否“继承”它所在组的角色的权限。一个带有 INHERIT 属性的角色可以自动使用已经赋与它直接或间接所在组的任何权限。没有 INHERIT，其它角色的成员关系只赋与该角色 SET ROLE 成其它角色的能力；其它角色的权限只是在这么做了之后才能获得。如果没有声明，缺省是 INHERIT。通过系统表， pg_roles 可以获得该用户的权限信息

    1.  dbadmin=> select rolname,rolinherit from pg_roles ;
    2.     rolname   | rolinherit
    3.  -------------+------------
    4.   subuser1    | t
    5.   subuser2    | t
    6.   admin       | t

总结

使用 PG 用户组可以做到使用一个 PG 用户组管理所在组内的所有其他普通用户和他们的对象。RDS 用户设置一个用户组，既可以管理多个用户的对象，又可以实现一定程度上的权限隔离，同时权限又不会过大，推荐在云上使用该方式管理自己的数据库。

如果要实现更加细粒度的权限控制，则需要使用 grant 和 revoke 语句，请参考官方sql-createrole 和 sql-grant。

原文：http://mysql.taobao.org/monthly/2015/11/03/