快来学PostgreSQL用户角色与权限
Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：PGCM、OCP、YCP
Skill：Oracle、Mysql、PostgreSQL、国产数据库
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）
业务范围：数据库安装部署、日常维护、主备切换、故障处理、性能优化、技术培训等。
需要的伙伴或者商业合作请移步 公众号【呆呆的私房菜】获取联系方式。
阅读本文可以了解到PostgreSQL用户与角色相关的内容，包括概念、权限管理等内容。
01 用户角色概述
02 用户角色管理
2.1 创建用户、角色
## 1. 系统命令行创建
createuser username with option xxx;
## 2. 在psql命令行创建用户
create user username with option xxx;
option可以是：
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT #角色是其他角色的成员，这些子句决定新角色是否从那些角色中“继承”特权
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS #决定是否一个角色可以绕过每一条行级安全性（RLS）策略。
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...] #ADMIN子句与ROLE相似，让它们能够把这个角色中的成员关系授予给其他人。
| USER role_name [, ...]
| SYSID uid
## 在psql命令行创建角色
create role rolename with option xxx;
option可以是：
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
基本•语法
支持参数
参数含义
create user UserName
superuser / nosuperuser
该用户是否为超级用户，缺省否
createdb / nocreatedb
该用户是否能创建数据库，缺省不能
createrole / nocreaterole
该用户是否能创建新角色，缺省不能
createuser / nocreateuser
该用户是否能创建新用户，缺省不能
inherit / noinherit
该用户是否集成父角色的权限，缺省不能
login / nologin
该用户是否能登陆数据库，缺省能
replication / noreplication
该用户是否能作为流复制用户，缺省不能
bypassrls / nobypassrls
该用户能否绕过行级安全策略，缺省否
connection limit
该用户的最大并发连接数，缺省-1无限制
[encrypted / noencrypted] password 'passwords'
该用户的密码，及是否加密存储在系统表里
valid until
该用户的密码失效时间
in role
该用户所属的父角色
in group
同in role，但属于过去式语法
role
该用户作为角色时，所拥有的子成员
admin
同role，但具有with admin option权限
user
同role，但术语过去式语法
sysid UID
该选项主要是为了向下兼容
2.2 查看用户、角色
## 显示用户和角色属性
\du
postgres=# \d pg_roles
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | C | |
oid | oid | | |
postgres=# \d pg_user
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
2.3 修改用户、角色
## 修改用户、角色属性
alter role username with option;
## 重命名
alter user oldname rename to newname;
## 设置用户参数
alter user username set parametername to value;
## 重置配置参数
alter user username reset parametername / all;
2.4 删除用户、角色
## 1. 系统命令行删除
dropuser username
## 2.在psql命令行删除
drop user username;
drop role rolename;
drop role if exists role_name;
## 注意：
## 1、只用超级用户能够删除超级用户
## 2、只有具有createrole权限的用户能删除非超级用户
## 3、删除用户前，需要先删除依赖该用户的对象、权限等信息
## 4、任何属于该组角色的对象都必须先被删除或者将对象的所有者赋予其它角色，任何赋予该组角色的权限也都必须被撤消。
## 5、删除组role只会删除组的role本身，组的成员并不会被删除
2.5 预定义角色
角色
描述
pg_read_all_data
读取所有数据（表、视图、序列），拥有对象SELECT权限，对所有架构拥有USAGE权限一样，即使没有明确的权限。此角色没有设置角色属性BYPASSRLS。如果正在使用RLS，管理员可能希望在授予此角色的角色上设置BYPASSRLS。
pg_write_all_data
写入所有数据（表、视图、序列），就好像对这些对象具有INSERT、UPDATE和DELETE权限，对所有架构具有USAGE权限一样，即使没有明确的权限。此角色没有设置角色属性BYPASSRLS。如果正在使用RLS，管理员可能希望在授予此角色的角色上设置BYPASSRLS。
pg_read_all_settings
读取所有配置变量，即使是那些通常只对超级用户可见的变量。
pg_read_all_stats
读取所有pg_stat_*视图并使用各种与统计信息相关的扩展，即使是那些通常只对超级用户可见的扩展。
pg_stat_scan_tables
执行监视函数，这些函数可能会在很长一段时间内对表使用ACCESS SHARE锁。
pg_monitor
读取/执行各种监视视图和功能。此角色是pg_read_all_settings、pg_read_all_stats和pg_stat_scan_tables的成员。
pg_database_owner
无。成员身份隐含地由当前数据库所有者组成。
pg_signal_backend
用信号通知另一个后端取消查询或终止其会话。
pg_read_server_files
允许使用COPY和其他文件访问功能从数据库可以访问的服务器上的任何位置读取文件。
pg_write_server_files
允许使用COPY和其他文件访问功能写入数据库可以在服务器上访问的任何位置的文件。
pg_execute_server_program
允许以用户身份在数据库服务器上执行程序数据库与COPY和其他允许执行服务器端程序的功能一样运行。
pg_checkpoint
允许执行checkpoint命令。
pg_use_reserved_connections
允许使用通过reserved_connections保留的连接插槽。
pg_create_subscription
允许对数据库具有create权限的用户发出create subscription。
03 权限管控
任何数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限。
数据库中所有权限都和角色挂钩，PostgreSQL权限分为系统权限和对象权限。
3.1 实例权限
## 配置pg_hba.conf限制用户登录
cat $PGDATA/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
3.2 数据库权限
特权
**描述 **
connect
允许用户连接到特定的数据库
create
授予在数据库创建新对象的能力
temporary
允许创建临时表
默认情况下，CONNECT权限被授予PUBLIC角色（所有用户）。但是，不能直接从单个用户撤销此权限。相反，您需要从PUBLIC角色中撤销它，然后将它单独授予其他需要它的用户。
revoke connect on database dbname from public;
3.3 schema权限
PostgreSQL数据库可以由一个或多个模式组成，其中包含表和其他对象，如视图、数据类型、函数、过程和运算符。
不同的模式可以具有相同名称的对象，而不会发生冲突。例如，schema1和schema2都可以包含名为table1的表。
与数据库不同，模式不是严格分开的。用户可以访问他们所连接的数据库中任何模式中的对象，前提是他们拥有必要的权限。
grant usage on schema schemaname to username;
grant create on schema schemaname to username;
grant all on schema schemaname to username;
3.4 object权限
权限
描述
select
允许从表中提取数据
insert
允许向表中添加新行
update
修改表中的现有行
delete
授予从表中删除行的功能
references
允许创建外键约束
trigger
允许在表上创建触发器
truncate
允许在表上使用truncate
## 授予增删改权限
grant insert,update,delete on table tablename to username;
## 授予单个表上的所有权限
grant all on table tablename to username;
## 授权schema上所有表的所有权限
grant all on all tables in schema schemaname to username;
关于授权的语法格式如下：
postgres=# \h grant
Command: GRANT
Description: define access privileges
Syntax:
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 ]
[ GRANTED BY role_specification ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT role_name [, ...] TO role_specification [, ...]
[ WITH ADMIN OPTION ]
[ GRANTED BY role_specification ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER