PostgreSQL 用户及权限管理 - 文章 - 开发者社区

前言

对于数据库来说，用户以及权限管理非常重要，让不用的用户具有不同的权限集合，将会显著的避免误操作。本章内容将会介绍 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

如果您有其他问题，欢迎您联系火山引擎技术支持服务