HBA Rules

Detailed explanation of PostgreSQL and Pgbouncer Host-Based Authentication (HBA) rules configuration in Pigsty.

Overview

HBA (Host-Based Authentication) controls “who can connect to the database from where and how”. Pigsty manages HBA rules declaratively through pg_default_hba_rules and pg_hba_rules.

Pigsty renders the following config files during cluster init or HBA refresh:

Config File	Path	Description
PostgreSQL HBA	`/pg/data/pg_hba.conf`	PostgreSQL server HBA rules
Pgbouncer HBA	`/etc/pgbouncer/pgb_hba.conf`	Connection pool HBA rules

HBA rules are controlled by these parameters:

Parameter	Level	Description
`pg_default_hba_rules`	G	PostgreSQL global default HBA
`pg_hba_rules`	G/C/I	PostgreSQL cluster/instance add
`pgb_default_hba_rules`	G	Pgbouncer global default HBA
`pgb_hba_rules`	G/C/I	Pgbouncer cluster/instance add

Rule features:

Role filtering: Rules support role field, auto-filter based on instance’s pg_role
Order sorting: Rules support order field, controls position in final config file
Two syntaxes: Supports alias form (simplified) and raw form (direct HBA text)

Refresh HBA

After modifying config, re-render config files and reload services:

bin/pgsql-hba <cls>                   # Refresh entire cluster HBA (recommended)
bin/pgsql-hba <cls> <ip>...           # Refresh specific instances in cluster

Script executes the following playbook:

./pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true

PostgreSQL only: ./pgsql.yml -l <cls> -t pg_hba,pg_reload -e pg_reload=true

Pgbouncer only: ./pgsql.yml -l <cls> -t pgbouncer_hba,pgbouncer_reload

Don't edit config files directly

Don’t directly edit /pg/data/pg_hba.conf or /etc/pgbouncer/pgb_hba.conf - they’ll be overwritten on next playbook run. All changes should be made in pigsty.yml, then execute bin/pgsql-hba to refresh.

Parameter Details

pg_default_hba_rules

PostgreSQL global default HBA rule list, usually defined in all.vars, provides base access control for all clusters.

Type: rule[], Level: Global (G)

pg_default_hba_rules:
  - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident'  ,order: 100}
  - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' ,order: 150}
  - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: pwd   ,title: 'replicator replication from localhost',order: 200}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' ,order: 250}
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,title: 'replicator postgres db from intranet' ,order: 300}
  - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' ,order: 350}
  - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: pwd   ,title: 'monitor from infra host with password',order: 400}
  - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl'   ,order: 450}
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: ssl   ,title: 'admin @ everywhere with ssl & pwd'    ,order: 500}
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket',order: 550}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password'     ,order: 600}
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet',order: 650}

pg_hba_rules

PostgreSQL cluster/instance-level additional HBA rules, can override at cluster or instance level, merged with default rules and sorted by order.

Type: rule[], Level: Global/Cluster/Instance (G/C/I), Default: []

pg_hba_rules:
  - {user: app_user, db: app_db, addr: intra, auth: pwd, title: 'app user access'}

pgb_default_hba_rules

Pgbouncer global default HBA rule list, usually defined in all.vars.

Type: rule[], Level: Global (G)

pgb_default_hba_rules:
  - {user: '${dbsu}'    ,db: pgbouncer   ,addr: local     ,auth: peer  ,title: 'dbsu local admin access with os ident',order: 100}
  - {user: 'all'        ,db: all         ,addr: localhost ,auth: pwd   ,title: 'allow all user local access with pwd' ,order: 150}
  - {user: '${monitor}' ,db: pgbouncer   ,addr: intra     ,auth: pwd   ,title: 'monitor access via intranet with pwd' ,order: 200}
  - {user: '${monitor}' ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other monitor access addr' ,order: 250}
  - {user: '${admin}'   ,db: all         ,addr: intra     ,auth: pwd   ,title: 'admin access via intranet with pwd'   ,order: 300}
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other admin access addr'   ,order: 350}
  - {user: 'all'        ,db: all         ,addr: intra     ,auth: pwd   ,title: 'allow all user intra access with pwd' ,order: 400}

pgb_hba_rules

Pgbouncer cluster/instance-level additional HBA rules.

Type: rule[], Level: Global/Cluster/Instance (G/C/I), Default: []

Note: Pgbouncer HBA does not support db: replication.

Rule Fields

Each HBA rule is a YAML dict supporting these fields:

Field	Type	Required	Default	Description
`user`	string	No	`all`	Username, supports `all`, placeholders, `+rolename`
`db`	string	No	`all`	Database name, supports `all`, `replication`, db name
`addr`	string	Yes*	-	Address alias or CIDR, see Address Aliases
`auth`	string	No	`pwd`	Auth method alias, see Auth Methods
`title`	string	No	-	Rule description, rendered as comment in config
`role`	string	No	`common`	Instance role filter, see Role Filtering
`order`	int	No	`1000`	Sort weight, lower first, see Order Sorting
`rules`	list	Yes*	-	Raw HBA text lines, mutually exclusive with `addr`

Either addr or rules must be specified. Use rules to write raw HBA format directly.

Address Aliases

Pigsty provides address aliases to simplify HBA rule writing:

Alias	Expands To	Description
`local`	Unix socket	Local Unix socket
`localhost`	Unix socket + `127.0.0.1/32` + `::1/128`	Loopback addresses
`admin`	`${admin_ip}/32`	Admin IP address
`infra`	All infra group node IPs	Infrastructure nodes
`cluster`	All current cluster member IPs	Same cluster instances
`intra` / `intranet`	`10.0.0.0/8`, `172.16.0.0/12`, `192.168.0.0/16`	Intranet CIDRs
`world` / `all`	`0.0.0.0/0` + `::/0`	Any address (IPv4 + IPv6)
`<CIDR>`	Direct use	e.g., `192.168.1.0/24`

Intranet CIDRs can be customized via node_firewall_intranet:

node_firewall_intranet:
  - 10.0.0.0/8
  - 172.16.0.0/12
  - 192.168.0.0/16

Auth Methods

Pigsty provides auth method aliases for simplified config:

Alias	Actual Method	Connection Type	Description
`pwd`	`scram-sha-256` or `md5`	`host`	Auto-select based on `pg_pwd_enc`
`ssl`	`scram-sha-256` or `md5`	`hostssl`	Force SSL + password
`ssl-sha`	`scram-sha-256`	`hostssl`	Force SSL + SCRAM-SHA-256
`ssl-md5`	`md5`	`hostssl`	Force SSL + MD5
`cert`	`cert`	`hostssl`	Client certificate auth
`trust`	`trust`	`host`	Unconditional trust (dangerous)
`deny` / `reject`	`reject`	`host`	Reject connection
`ident`	`ident`	`host`	OS user mapping (PostgreSQL)
`peer`	`peer`	`local`	OS user mapping (Pgbouncer/local)

pg_pwd_enc defaults to scram-sha-256, can be set to md5 for legacy client compatibility.

User Variables

HBA rules support these user placeholders, auto-replaced with actual usernames during rendering:

Placeholder	Default	Corresponding Param
`${dbsu}`	`postgres`	`pg_dbsu`
`${repl}`	`replicator`	`pg_replication_username`
`${monitor}`	`dbuser_monitor`	`pg_monitor_username`
`${admin}`	`dbuser_dba`	`pg_admin_username`

Role Filtering

The role field in HBA rules controls which instances the rule applies to:

Role	Description
`common`	Default, applies to all instances
`primary`	Primary instance only
`replica`	Replica instance only
`offline`	Offline instance only (`pg_role: offline` or `pg_offline_query: true`)
`standby`	Standby instance
`delayed`	Delayed replica instance

Role filtering matches based on instance’s pg_role variable. Non-matching rules are commented out (prefixed with #).

pg_hba_rules:
  # Only applies on primary: writer can only connect to primary
  - {user: writer, db: all, addr: intra, auth: pwd, role: primary, title: 'writer only on primary'}

  # Only applies on offline instances: ETL dedicated network
  - {user: '+dbrole_offline', db: all, addr: '172.20.0.0/16', auth: ssl, role: offline, title: 'offline dedicated'}

Order Sorting

PostgreSQL HBA is first-match-wins, rule order is critical. Pigsty controls rule rendering order via the order field.

Order Interval Convention

Interval	Usage
`0 - 99`	User high-priority rules (before all defaults)
`100 - 650`	Default rule zone (spaced by 50 for insertion)
`1000+`	User rule default (rules without `order`)

PostgreSQL Default Rules Order

Order	Rule Description
100	dbsu local ident
150	dbsu replication local
200	replicator localhost
250	replicator intra replication
300	replicator intra postgres
350	monitor localhost
400	monitor infra
450	admin infra ssl
500	admin world ssl
550	dbrole_readonly localhost
600	dbrole_readonly intra
650	dbrole_offline intra

Pgbouncer Default Rules Order

Order	Rule Description
100	dbsu local peer
150	all localhost pwd
200	monitor pgbouncer intra
250	monitor world deny
300	admin intra pwd
350	admin world deny
400	all intra pwd

Syntax Examples

Alias Form: Using Pigsty simplified syntax

pg_hba_rules:
  - title: allow grafana view access
    role: primary
    user: dbuser_view
    db: meta
    addr: infra
    auth: ssl

Rendered result:

# allow grafana view access [primary]
hostssl  meta               dbuser_view        10.10.10.10/32     scram-sha-256

Raw Form: Using PostgreSQL HBA syntax directly

pg_hba_rules:
  - title: allow intranet password access
    role: common
    rules:
      - host all all 10.0.0.0/8 scram-sha-256
      - host all all 172.16.0.0/12 scram-sha-256
      - host all all 192.168.0.0/16 scram-sha-256

Rendered result:

# allow intranet password access [common]
host all all 10.0.0.0/8 scram-sha-256
host all all 172.16.0.0/12 scram-sha-256
host all all 192.168.0.0/16 scram-sha-256

Common Scenarios

Blacklist IP: Use order: 0 to ensure first match

pg_hba_rules:
  - {user: all, db: all, addr: '10.1.1.100/32', auth: deny, order: 0, title: 'block bad ip'}

Whitelist App Server: High priority for specific IP

pg_hba_rules:
  - {user: app_user, db: app_db, addr: '192.168.1.10/32', auth: ssl, order: 50, title: 'app server'}

Admin Force Certificate: Override default SSL password auth

pg_hba_rules:
  - {user: '${admin}', db: all, addr: world, auth: cert, order: 10, title: 'admin cert only'}

Offline Instance Dedicated Network: Only on offline instances

pg_hba_rules:
  - {user: '+dbrole_offline', db: all, addr: '172.20.0.0/16', auth: ssl-sha, role: offline, title: 'etl network'}

Restrict Access by Database: Sensitive databases limited to specific networks

pg_hba_rules:
  - {user: fin_user, db: finance_db, addr: '10.20.0.0/16', auth: ssl, title: 'finance only'}
  - {user: hr_user, db: hr_db, addr: '10.30.0.0/16', auth: ssl, title: 'hr only'}

Pgbouncer Dedicated Rules: Note no db: replication support

pgb_hba_rules:
  - {user: '+dbrole_readwrite', db: all, addr: world, auth: ssl, title: 'app via pgbouncer'}

Complete Cluster Example

pg-prod:
  hosts:
    10.10.10.11: {pg_seq: 1, pg_role: primary}
    10.10.10.12: {pg_seq: 2, pg_role: replica}
    10.10.10.13: {pg_seq: 3, pg_role: offline}
  vars:
    pg_cluster: pg-prod

    pg_hba_rules:
      # Blacklist: known malicious IP (highest priority)
      - {user: all, db: all, addr: '10.1.1.100/32', auth: deny, order: 0, title: 'blacklist'}

      # App server whitelist (high priority)
      - {user: app_user, db: app_db, addr: '192.168.1.0/24', auth: ssl, order: 50, title: 'app servers'}

      # ETL tasks: offline instances only
      - {user: etl_user, db: all, addr: '172.20.0.0/16', auth: pwd, role: offline, title: 'etl tasks'}

      # Cluster internal monitoring
      - {user: '${monitor}', db: all, addr: cluster, auth: pwd, order: 380, title: 'cluster monitor'}

    pgb_hba_rules:
      # App via connection pool
      - {user: '+dbrole_readwrite', db: all, addr: '192.168.1.0/24', auth: ssl, title: 'app via pgbouncer'}

Verification & Troubleshooting

View Current HBA Rules

psql -c "TABLE pg_hba_file_rules"         # View via SQL (recommended)
cat /pg/data/pg_hba.conf                  # View PostgreSQL HBA file
cat /etc/pgbouncer/pgb_hba.conf           # View Pgbouncer HBA file
grep '^#' /pg/data/pg_hba.conf | head -20 # View rule titles (verify order)

Test Connection Auth

psql -h <host> -p 5432 -U <user> -d <db> -c "SELECT 1"

Common Issues

Error Message	Possible Cause	Solution
`no pg_hba.conf entry for host...`	No matching HBA rule	Add corresponding rule and refresh
`password authentication failed`	Wrong password or enc	Check password and `pg_pwd_enc`
Rule not taking effect	Not refreshed or order	Run `bin/pgsql-hba`, check order

Important Notes

Order sensitive: PostgreSQL HBA is first-match-wins, use order wisely
Role matching: Ensure role field matches target instance’s pg_role
Address format: CIDR must be correct, e.g., 10.0.0.0/8 not 10.0.0.0/255.0.0.0
Pgbouncer limitation: Does not support db: replication
SSL prerequisite: Ensure SSL is configured before using ssl, cert auth
Test first: Validate in test environment before modifying HBA
Refresh on scale: Rules using addr: cluster need refresh after cluster membership changes

HBA Management: Daily HBA rule management operations
User Config: User and role configuration
Access Control: Role system and permission model
Security & Compliance: PostgreSQL cluster security features

Feedback

Was this page helpful?

Thanks for the feedback! Please let us know how we can improve.

Sorry to hear that. Please let us know how we can improve.

Last Modified 2026-02-14: docs(juice): refine JuiceFS config semantics and metric names (0510c97)