PostgreSQL 角色体系

Pigsty的默认角色体系包含四个默认角色,以及四个默认用户,涵盖了基础的访问控制需求。

Pigsty的默认角色体系包含四个默认角色,以及四个默认用户

以下是Pigsty自带的8个默认用户/角色的定义

name attr roles desc
dbrole_readonly Cannot login role for global readonly access
dbrole_readwrite Cannot login dbrole_readonly role for global read-write access
dbrole_offline Cannot login role for restricted read-only access (offline instance)
dbrole_admin Cannot login
Bypass RLS
pg_monitor
pg_signal_backend
dbrole_readwrite
role for object creation
postgres Superuser
Create role
Create DB
Replication
Bypass RLS
system superuser
replicator Replication
Bypass RLS
pg_monitor
dbrole_readonly
system replicator
dbuser_monitor 16 connections pg_monitor
dbrole_readonly
system monitor user
dbuser_dba Bypass RLS
Superuser
dbrole_admin system admin user

默认角色

Pigsty带有四个默认角色:

  • 只读角色(dbrole_readonly):对所有数据表具有只读权限。
  • 读写角色(dbrole_readwrite):对所有数据表具有写入权限,继承dbrole_readonly
  • 管理角色(dbrole_admin):可以执行DDL变更,继承dbrole_readwrite
  • 离线角色(dbrole_offline):特殊只读角色,用于执行慢查询/ETL/交互查询,仅允许在特定实例上访问。

其定义如下所示

- { name: dbrole_readonly  , login: false , comment: role for global read-only access  }                            # production read-only role
- { name: dbrole_offline ,   login: false , comment: role for restricted read-only access (offline instance) }      # restricted-read-only role
- { name: dbrole_readwrite , login: false , roles: [dbrole_readonly], comment: role for global read-write access }  # production read-write role
- { name: dbrole_admin , login: false , roles: [pg_monitor, dbrole_readwrite] , comment: role for object creation } # production DDL change role

!> 不建议普通用户修改默认角色的名称

默认用户

Pigsty带有四个默认用户:

  • 超级用户(postgres),数据库的拥有者与创建者,与操作系统用户一致
  • 复制用户(replicator),用于主从复制的系统用户
  • 监控用户(dbuser_monitor),用于监控数据库与连接池指标的用户
  • 管理员(dbuser_dba),执行日常管理操作与数据库变更的管理员用户

其定义如下所示:

- { name: postgres , superuser: true , comment: system superuser }                             # system dbsu, name is designated by `pg_dbsu`
- { name: dbuser_dba , superuser: true , roles: [dbrole_admin] , comment: system admin user }  # admin dbsu, name is designated by `pg_admin_username`
- { name: replicator , replication: true , bypassrls: true , roles: [pg_monitor, dbrole_readonly] , comment: system replicator }                   # replicator
- { name: dbuser_monitor , roles: [pg_monitor, dbrole_readonly] , comment: system monitor user , parameters: {log_min_duration_statement: 1000 } } # monitor user

在Pigsty中,4个默认的重要用户的用户名和密码是由独立参数控制与管理的:

pg_dbsu: postgres                             # os user for database

# - system roles - #
pg_replication_username: replicator           # system replication user
pg_replication_password: DBUser.Replicator    # system replication password
pg_monitor_username: dbuser_monitor           # system monitor user
pg_monitor_password: DBUser.Monitor           # system monitor password
pg_admin_username: dbuser_dba                 # system admin user
pg_admin_password: DBUser.DBA                 # system admin password

出于安全考虑,不建议为默认超级用户postgres设置密码或允许远程访问,所以没有专门的dbsu_password选项。 如果有此类需求,可在pg_default_roles中为超级用户设置密码。

在生产环境使用时,请务必修改所有默认用户的密码

此外,用户可以在 pg_users 定义集群特定的业务用户,定义方式与 pg_default_roles 一致。

如果有较高数据安全需求,建议移除 dbuser_monitordborle_readony 角色,部分监控系统功能会不可用。


最后修改 2022-05-27: init commit (1e3e284)