02：PostgreSQL用户和权限

环境：

• 操作系统：CentOS 7.9 64bit
• PostgreSQL 版本：16.x 或 15.x
• 安装用户：postgres
• 软件安装目标路径：/usr/pgsql-<version>
• 数据库数据目录：/pgdata

用户和角色

• 数据库在初始化时，会自动初始化一个超级用户。该超级用户与初始化的操作系统用户名相同，通常为postgres。
• 数据库对象都有其owner属主。owner默认拥有所有权限，且无需授权。对对象alter和drop的权限为owner专属，不能grant授权别人。
• 在PostgreSQL中，用户与角色几乎是没有区别，可以等同使用。

创建用户或角色

--创建用户示例
CREATE USER user_zyp WITH CREATEDB CREATEROLE LOGIN PASSWORD '123456';

--语法格式
CREATE ROLE name [ [ WITH ] option [ ... ] ]
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:
      SUPERUSER | NOSUPERUSER  （默认NOSUPERUSER）
    | CREATEDB | NOCREATEDB  （默认NOCREATEDB）
    | CREATEROLE | NOCREATEROLE  （默认NOCREATEROLE）
    | INHERIT | NOINHERIT  （默认INHERIT）
    | LOGIN | NOLOGIN  （创建ROLE默认NOLOGIN，创建USER默认LOGIN）
    | REPLICATION | NOREPLICATION  （默认NOREPLICATION）
    | BYPASSRLS | NOBYPASSRLS  （默认NOBYPASSRLS）
    | CONNECTION LIMIT connlimit  （默认-1不限制）
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL 
    | VALID UNTIL 'timestamp' 
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...] 
    | USER role_name [, ...]

创建用户和角色唯一的区别是：创建用户默认有login权限，创建角色默认没有login权限。

权限管理

权限分为两类：

• 对于用户/角色权限（创建语句中option指定的权限），需要使用ALTER ROLE命令修改。
• 对于对象权限（用户/角色对某写对象的权限），需要使用GRANT和REVOKE进行赋权或收回。

赋权或收回命令通常的命令格式（不同类型略有不同，参见官方文档）：

--语句一般格式（不同类型略有不同）
GRANT some_privileges ON object_type object_name TO role_name  [ WITH GRANT OPTION ];
REVOKE some_privileges ON object_type object_name FROM role_name  [ WITH GRANT OPTION ];

--指定某类所有对象授权语句一般格式（不同类型略有不同）
GRANT some_privileges ON ALL 类型(复数) IN object_type object_name TO role_name  [ WITH GRANT OPTION ];
REVOKE some_privileges ON ALL 类型(复数) IN object_type object_name FROM role_name  [ WITH GRANT OPTION ];

其中some_privileges可以为：
   SELECT
   INSERT
   UPDATE
   DELETE
   TRUNCATE
   REFERENCES
   TRIGGER
   CREATE
   CONNECT
   TEMPORARY或TEMP
   EXECUTE
   USAGE
   ALL全部

其中role_name除了可为具体用户名/角色名外，还可以为PUBLIC表示所有用户

若grant ALL on DATABASE 数据库 to role_name不会自动把该数据库中schema、Table等对象授权给role_name

查看权限

查看用户/角色权限（创建语句中option指定的权限）：

--可用以下语句查询或使用\du+
SELECT * FROM pg_roles;

查看用户/角色对象权限（GRANT赋予的权限）：

SELECT owner
      ,relname as object_name,grantor,grantee
      ,CASE
         WHEN (t.privilege = 'r'::text) THEN 'SELECT'::text
         WHEN (t.privilege = 'a'::text) THEN 'INSERT'::text
         WHEN (t.privilege = 'd'::text) THEN 'DELETE'::text
         WHEN (t.privilege = 'w'::text) THEN 'UPDATE'::text
         WHEN (t.privilege = 'D'::text) THEN 'TRUNCATE'::text
         WHEN (t.privilege = 'X'::text) THEN 'EXECUTE'::text
         WHEN (t.privilege = 'x'::text) THEN 'REFERENCES'::text
         WHEN (t.privilege = 'U'::text) THEN 'USAGE'::text
         WHEN (t.privilege = 't'::text) THEN 'TRIGGER'::text
         WHEN (t.privilege = 'C'::text) THEN 'CREATE'::text
         WHEN (t.privilege = 'c'::text) THEN 'CONNECT'::text
         WHEN (t.privilege = 'T'::text) THEN 'TEMPORARY'::text
         ELSE t.privilege
        END AS privilege
  FROM (select CASE 
                 WHEN grantee IS NOT NULL AND grantee!=''
                   THEN has_table_privilege(grantee, oid, 'TRUNCATE')
                 ELSE NULL
               END AS TRUNCATE_privs
              ,regexp_split_to_table(privs, ''::text) AS privilege
              ,t_cls.* 
          from (select (regexp_split_to_array(unnest(relacl)::text,'=|/'))[1] as grantee
                      ,(regexp_split_to_array(unnest(relacl)::text,'=|/'))[2] as privs
                      ,(regexp_split_to_array(unnest(relacl)::text,'=|/'))[3] as grantor
                      ,relname
                      ,relacl
                      ,(select usename from pg_user where usesysid = c.relowner) as owner
                      ,(SELECT nspname FROM pg_namespace n WHERE n.oid = c.relnamespace) as nspname
                      ,c.oid
                  FROM pg_class c
                 WHERE 1=1 
                   AND relkind in ('r', 'S', 't', 'p','P')
                 order by owner,relname
        ) t_cls
      ) t;

查看某个表上哪些用户拥有什么权限：

SELECT * FROM information_schema.table_privileges 
WHERE table_name = 'your_table_name';

查看schema能被哪些访问：

\d+

其中UC字母分别表示：Usage、Create权限。