PostgreSQL Role System

Pigsty default role system and ACL demands

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

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

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.

1. - { name: dbrole_readonly  , login: false , comment: role for global read-only access  }                            # production read-only role
2. - { name: dbrole_offline ,   login: false , comment: role for restricted read-only access (offline instance) }      # restricted-read-only role
3. - { name: dbrole_readwrite , login: false , roles: [dbrole_readonly], comment: role for global read-write access }  # production read-write role
4. - { 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:

1. - { name: postgres , superuser: true , comment: system superuser }                             # system dbsu, name is designated by `pg_dbsu`
2. - { name: dbuser_dba , superuser: true , roles: [dbrole_admin] , comment: system admin user }  # admin dbsu, name is designated by `pg_admin_username`
3. - { name: replicator , replication: true , bypassrls: true , roles: [pg_monitor, dbrole_readonly] , comment: system replicator }                   # replicator
4. - { 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.

1. pg_dbsu: postgres                             # os user for the database

3. # - system roles - #
4. pg_replication_username: replicator           # system replication user
5. pg_replication_password: DBUser.Replicator    # system replication password
6. pg_monitor_username: dbuser_monitor           # system monitor user
7. pg_monitor_password: DBUser.Monitor           # system monitor password
8. pg_admin_username: dbuser_dba                 # system admin user
9. 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.

Each variable is an array consisting of the following rules.

1. - title: allow intranet admin password access
2. role: common
3. rules:
4. - host    all     +dbrole_admin               10.0.0.0/8          md5
5. - host    all     +dbrole_admin               172.16.0.0/12       md5
6. - 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

1. #==============================================================#
2. # Default HBA
3. #==============================================================#
4. # allow local su with ident"
5. local   all             postgres                               ident
6. local   replication     postgres                               ident

8. # allow local user password access
9. local   all             all                                    md5

11. # allow local/intranet replication with password
12. local   replication     replicator                              md5
13. host    replication     replicator         127.0.0.1/32         md5
14. host    all             replicator         10.0.0.0/8           md5
15. host    all             replicator         172.16.0.0/12        md5
16. host    all             replicator         192.168.0.0/16       md5
17. host    replication     replicator         10.0.0.0/8           md5
18. host    replication     replicator         172.16.0.0/12        md5
19. host    replication     replicator         192.168.0.0/16       md5

21. # allow local role monitor with password
22. local   all             dbuser_monitor                          md5
23. host    all             dbuser_monitor      127.0.0.1/32        md5

25. #==============================================================#
26. # Extra HBA
27. #==============================================================#
28. # add extra hba rules here

30. #==============================================================#
31. # primary HBA
32. #==============================================================#

34. #==============================================================#
35. # special HBA for instance marked with 'pg_offline_query = true'
36. #==============================================================#

38. #==============================================================#
39. # Common HBA
40. #==============================================================#
41. #  allow meta node password access
42. host    all     all                         10.10.10.10/32      md5

44. #  allow intranet admin password access
45. host    all     +dbrole_admin               10.0.0.0/8          md5
46. host    all     +dbrole_admin               172.16.0.0/12       md5
47. host    all     +dbrole_admin               192.168.0.0/16      md5

49. #  allow intranet password access
50. host    all             all                 10.0.0.0/8          md5
51. host    all             all                 172.16.0.0/12       md5
52. host    all             all                 192.168.0.0/16      md5

54. #  allow local read/write (local production user via pgbouncer)
55. local   all     +dbrole_readonly                                md5
56. host    all     +dbrole_readonly           127.0.0.1/32         md5

58. #==============================================================#
59. # Ad Hoc HBA
60. #===========================================================

Change HBA Rules

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

1. ./pgsql.yml -t pg_hba    # Specify the target cluster with -l
2. 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:

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

1. pgbouncer_hba_rules:                          # pgbouncer host-based authentication rules
2. - title: local password access
3. role: common
4. rules:
5. - local  all          all                                     md5
6. - host   all          all                     127.0.0.1/32    md5

8. - title: intranet password access
9. role: common
10. rules:
11. - host   all          all                     10.0.0.0/8      md5
12. - host   all          all                     172.16.0.0/12   md5
13. - host   all          all                     192.168.0.0/16  md5

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