PostgreSQL Role System

Pigsty default role system and ACL demands

Pigsty’s default role system contains four default roles and four default users：

Default Roles

Pigsty has four default roles：

• Read-only role (dbrole_readonly): Has read-only access to all data tables.
• Read-write role (dbrole_readwrite): Has to write access to all data tables, inherits dbrole_readonly.
• Admin role (dbrole_admin): Can execute DDL changes, inherits dbrole_readwrite.
• Offline role (dbrole_offline): A special read-only role for executing slow queries/ETL/interactive queries, only allowed access to specific instances.

The definition is shown below.

- { 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

Common users should not change the name of the default role.

Default Users

Pigsty has four default users.

• superuser (postgres), the owner and creator of the database, the same as the OS user.
• Replication user (replicator), the system user used for primary-replica.
• Monitor user (dbuser_monitor), a user used to monitor database and connection pool metrics.
• Admin user (dbuser_dba), the admin user who performs daily operations and database changes.

The definitions are shown below:

- { 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

In Pigsty, four important default usernames and passwords are controlled and managed by separate parameters.

pg_dbsu: postgres                             # os user for the 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

It is not recommended to set a password or allow remote access for the default superuser postgres, so there is no dedicated dbsu_password option. If there is such a need, you can set a password for the dbsu in pg_default_roles.

Be sure to change the passwords of all default users.

In addition, users can define cluster-specific business users in pg_users in the same way as pg_default_roles.

It is recommended to remove the dborle_readony role from dbuser_monitor if there is a higher data security requirement. Some of the monitoring system features will not be available.

Authentication

Pigsty uses md5 password authentication by default and provides access control based on the PostgreSQL HBA mechanism.

HBA(Host Based Authentication)can be treated as an IP blocklist and allowlist.

Config: HBA

In Pigsty, the HBA of all instances is generated from the config file, and HBA rules vary depending on the instance’s role (pg_role). The following variables control pigsty’s HBAs.

• pg_hba_rules: Environmentally uniform HBA rules
• pg_hba_rules_extra: HBA rules for a specific instance or cluster
• pgbouncer_hba_rules: HBA rules used for connection pooling
• pgbouncer_hba_rules_extra: HBA rules for a specific instance or cluster connection pooling

Each variable is an array consisting of the following rules.

- title: allow intranet admin password access
  role: common
  rules:
    - host    all     +dbrole_admin               10.0.0.0/8          md5
    - host    all     +dbrole_admin               172.16.0.0/12       md5
    - host    all     +dbrole_admin               192.168.0.0/16      md5

Role-Based HBA

The HBA rule set with role = common is installed to all instances,(role: primary) are only installed to instances with pg_role = primary.

As a special case, the HBA rule for the role: offline will be installed to instances with pg_role == 'offline' as well as to instances with pg_offline_query == true.

The rendering priority rules for HBA are:

• hard_coded_rules Global hard-coded rules
• pg_hba_rules_extra.common Cluster common rules
• pg_hba_rules_extra.pg_role Cluster role rules
• pg_hba_rules.pg_role Global role rules
• pg_hba_rules.offline Cluster offline rules
• pg_hba_rules_extra.offline Global offline rules
• pg_hba_rules.common Global common rules

Default HBA Rules

Under the default config, the primary and replica will use the following HBA rules:

• Superuser access with local OS auth.
• Other users can access it with a password from local.
• Replica users can access via password from the LAN segment.
• Monitor users can access it locally.
• Everyone can access it with a password on the meta node.
• Admin users can access via password from the LAN.
• Everyone can access the intranet with a password.
• Read and write users (production business users) can be accessed locally (Connection Pool).
• On the replica: read-only users (individuals) can access from the local (Connection Pool).
• On instances with pg_role == 'offline' or with pg_offline_query == true, HBA rules that allow access to dbrole_offline grouped users are added.

Default HBA rule information

#==============================================================#
# Default HBA
#==============================================================#
# allow local su with ident"
local   all             postgres                               ident
local   replication     postgres                               ident
# allow local user password access
local   all             all                                    md5
# allow local/intranet replication with password
local   replication     replicator                              md5
host    replication     replicator         127.0.0.1/32         md5
host    all             replicator         10.0.0.0/8           md5
host    all             replicator         172.16.0.0/12        md5
host    all             replicator         192.168.0.0/16       md5
host    replication     replicator         10.0.0.0/8           md5
host    replication     replicator         172.16.0.0/12        md5
host    replication     replicator         192.168.0.0/16       md5
# allow local role monitor with password
local   all             dbuser_monitor                          md5
host    all             dbuser_monitor      127.0.0.1/32        md5
#==============================================================#
# Extra HBA
#==============================================================#
# add extra hba rules here
#==============================================================#
# primary HBA
#==============================================================#
#==============================================================#
# special HBA for instance marked with 'pg_offline_query = true'
#==============================================================#
#==============================================================#
# Common HBA
#==============================================================#
#  allow meta node password access
host    all     all                         10.10.10.10/32      md5
#  allow intranet admin password access
host    all     +dbrole_admin               10.0.0.0/8          md5
host    all     +dbrole_admin               172.16.0.0/12       md5
host    all     +dbrole_admin               192.168.0.0/16      md5
#  allow intranet password access
host    all             all                 10.0.0.0/8          md5
host    all             all                 172.16.0.0/12       md5
host    all             all                 192.168.0.0/16      md5
#  allow local read/write (local production user via pgbouncer)
local   all     +dbrole_readonly                                md5
host    all     +dbrole_readonly           127.0.0.1/32         md5
#==============================================================#
# Ad Hoc HBA
#===========================================================

Change HBA Rules

Users can modify and apply the new HBA rules through a playbook after the cluster/instance is created and running.

./pgsql.yml -t pg_hba    # Specify the target cluster with -l
bin/reloadhba <cluster>  # Reload the HBA rules

When the database cluster directory is destroyed and rebuilt, the new copy will have the same HBA rules as the cluster primary. You can use the above command to perform HBA repair for a specific instance.

Pgbouncer HBA

In Pigsty, Pgbouncer also uses HBA for access control. The usage is the same as Postgres HBA:

• pgbouncer_hba_rules: HBA rules used by the connection pool
• pgbouncer_hba_rules_extra: Instance- or cluster-specific connection pooling HBA rules

The default Pgbouncer HBA rules allow password access from local and intranet.

pgbouncer_hba_rules:                          # pgbouncer host-based authentication rules
  - title: local password access
    role: common
    rules:
      - local  all          all                                     md5
      - host   all          all                     127.0.0.1/32    md5
  - title: intranet password access
    role: common
    rules:
      - host   all          all                     10.0.0.0/8      md5
      - host   all          all                     172.16.0.0/12   md5
      - host   all          all                     192.168.0.0/16  md5

Last modified 2022-06-04: fii en docs batch 2 (61bf601)