PostgreSQL Auth

Authentication is about identity verification and block/allow list

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)