Managing PostgreSQL HBA Rules

Quick Start

Pigsty uses declarative management: first define HBA rules in the inventory, then use bin/pgsql-hba <cls> to refresh.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_hba_rules:                            # <--- Define HBA rules here!
      - {user: dbuser_app, db: app, addr: intra, auth: pwd, title: 'app access'}
      - {user: dbuser_api, db: all, addr: world, auth: ssl, title: 'api ssl access'}

• Script
• Playbook
• Example

bin/pgsql-hba <cls>              # Refresh PostgreSQL and Pgbouncer HBA rules for cluster
bin/pgsql-hba <cls> <ip>...      # Refresh HBA rules for specific instances

./pgsql.yml -l <cls> -t pg_hba,pg_reload                 # Refresh PostgreSQL HBA only
./pgsql.yml -l <cls> -t pgbouncer_hba,pgbouncer_reload   # Refresh Pgbouncer HBA only
./pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload  # Refresh both

bin/pgsql-hba pg-meta                      # Refresh pg-meta cluster HBA rules
bin/pgsql-hba pg-meta 10.10.10.10          # Refresh specific instance only
bin/pgsql-hba pg-meta 10.10.10.11 10.10.10.12  # Refresh multiple instances

For rule syntax, see HBA Configuration. For authentication methods, default boundaries, and credential management, see Authentication.

Refresh HBA Rules

After modifying HBA rules in pigsty.yml, re-render config files and reload services.

• Script
• Playbook
• Example

bin/pgsql-hba <cls>              # Refresh entire cluster HBA (PostgreSQL + Pgbouncer)
bin/pgsql-hba <cls> <ip>...      # Refresh specific instances (multiple IPs space-separated)

./pgsql.yml -l <cls> -t pg_hba,pg_reload                 # Refresh PostgreSQL HBA only
./pgsql.yml -l <cls> -t pgbouncer_hba,pgbouncer_reload   # Refresh Pgbouncer HBA only
./pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload  # Refresh both

bin/pgsql-hba pg-meta                      # Refresh pg-meta cluster
bin/pgsql-hba pg-meta 10.10.10.10          # Refresh 10.10.10.10 instance only

Result: Renders PostgreSQL and Pgbouncer HBA config files based on inventory definitions, then reloads services to apply.

Config file locations

Related Tags

Verify HBA Rules

After refreshing HBA rules, verify config is correctly applied.

View current HBA rules

• SQL
• Bash
• Test Connection

-- View PostgreSQL HBA rules (recommended)
TABLE pg_hba_file_rules;

-- View matching rules for specific database
SELECT * FROM pg_hba_file_rules WHERE database @> ARRAY['mydb']::text[];

# View PostgreSQL HBA config file
cat /pg/data/pg_hba.conf

# View Pgbouncer HBA config file
cat /etc/pgbouncer/pgb_hba.conf

# View config file header (confirm if updated)
head -20 /pg/data/pg_hba.conf

# Test connection for specific user from specific address
psql -h <host> -p 5432 -U <user> -d <database> -c "SELECT 1"

# Test connection through Pgbouncer
psql -h <host> -p 6432 -U <user> -d <database> -c "SELECT 1"

Check HBA config syntax

# Reload config (validates syntax)
psql -c "SELECT pg_reload_conf()"

# If syntax errors, check logs
tail -f /pg/log/postgresql-*.log

Common Scenarios

Add New HBA Rule

Add rule to cluster config’s pg_hba_rules, then refresh:

pg-meta:
  vars:
    pg_hba_rules:
      - {user: new_user, db: new_db, addr: '192.168.1.0/24', auth: pwd, title: 'new app access'}

bin/pgsql-hba pg-meta

Emergency IP Block

When detecting malicious IP, add high-priority (order: 0) deny rule:

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

bin/pgsql-hba pg-meta    # Refresh immediately

Role-Based Rules

Configure different HBA rules for primary and replica using role parameter:

pg_hba_rules:
  # Only primary allows write users
  - {user: writer, db: all, addr: intra, auth: pwd, role: primary, title: 'writer on primary'}
  # Replicas allow read-only users
  - {user: reader, db: all, addr: world, auth: ssl, role: replica, title: 'reader on replica'}

After refresh, rules auto-enable/disable based on instance’s pg_role.

Refresh HBA After Expansion

When cluster adds new instances, rules using addr: cluster need refresh to include new members:

./pgsql.yml -l 10.10.10.14       # Add new instance
bin/pgsql-hba pg-meta            # Refresh all instances' HBA (includes new member IPs)

Refresh HBA After Failover

After Patroni failover, instance pg_role may not match config. If HBA rules use role filtering, update config and refresh:

# Update role definitions in pigsty.yml then refresh
bin/pgsql-hba pg-meta

Troubleshooting

Connection Rejected

Symptom: FATAL: no pg_hba.conf entry for host "x.x.x.x", user "xxx", database "xxx"

Steps:

1. Check current HBA rules, confirm if matching rule exists:

psql -c "TABLE pg_hba_file_rules"

2. Confirm client IP, username, database matches any rule

3. Check rule order (HBA uses first-match-wins)

4. Add corresponding rule and refresh:

bin/pgsql-hba <cls>

Authentication Failed

Symptom: FATAL: password authentication failed for user "xxx"

Steps:

1. Confirm password is correct
2. Check password encryption method (pg_pwd_enc) compatibility with client
3. Check if user exists:

SELECT * FROM pg_roles WHERE rolname = 'xxx';

HBA Rules Not Applied

Steps:

1. Confirm refresh command was executed
2. Check if Ansible execution succeeded
3. Confirm PostgreSQL reloaded:

psql -c "SELECT pg_reload_conf()"

4. Check if config file was updated:

head -20 /pg/data/pg_hba.conf

Rule Order Issues

HBA uses first-match-wins. If rules not working as expected:

1. Check order values in rule definitions
2. Use psql -c "TABLE pg_hba_file_rules" to view actual order
3. Adjust order values (lower numbers = higher priority)

Pgbouncer HBA

Pgbouncer HBA management is similar to PostgreSQL, with some differences.

Config differences

Refresh Pgbouncer HBA

• Script
• Playbook
• View

bin/pgsql-hba <cls>    # Refresh both PostgreSQL and Pgbouncer

./pgsql.yml -l <cls> -t pgbouncer_hba,pgbouncer_reload   # Refresh Pgbouncer HBA only

cat /etc/pgbouncer/pgb_hba.conf    # View Pgbouncer HBA rules

Best Practices

1. Always manage in config files: Don’t edit pg_hba.conf directly - all changes through pigsty.yml
2. Test environment first: HBA changes can cause connection issues - verify in test env first
3. Use order for priority: Blocklist rules use order: 0 to ensure priority matching
4. Refresh promptly: Refresh HBA after adding/removing instances or failover
5. Principle of least privilege: Only open necessary access - avoid addr: world + auth: trust
6. Monitor auth failures: Watch for auth failures in pg_stat_activity
7. Backup config: Backup pigsty.yml before important changes

Related Documentation

• HBA Configuration: HBA rule config syntax and parameter details
• User Management: User and role management operations
• Access Control: Role system and permission model
• Authentication: Authentication methods, default boundaries, and credential management
• Encrypted Communication: TLS and client certificate verification