PostgreSQL pg_hba.conf Guide
Table of Contents
1. What is pg_hba.conf?
2. Authentication Methods in pg_hba.conf
3. Create Database and User
4. Contents of pg_hba.conf
5. Play with peer
6. Play with trust
7. Play with reject
8. Restrict by User
9. Restrict by Network
10. Verify logs
Location: usually inside PostgreSQL data directory (/var/lib/pgsql/<version>/data/pg_hba.conf or /pgData/pgsql15/data/pg_hba.conf depending on your setup). HBA = Host-Based Authentication. Format: # TYPE DATABASE USER ADDRESS METHOD TYPE: local, host, hostssl, hostnossl DATABASE: which DBs (e.g. all, postgres, mydb) USER : which roles (e.g. all, myuser) ADDRESS : client IP/CIDR (127.0.0.1/32, 192.168.2.0/24) METHOD : authentication method (trust, md5, scram-sha-256, peer, reject, cert) OPTIONS : optional settings (e.g., clientcert=1)
2. Authentication Methods in pg_hba.conf
trust: No password needed. Anyone who can connect to the server is trusted. Use: testing only. Not secure in production. Example: host all all 127.0.0.1/32 trust md5 Password required, stored as MD5 hash. Legacy support. Weaker security, avoid if possible. Example: host all all 127.0.0.1/32 md5 scram-sha-256 Password required, stored as salted SCRAM-SHA-256 hash. Stronger than MD5. Recommended method for production. Example: host all all 127.0.0.1/32 scram-sha-256 peer Works for local connections only. The OS user name must match the PostgreSQL role. Good for local scripts/services under same user. Example: local all all peer reject Explicitly denies access. Useful for blocking unwanted connections (like a firewall rule). Example: host all all 0.0.0.0/0 reject cert Requires SSL/TLS client certificate. PostgreSQL role must match certificate username. Very secure for enterprise / production with SSL. Example: hostssl all all 192.168.1.0/24 cert clientcert=1
postgres=# CREATE USER teja WITH PASSWORD 'teja123'; CREATE ROLE postgres=# CREATE DATABASE orcl OWNER teja; CREATE DATABASE postgres=#
[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # Allow local peer access for postgres local all postgres peer # Allow local password access for all local all all scram-sha-256 [postgres@pg17 ~]$
Works for local connections only. The OS user name must match the PostgreSQL role. [postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # Allow local peer access for postgres local all postgres peer # Allow local password access for all local all all scram-sha-256 [postgres@pg17 ~]$ [postgres@pg17 ~]$ psql -U teja -d orcl Password for user teja: <---- it's asking password psql (15.13) Type "help" for help. orcl=> exit [postgres@pg17 ~]$ Password Not asking for user postgres [postgres@pg17 ~]$ psql -U postgres -d orcl psql (15.13) Type "help" for help. orcl=#
No password needed. Anyone who can connect to the server is trusted. Use: testing only. Not secure in production. Change pg_hba.conf: [postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # Allow local peer access for postgres local all postgres peer # Allow local password access for all local all all trust [postgres@pg17 ~]$ [postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/ server signaled [postgres@pg17 ~]$ psql -U teja -d orcl It won’t ask for a password. psql (15.13) Type "help" for help. orcl=>
[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # Allow local peer access for postgres local all postgres peer # Allow local password access for all local all all reject [postgres@pg17 ~]$ [postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/ server signaled [postgres@pg17 ~]$ psql -U teja -d orcl psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: pg_hba.conf rejects connection for host "[local]", user "teja", database "orcl", no encryption [postgres@pg17 ~]$
Allow only user teja, block everyone else: [postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # Allow local peer access for postgres local all postgres peer # Allow local password access for all local all all trust # Allow IPv4 localhost host orcl teja 192.168.2.31/32 scram-sha-256 host orcl all 192.168.2.31/32 reject [postgres@pg17 ~]$ [postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/ server signaled [postgres@pg17 ~]$ psql -U teja -d orcl -h 192.168.2.31 Password for user teja: psql (15.13) Type "help" for help. orcl=> \conninfo You are connected to database "orcl" as user "teja" on host "192.168.2.31" at port "5432". orcl=> orcl=> exit [postgres@pg17 ~]$ [postgres@pg17 ~]$ psql -U postgres -d orcl -h 192.168.2.31 psql: error: connection to server at "192.168.2.31", port 5432 failed: FATAL: pg_hba.conf rejects connection for host "192.168.2.31", user "postgres", database "orcl", no encryption [postgres@pg17 ~]$ Notice: Only user Teja able to connect where as user postgres not able to connect
We want to block connections from IP 192.168.2.0/24 [postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # Allow local peer access for postgres local all postgres peer # Allow local password access for all local all all trust # Allow IPv4 localhost host all all 192.168.2.0/24 reject [postgres@pg17 ~]$ [postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/ server signaled [postgres@pg17 ~]$ [postgres@pg17 ~]$ psql -U teja -d orcl -h 192.168.2.31 psql: error: connection to server at "192.168.2.31", port 5432 failed: FATAL: pg_hba.conf rejects connection for host "192.168.2.31", user "teja", database "orcl", no encryption [postgres@pg17 ~]$
postgres=# SELECT * FROM pg_hba_file_rules; line_number | type | database | user_name | address | netmask | auth_method | options | error -------------+-------+----------+------------+--------------+-----------------+---------------+---------+------- 4 | local | {all} | {postgres} | | | peer | | 7 | local | {all} | {all} | | | trust | | 11 | host | {orcl} | {teja} | 192.168.2.31 | 255.255.255.255 | scram-sha-256 | | 12 | host | {orcl} | {all} | 192.168.2.31 | 255.255.255.255 | reject | | (4 rows) postgres=# [postgres@pg17 ~]$ tail -f /pgData/pgsql15/data/log/postgresql-Tue.log 2025-09-16 04:08:37.244 EDT [8294] LOG: database system was shut down at 2025-09-16 04:08:32 EDT 2025-09-16 04:08:37.251 EDT [8289] LOG: database system is ready to accept connections 2025-09-16 04:11:27.459 EDT [8289] LOG: received SIGHUP, reloading configuration files 2025-09-16 04:11:31.842 EDT [8401] FATAL: pg_hba.conf rejects connection for host "[local]", user "teja", database "orcl", no encryption 2025-09-16 04:13:37.568 EDT [8292] LOG: checkpoint starting: time 2025-09-16 04:13:37.573 EDT [8292] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.002 s, total=0.006 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB 2025-09-16 04:16:15.863 EDT [8289] LOG: received SIGHUP, reloading configuration files 2025-09-16 04:16:52.318 EDT [8474] FATAL: pg_hba.conf rejects connection for host "192.168.2.31", user "teja", database "orcl", no encryption 2025-09-16 04:27:15.932 EDT [8289] LOG: received SIGHUP, reloading configuration files 2025-09-16 04:27:50.593 EDT [8613] FATAL: pg_hba.conf rejects connection for host "192.168.2.31", user "postgres", database "orcl", no encryption
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp :
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/