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_monitor
的dborle_readony
角色,部分监控系统功能会不可用。
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.
最后修改 2022-05-27: init commit (1e3e284)