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.

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

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)