说明
权限管理是所有数据库都绕不开的话题,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
语句来管理。