PostgreSQL 权限管理

Bing@DBA

已于 2023-07-06 14:58:15 修改

阅读量6.2k

点赞数 2

分类专栏： PostgreSQL 文章标签： postgresql 数据库 mysql

于 2022-06-21 11:31:22 首次发布

本文链接：https://blog.csdn.net/qq_42768234/article/details/125387966

版权

PostgreSQL 专栏收录该内容

13 篇文章 5 订阅

订阅专栏

文章目录

说明

权限管理是所有数据库都绕不开的话题，PG 中使用 角色（role）机制来处理用户身份认证。
本篇文章介绍如何管理 PostgreSQL 中的 角色用户组角色。

角色管理

角色是一系列权限的集合，为了管理方便，通常将一系列权限授予给角色，然后再把角色授予给有需要的用户。常见关系型数据库都支持角色，MySQL 在 8.0 版本也引入了 Role。角色与用户

1. 角色与用户

使用 CREATE USER 与 CREATE ROLE分别可以创建 用户和 角色 除了使用 CREATE ROLE创建出现的用户没有 LOGIN 权限外，两者没有其它区别。

-- 创建角色
create role knock PASSWORD '1234';

-- 尝试登录
$ psql -U knock -W '1234' -d db1
psql: error: could not connect to server: FATAL:  role "knock" is not permitted to log in

-- 创建用户
create user knock_user PASSWORD '12345';

-- 尝试登录
$ psql -U knock_user -W '1234' -d db1
psql (12.2)
Type "help" for help.

db1=>

2. 创建角色

创建角色的语法 👇 PostgreSQL - Create Role

CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ 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 [, ...]
    | SYSID uid

可以使用下方 SQL 查询现有的角色，或者使用 psql 中的 \du：

SELECT rolname FROM pg_roles;

          rolname          
---------------------------
 postgres
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend

其中 postgres 是系统初始化数据库时创建的默认角色，它是一个超级用户，类比 MySQL 中的 root。其它多出的角色，是系统预定义的角色，用于提供针对一些特定的常用特权和信息的访问权限。每个预定义角色的说明可参考：官方文档。

3. 角色属性

角色可以拥有属性，属性确定了角色拥有的特权，并且在登录时与客户端认证系统进行交互。常见的角色属性包括：

登录权限：只有具有 LOGIN属性的角色才能连接数据，具有 LOGIN属性的角色也可以被看作一个 “数据库用户”。

CREATE ROLE name LOGIN;
CREATE USER name;

超级用户：数据库超级用户可以避开所有的权限检查，只验证登录权限。因此，这是一个很危险的特权，使用时要特别小心，最好在生产环境的日常操作中避开使用超级用户。

CREATE ROLE name SUPERUSER;

创建数据库：只有明确授权的角色才能够创建数据库，超级用户除外。

CREATE ROLE name CREATEDB;

创建角色：只有明确授权的角色才能够超级角色。of course, 超级用户除外。

CREATE ROLE name CREATEROLE;

启动复制：只有明确授权的角色才能够启动流复制，用于流复制的角色还需要拥有 LOGIN特权。使用下方命令可以创建可用于流复制的角色

CREATE ROLE name REPLICATION LOGIN;

密码： 数据库连接时需要指定验证的密码，可以使用下方命令创建：

-- 创建用户并指定密码
CREATE ROLE name PASSWORD 'YouPassword';
-- 创建用并指定密码，且设置密码的过期时间
CREATE ROLE tony WITH LOGIN PASSWORD 'YouPassword' VALID UNTIL '2023-01-01';

一个角色被创建后，可以通过 ALTER ROLE 语句修改它的属性。具体可参考 官方文档 ALTER ROLE。

postgres=# CREATE ROLE admin CREATEDB CREATEROLE;
CREATE ROLE
postgres=# \du admin
List of roles
Role name |              Attributes              | Member of 
-----------+--------------------------------------+-----------
admin     | Create role, Create DB, Cannot login | {}

postgres=# ALTER ROLE admin NOCREATEROLE;
ALTER ROLE
postgres=# \du admin
List of roles
Role name |       Attributes        | Member of 
-----------+-------------------------+-----------
admin     | Create DB, Cannot login | {}

postgres=#

对象授权

1. 语法介绍

PostgreSQL 通过 GRANT 语句进行数据库对象授权，授权语法👇 详细可参考官方文档。

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

其中role_specification可以是：

[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

上方授权语法可以简化为：

GRANT some_privileges ON database_object_type object_name TO role_name;

-- 回收权限的语法类似
REVOKE some_privileges ON database_object_type object_name TO role_name;

some_privileges 表示在该数据库对象中的权限，例如 INSERT``UPDATE``DELETE 诸如此类。
database_object_type** **表示数据库的对象类型，如 TABLE SEQUENCE``SCHEMA等。

2. 对象权限

下面介绍 some_privileges中的各类权限**：**

权限名称	使用说明
SELECT	对于表和视图来说，表示允许查询，对于序列来说，表示允许使用 currval 函数。
INSERT	表示允许向特定的表中插入数据，拥有此权限，也表示允许使用 `COPY FROM`语句向表中插入数据。
UPDATE	对于表来说，表示允许更新表中数据。想要使用 SELECT…FOR `UPDATE` or `SHARE` 语句，也需要该权限，
DELETE	允许删除表中数据。
TRUNCATE	表示允许在表中执行 TRUNCATE 操作。
REFERENCES	创建外键约束时，需要参照列和被参照列，都拥有该权限。该权限可以授予表中所有列，或者其中一个列。
TRIGGER	允许在表中创建触发器。
CREATE	对于数据库来说，表示创建 SCHEMA 权限；
对于模式来说，表示创建数据库对象权限；
对于表空间来说，表示允许将表、索引创建到此表空间，或者移动到此表空间。
CONNECT	表示用户连接到指定数据库
TEMPORARY	表示允许使用指定数据库，使用临时表。
EXECUTE	允许调用函数或过程，包括使用在函数之上实现的任何运算符。这是适用于函数和过程的唯一权限类型。
USAGE	对于程序语言，允许使用语言来创建该语言的函数。这是适用于过程语言的唯一权限类型。
对于模式，允许访问模式中包含的对象（假设对象自己的权限要求也已得到满足）。从本质上讲，这允许受让者“look up”模式中的对象。如果没有此权限，仍可以看到对象名称，例如通过查询系统目录。此外，在撤消此权限后，现有会话可能还具有以前执行过此查找的语句，因此这不是阻止对象访问的彻底安全的方法。
对于序列, 允许使用currval 和 nextval 函数.
对于类型和域，允许在创建表、函数和其他模式对象时使用类型或域。（注意，此权限不控制类型的全部 “usage” ，例如查询中出现的类型的值。它仅防止创建依赖于类型的对象。此权限的主要目的是控制哪些用户可以对类型创建依赖项，这可能会防止所有者以后更改类型。）
对于外部数据包装器，允许使用外部数据包装器创建新服务器。
对于外部服务器，允许使用服务器创建外部表。受让者还可以创建、更改或删除与该服务器关联的自己的用户映射。
ALL PRIVILEGES	表示授予所有的权限。

上方介绍了 PG 中的对象权限，授权与回收语法相似，在此不多介绍。详细可参考 PostgreSQL Privileges。

角色成员

在现实的环境中，管理员通常需要管理大量的用户和对象权限。为了便于权限管理，减少复杂度，可以将用户进行分组，然后以组为单位进行权限的授予和撤销操作。为此，PostgreSQL 引入了组 (group) 角色的概念。一般不应授予组角色登录权限，因为它是作为其他角色的容器而存在的。

1. 组角色管理

可以使用以下 SQL 创建组角色：

PS：这里可以看到跟 Role 创建方法没有区别，为什么叫组角色呢？
拥有 LOGIN 权限的角色等价于用户，当角色或者用户，被其它用户继承，那么这个被继承的角色称为 “组角色”。

CREATE ROLE fantasy INHERIT;

请注意 INHERIT 的用法，它表示组角色 fantasy的任何一个成员角色都将自动继承除 “超级用户权限” 外的所有权限。出于安全考虑 PostgreSQL 不允许超级用户权限通过继承的方式传递。如果不显式指定，默认也有 INHERIT的效果，但为了清晰可见，如果是计划用组角色建议添加上。

**如果希望角色禁止被授权其它角色，“成为组角色” 可以使用 ****NOINHERIT**关键字。

角色授予：
将 knock 角色授予给用户 fantasy：

GRANT knock TO fantasy;

将 knock 角色收回：

REVOKE knock FROM wenruo;

2. SET ROLE

前面介绍过，有些权限是无法被继承的，例如 SUPERUSER超级用户权限，此时可以使用 SET ROLE 语法，来实现 “冒名顶替” 其组角色的身份，从而获得父角色所拥有的SUPERUSER权限，仅限当前会话存续期间有效。请看下方案例：

-- 创建一个拥有超级用户的角色
CREATE ROLE super_loe SUPERUSER;
-- 创建一个用户，设定密码
create user test;
alter test PASSWORD '112233';
-- 将 super_loe 授予 test
grant super_loe to test;

我们使用 test 登录到 PG 尝试查一张表：

db1=> \du test
            List of roles
 Role name | Attributes |  Member of  
-----------+------------+-------------
 test      |            | {super_loe}
 
db1=> select * from pgbench_accounts;
ERROR:  permission denied for table pgbench_accounts

super_loe 拥有超级权限，可以忽略任何权限检查，从测试中可以看到 test 没有继承 super_loe 的 SUPERUSER 权限。

db1=> set role super_loe;
SET
db1=# 
db1=# select * from pgbench_accounts limit 1;
 aid | bid | abalance |                                        filler                                        
-----+-----+----------+--------------------------------------------------------------------------------------
   1 |   1 |        0 |                                                                                     
(1 row)

db1=# SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 test         | super_loe
(1 row)

我们使用 set role 语法，将 test 切换到了 super_loe 此时也拥有了 SUPERUSER 权限。
使用 SET ROLE 可以在角色组内，进行用户切换：

SET ROLE 无需超级权限，即可执行；
一个具备 SUPER 权限的角色，可以切换到任何用户；
SET ROLE 会修改 current_user bian 变量的值；

创建只读账号

在 PostgreSQL 没有推出 ALTER DEFAULT PRIVILEGES 特性前，即使一次性把所有的表授权给一个用户，但是后面有新建对象的话，该只读账号没有新建对象的权限。下面 ALTER DEFAULT PRIVILEGES 语法：

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

1. 授权案例

创建业务账号有 db1 public 下所有表的读写权限：

-- 创建用户
create user op_rw password '123';
-- 读写权限
grant SELECT, UPDATE, DELETE on ALL tables in schema public to op_rw;

登陆 op_rw 账号，现在业务使用它创建业务表：

create table t1(id int primary key);

              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | t1               | table | op_rw

此时另外一个 Bi 业务组有需求，要 db1 数据库的只读权限：

-- 创建用户
create user bi_ro password '123';

-- 读写权限
grant SELECT, UPDATE, DELETE on ALL tables in schema public to bi_ro;

此时 bi_ro 是可以访问 db1 public 下的所有表，但是业务组新建的表 bi_ro 是无权访问：

-- 登陆 op_rw
psql -U op_rw -p 5432 -h 127.0.0.1 -d db1

-- 建表
create table t2(id int primary key);

此时使用 bi_ro 账号查询：

-- 登陆 bi_ro
psql -U bi_ro -p 5432 -h 127.0.0.1 -d db1
-- 查询 t2
select * from t2;
ERROR:  permission denied for table t2

因为 t2 是授权后创建的对象 bi_ro 当前无权访问，使用 op_rw 执行 default privileges：

alter default privileges for user op_rw in schema public grant select on tables to bi_ro;

再创建一张表：

create table t3(id int primary key);

切换 bi_ro 账号查询 t3 表：

db1=> select * from t3;
 id 
----
(0 rows)

可以使用 \ddp 查询授权关系：

db1=> \ddp
           Default access privileges
  Owner   | Schema | Type  | Access privileges 
----------+--------+-------+-------------------
 op_rw    | public | table | bi_ro=r/op_rw

总结

PostgreSQL 中的角色很灵活，创建一个角色赋予登录权限，它可以被称为 “用户”，创建一个角色并将其授予给其它用户，这个被授予角色，称为 组角色。
PostgreSQL 中的权限分为两类，一种是创建角色时指定的 角色属性，可以使用 ALTER 语句管理，另外一种则是 PRIVILEGES对象权限，使用 GRANT 语句来管理。