对于数据库来说，用户以及权限管理非常重要，让不用的用户具有不同的权限集合，将会显著的避免误操作。本章内容将会介绍 PostgreSQL 中的用户和权限管理机制。
通常来说，角色是一系列权限的集合，在 PostgreSQL中，我们可以认为一个用户就是一个角色，也就是说，我们可以将一个用户的权限赋予给另外一个用户。
用户管理
1.创建用户
rudonx=# create user rudonx1 password 'ASDFasdf=2020';
CREATE ROLE
2.修改用户，这里以修改密码举例
rudonx=# alter user rudonx1 with password 'QWERqwer=2020';
ALTER ROLE
3.删除用户
rudonx=# drop user rudonx1;
DROP ROLE
需要注意的是，在删除用户时必须要保证所有对象的所有者非删除的用户，否则会有如下报错：
rudonx=# drop user rudonx1;
ERROR: role "rudonx1" cannot be dropped because some objects depend on it
DETAIL: 1 object in database rudonx1
同时，如果我们之前赋予过一些权限给这个用户，在删除的时候，也会有如下报错：
rudonx1=# drop user rudonx1;
ERROR: role "rudonx1" cannot be dropped because some objects depend on it
DETAIL: privileges for database rudonx
总结来说，需要注意如下几点：
- 删除用户之前，需要预先删除依赖该用户的对象和权限。
- 只有具有 create role 权限的用户才能删除非超级用户。
关于用户管理的更多信息，您可以参考文档[1]。
PostgreSQL 的权限是从如下几个层次来进行划分的：
- 创建数据库的权限和特殊的超级用户权限( RDS for PostgreSQL 未向用户开放超级管理员权限)
- 数据库中 schema 的权限,如创建 schema 等操作
- schema 中对象的权限，比如创建表等操作
- 其次是表级别权限，如 增删改查
- 最后是表中某些字段的权限
下面我们通过具体的示例进行展示：
1.向用户添加创建 schema 的权限，表明用户可以在 rudonx 这个 database 中 进行创建 schema 的操作：
rudonx=# grant create on database rudonx to rudonx1;
GRANT
2.向用户赋予创建表的权限，表明用户可以在 public schema 中创建表，这里需要使用 grant 语法[2]：
rudonx=# grant create on schema public to rudonx1;
GRANT
3.赋予用户查询 owner 为其他用户的表的权限
rudonx=# grant select on table test to rudonx1;
GRANT
如果想赋予 public 下所有表的查询权限，可以使用如下语句：
rudonx=# grant select on all tables in schema public to rudonx1;
GRANT
4.查看特定用户所具有的表级权限
rudonx=# select table_catalog,table_schema,table_name , privilege_type from information_schema.table_privileges where grantee='rudonx1';
table_catalog | table_schema | table_name | privilege_type
---------------+--------------+------------+----------------
rudonx | public | rudonx | SELECT
rudonx | public | test1 | SELECT
rudonx | public | test | SELECT
rudonx | public | t1 | SELECT
(4 rows)
5.查看用户具有的 schema 级别的权限
rudonx=# SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
Name | Owner | Access privileges | Description
---------+---------+-------------------+------------------------
public | rudonx | rudonx=UC/rudonx +| standard public schema
| | =UC/rudonx +|
| | rudonx1=C/rudonx |
rudonx1 | rudonx1 | |
(2 rows)
6.查看用户具有的数据库级别的权限
rudonx=# SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END as "Size",
t.spcname as "Tablespace",
pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
输出如下：
-[ RECORD 2 ]-----+-------------------------------------------
Name | rudonx
Owner | rudonx
Encoding | UTF8
Collate | en_US.utf-8
Ctype | en_US.utf-8
Access privileges | =Tc/rudonx +
| rudonx=CTc/rudonx +
| rudonx1=C/rudonx
Size | 8261 kB
Tablespace | pg_default
Description |
7.最后让我们来看下如何回收权限，回收权限使用 revoke 语法[3]：
rudonx=# revoke select on table test1 from rudonx1;
REVOKE
对于 grant 和 revoke 来说，简单的可以简化为如下两个命令:
GRANT some_privileges ON database_object_type object_name TO role_name;
REVOKE some_privileges ON database_object_type object_name FROM role_name;
[1] https://www.postgresql.org/docs/11/sql-createuser.html
[2]https://www.postgresql.org/docs/11/sql-grant.html
[3] https://www.postgresql.org/docs/11/sql-revoke.html
如果您有其他问题，欢迎您联系火山引擎技术支持服务
