PostgreSQL DBA Part 6: pg_hba.conf and Client Authentication

This is Part 6 of the PostgreSQL DBA series. pg_hba.conf is the file that decides who may connect to your PostgreSQL server, to which database, from which address, and how they must prove who they are. To allow remote connections you change two things: listen_addresses in postgresql.conf (so the server listens on the network) and a host line in pg_hba.conf (so the server accepts that client). By the end of this part you will be able to read every line of the default file, add a rule for an application server, test it before you reload, and fix the error messages people hit most often.

In Part 5 we covered postgresql.conf, including the difference between a reload and a restart. You will use both here: pg_hba.conf changes need only a reload, while listen_addresses needs a restart.

What you will learn

• What each column of a pg_hba.conf line means, and the first-match rule
• What the Ubuntu default rules do, and why sudo -u postgres psql needs no password
• How to let an application server connect with scram-sha-256 passwords
• How to test the file with the pg_hba_file_rules view before you reload
• How to find and convert old MD5 passwords, which PostgreSQL 18 deprecates
• How to read the real authentication errors and fix each one

Prerequisites

• Parts 2 to 5 of this series: installing PostgreSQL 18, the data directory, psql, and postgresql.conf with reload vs restart.
• One Ubuntu server with PostgreSQL 18 from the PGDG repository and cluster 18 main running (check with pg_lsclusters). In this part the server is called db1 with address 10.0.0.11.
• Optional: a second machine (the application server app1, 10.0.0.21) with the PostgreSQL client installed (sudo apt-get install -y postgresql-client-18). If you have only one machine, you can test from db1 itself, as shown below.
• A Linux user with sudo rights, and a test server. A wrong pg_hba.conf can lock everyone out, so practise away from production.

How it works

Think of pg_hba.conf as the guest list at the door of a building. Before anyone reaches a database, the server reads the list from the top. It looks for the first line that matches the visitor: how they arrived, which database they want, which user name they gave and where they came from. That line says how the visitor must prove their identity, or that they must be turned away.

HBA stands for host-based authentication. Authentication means proving who you are (for example with a password). It is different from authorization, which decides what you may do once you are in (GRANT and REVOKE, covered in Part 7).

Each rule is one line with these columns:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    shop            app_user        10.0.0.0/24             scram-sha-256

The address uses CIDR notation: an IP address, a slash and the number of fixed bits. 10.0.0.0/24 means every address from 10.0.0.0 to 10.0.0.255, and /32 means exactly one IPv4 address. Do not put spaces around the slash.

The methods you will use most:

• peer: for local socket connections only. The server asks the Linux kernel which operating system user opened the connection and allows it only if that name equals the database user name. No password is involved.
• scram-sha-256: password check using a challenge and response, so the password itself never travels over the network, and the server stores only a salted hash. This is the method to use for passwords.
• reject: always refuse. Useful to block one host or user above a broader rule.
• trust: allow anyone who matches, with no password, as any user they name. Avoid it outside a throwaway test machine.
• md5: an older password method. It is deprecated in PostgreSQL 18 and will be removed in a future release.

Two rules decide everything:

• First match wins. The server uses the first line whose type, database, user and address all match. If authentication on that line fails, it does not try the next line. If no line matches, the connection is refused.
• The file is read at start and on reload. On Linux, editing the file does nothing until you reload the server. A reload does not disconnect anyone; existing sessions keep running.

Reading the Ubuntu default rules

Step 1: Find the file the server is using

On db1, as your sudo user. On Ubuntu the file is not inside the data directory. Ask the server where it is, so you never edit the wrong copy:

sudo -u postgres psql -c "SHOW hba_file;"

              hba_file               
-------------------------------------
 /etc/postgresql/18/main/pg_hba.conf
(1 row)

The file belongs to the postgres Linux user with permissions -rw-r-----, so you need sudo to read or edit it. Next to it is pg_ident.conf, which holds optional user name maps for peer and ident; you do not need it in this part.

Step 2: List the active lines

On db1, as your sudo user. The file is about 130 lines, most of them comments. Show only the real rules:

sudo grep -vE '^\s*(#|$)' /etc/postgresql/18/main/pg_hba.conf

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Read it top to bottom. Local socket connections use peer, so the Linux user postgres can log in as the database user postgres without a password. TCP connections to 127.0.0.1 (IPv4) or ::1 (IPv6), that is, from the server to itself, need a scram-sha-256 password. The three replication lines do the same for physical replication, which you will use in Part 33. Nothing allows a connection from another machine yet.

Step 3: See the same rules from SQL

On db1, as the postgres user through psql. The pg_hba_file_rules view parses the file and shows one row per rule. Only superusers can read it by default.

SELECT rule_number, line_number, type, database, user_name, address, auth_method
  FROM pg_hba_file_rules;

 rule_number | line_number | type  |   database    | user_name  |  address  |  auth_method  
-------------+-------------+-------+---------------+------------+-----------+---------------
           1 |         118 | local | {all}         | {postgres} |           | peer
           2 |         123 | local | {all}         | {all}      |           | peer
           3 |         125 | host  | {all}         | {all}      | 127.0.0.1 | scram-sha-256
           4 |         127 | host  | {all}         | {all}      | ::1       | scram-sha-256
           5 |         130 | local | {replication} | {all}      |           | peer
           6 |         131 | host  | {replication} | {all}      | 127.0.0.1 | scram-sha-256
           7 |         132 | host  | {replication} | {all}      | ::1       | scram-sha-256
(7 rows)

rule_number is the order the server checks rules in, and line_number tells you where to look in the file. This view reads the file as it is on disk now, not what the server last loaded. That makes it the perfect test before a reload, as you will see in Step 7.

Step 4: See peer authentication in action

On db1, as root or any Linux user that is not postgres. Try to connect as the database user postgres:

psql -U postgres -d postgres -c "SELECT 1;"

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

Rule 1 matched (local, any database, user postgres) and its method is peer. The kernel reported the Linux user root, which is not postgres, so the server refused. This is why the series always uses sudo -u postgres psql: it runs psql as the Linux user postgres, and peer lets it in.

Letting an application server connect

A common job: the application on app1 (10.0.0.21) must reach database shop on db1 as user app_user with a password. The connection path looks like this:

app1 (10.0.0.21)  --TCP 5432, scram-sha-256-->  db1 (10.0.0.11:5432)  database shop, role app_user

Step 5: Create the role and database

On db1, as the postgres user. A rule is useless if the role has no password: password methods always fail for a role whose stored password is null.

sudo -u postgres psql <<'EOF'
CREATE ROLE app_user LOGIN PASSWORD 'Str0ng#Pass';
CREATE DATABASE shop OWNER app_user;
SHOW password_encryption;
SELECT rolname, left(rolpassword, 14) AS hash_start
  FROM pg_authid WHERE rolname = 'app_user';
EOF

CREATE ROLE
CREATE DATABASE
 password_encryption 
---------------------
 scram-sha-256
(1 row)

 rolname  |   hash_start   
----------+----------------
 app_user | SCRAM-SHA-256$
(1 row)

password_encryption (default scram-sha-256) decides how a new password is hashed at the moment it is set. The stored value starts with SCRAM-SHA-256$, so this role can use scram-sha-256 authentication. In real life, set passwords with the psql meta-command \password app_user so the plain password never appears in your shell history or the server log.

Verify through the loopback rule (rule 3), from db1 itself:

PGPASSWORD='Str0ng#Pass' psql -h 127.0.0.1 -U app_user -d shop \
  -c "SELECT current_user, inet_client_addr(), inet_server_addr();"

 current_user | inet_client_addr | inet_server_addr 
--------------+------------------+------------------
 app_user     | 127.0.0.1        | 127.0.0.1
(1 row)

Step 6: Make the server listen on the network

On db1, as your sudo user. By default listen_addresses is localhost, so the server does not even accept TCP connections from other machines. This setting can only be set at server start, so it needs a restart. Using a conf.d file, as in Part 5:

# /etc/postgresql/18/main/conf.d/10-listen.conf
# before: not set (default listen_addresses = 'localhost')
# after:
listen_addresses = '*'

echo "listen_addresses = '*'" | sudo -u postgres tee /etc/postgresql/18/main/conf.d/10-listen.conf
sudo systemctl restart postgresql@18-main
sudo -u postgres psql -c "SHOW listen_addresses;"

listen_addresses = '*'
 listen_addresses 
------------------
 *
(1 row)

* means all available IP interfaces. You can instead list specific addresses, for example 'localhost, 10.0.0.11', to listen only on the private network. Verify in the server log:

sudo grep 'listening on' /var/log/postgresql/postgresql-18-main.log | tail -2

2026-10-26 12:58:42.807 UTC [1485] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-10-26 12:58:42.811 UTC [1485] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

If you only reload instead of restarting, the log says parameter "listen_addresses" cannot be changed without restarting the server and nothing changes. Undo: sudo rm /etc/postgresql/18/main/conf.d/10-listen.conf and restart again. Also allow port 5432 in any firewall (for example ufw) between app1 and db1, only from the app network.

Now try to connect to db1 through its network address. From db1 itself this uses the 10.0.0.11 interface, so the loopback rules do not match:

PGPASSWORD='Str0ng#Pass' psql -h 10.0.0.11 -U app_user -d shop -c "SELECT 1;"

psql: error: connection to server at "10.0.0.11", port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.0.0.11", user "app_user", database "shop", SSL encryption
connection to server at "10.0.0.11", port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.0.0.11", user "app_user", database "shop", no encryption

The server now listens, but no rule matches this address. psql tried twice because Ubuntu enables SSL by default: first with SSL encryption, then without. Neither matched any line, so the server refused both.

Step 7: Add the rule and test it before reloading

On db1, as your sudo user. Back up the file, then add the new rule at the end with an editor such as nano:

sudo cp -p /etc/postgresql/18/main/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf.bak
sudo nano /etc/postgresql/18/main/pg_hba.conf

# before: the file ends with the "host replication all ::1/128 scram-sha-256" line
# after: these lines added at the end

# Application servers on the app network
host    shop            app_user        10.0.0.0/24             scram-sha-256

This rule allows only TCP connections, only to database shop, only as app_user, only from 10.0.0.0 to 10.0.0.255, and only with a SCRAM password. Keep rules this narrow; avoid host all all 0.0.0.0/0, which opens every database to the whole internet if the port is reachable.

Before you reload, ask the server to parse the file. Here is what you see if you made a typo, writing scram-sha256 without the second hyphen:

SELECT line_number, auth_method, error
  FROM pg_hba_file_rules
 WHERE error IS NOT NULL;

 line_number | auth_method |                    error                     
-------------+-------------+----------------------------------------------
         135 |             | invalid authentication method "scram-sha256"
(1 row)

If you reload with this error, the server logs the problem and keeps using the old rules. The connection still fails and only the log tells you why:

LOG:  received SIGHUP, reloading configuration files
LOG:  invalid authentication method "scram-sha256"
CONTEXT:  line 135 of configuration file "/etc/postgresql/18/main/pg_hba.conf"
LOG:  /etc/postgresql/18/main/pg_hba.conf was not reloaded

Fix the typo and run the check again. When the query returns no rows, look at the new rule:

SELECT rule_number, line_number, type, database, user_name, address, netmask, auth_method
  FROM pg_hba_file_rules
 WHERE line_number > 132;

 rule_number | line_number | type | database | user_name  | address  |    netmask    |  auth_method  
-------------+-------------+------+----------+------------+----------+---------------+---------------
           8 |         135 | host | {shop}   | {app_user} | 10.0.0.0 | 255.255.255.0 | scram-sha-256
(1 row)

Step 8: Reload and verify

On db1, as your sudo user. pg_hba.conf needs only a reload:

sudo systemctl reload postgresql@18-main
sudo tail -1 /var/log/postgresql/postgresql-18-main.log

2026-10-26 12:59:10.926 UTC [1485] LOG:  received SIGHUP, reloading configuration files

No "was not reloaded" line follows, so the new rules are active. SELECT pg_reload_conf(); as a superuser does the same thing. Verify from app1 (or from db1 with -h 10.0.0.11):

PGPASSWORD='Str0ng#Pass' psql -h 10.0.0.11 -U app_user -d shop \
  -c "SELECT current_user, current_database(), inet_client_addr();"

 current_user | current_database | inet_client_addr 
--------------+------------------+------------------
 app_user     | shop             | 10.0.0.11
(1 row)

Now prove the rule is as narrow as you meant. The same user asking for another database is refused, because no line matches database postgres from this address:

psql: error: connection to server at "10.0.0.11", port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.0.0.11", user "app_user", database "postgres", SSL encryption

Undo: delete the two added lines (or sudo cp -p /etc/postgresql/18/main/pg_hba.conf.bak /etc/postgresql/18/main/pg_hba.conf) and reload.

Rule order: first match wins

Step 9: Block one user with a reject rule

On db1, as your sudo user. To see first-match in action, add a reject line above the IPv4 loopback rule, so it is checked first:

# before:
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256

# after:
host    shop            app_user        127.0.0.1/32            reject
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256

Check the order, then reload:

SELECT rule_number, line_number, database, user_name, address, auth_method
  FROM pg_hba_file_rules
 WHERE type = 'host' AND rule_number <= 4;
SELECT pg_reload_conf();

 rule_number | line_number | database | user_name  |  address  |  auth_method  
-------------+-------------+----------+------------+-----------+---------------
           3 |         124 | {shop}   | {app_user} | 127.0.0.1 | reject
           4 |         126 | {all}    | {all}      | 127.0.0.1 | scram-sha-256
(2 rows)

 pg_reload_conf 
----------------
 t
(1 row)

Verify with two connections from db1:

PGPASSWORD='Str0ng#Pass' psql -h 127.0.0.1 -U app_user -d shop -c "SELECT 1;"
PGPASSWORD='Str0ng#Pass' psql -h 127.0.0.1 -U app_user -d postgres -c "SELECT current_user, current_database();"

psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "app_user", database "shop", SSL encryption
connection to server at "127.0.0.1", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "app_user", database "shop", no encryption
 current_user | current_database 
--------------+------------------
 app_user     | postgres
(1 row)

The correct password did not help for shop: rule 3 matched first and its method is reject, so the server never looked at rule 4. For database postgres, rule 3 does not match, so rule 4 applies and the password works. The lesson: put narrow rules first and broad rules last. A broad rule high in the file silently hides every rule below it.

Undo: delete the reject line and reload. Rule numbers return to what you saw in Step 3.

Moving from MD5 to SCRAM

PostgreSQL 18 deprecates MD5 passwords: support will be removed in a future major release. Servers upgraded from old versions often still have roles with MD5 hashes. An MD5 hash cannot be used with scram-sha-256, so those users fail to log in the day you switch the rules.

Step 10: Find roles with MD5 passwords

On db1, as the postgres user. To have something to find, create an old-style role. The SET changes password_encryption for this session only:

SET password_encryption = 'md5';
CREATE ROLE legacy_user LOGIN PASSWORD 'Old#Pass1';
RESET password_encryption;

On PostgreSQL 18 the CREATE ROLE prints a deprecation warning:

WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
HINT:  Refer to the PostgreSQL documentation for details about migrating to another password type.
CREATE ROLE

You can turn this warning off with md5_password_warnings = off (new in PostgreSQL 18, default on), but it is better to fix the passwords. List every role whose stored hash is MD5:

SELECT rolname, left(rolpassword, 14) AS hash_start
  FROM pg_authid
 WHERE rolpassword LIKE 'md5%';

   rolname   |   hash_start   
-------------+----------------
 legacy_user | md55804793cdf4
(1 row)

Step 11: Re-set the password and switch the rule

The documented order is: make sure every client library supports SCRAM, keep password_encryption = scram-sha-256 (the default), have each user set a new password, then change the method in pg_hba.conf from md5 to scram-sha-256. Re-set the password:

ALTER ROLE legacy_user PASSWORD 'New#Pass2';
SELECT rolname, left(rolpassword, 14) AS hash_start
  FROM pg_authid
 WHERE rolname IN ('app_user', 'legacy_user')
 ORDER BY rolname;

ALTER ROLE
   rolname   |   hash_start   
-------------+----------------
 app_user    | SCRAM-SHA-256$
 legacy_user | SCRAM-SHA-256$
(2 rows)

While you migrate, a line with method md5 still works for users who already have SCRAM hashes: the server automatically uses SCRAM for them. That lets you convert users one by one and change md5 to scram-sha-256 in pg_hba.conf at the end, followed by a reload. Cleanup:

sudo -u postgres psql -c "DROP ROLE legacy_user;"

Common mistakes and how to fix them

FATAL: no pg_hba.conf entry for host "...", user "...", database "...", no encryption

No line matches this combination of type, address, database and user. Copy the three values from the message and compare them with pg_hba_file_rules. Usual causes: the client address is outside the CIDR range, the database name is different (if the client gives no database, it defaults to the user name), or you edited the file but did not reload. The words "SSL encryption" or "no encryption" tell you which attempt was refused; a hostssl line never matches a non-SSL attempt.

FATAL: pg_hba.conf rejects connection for host "..."

A reject line matched first. Find it in pg_hba_file_rules, then move your allow rule above it or narrow the reject rule.

FATAL: password authentication failed for user "..."

A line matched and asked for a password, but the check failed. The client sees the same message for a wrong password, a role with no password and a role that does not exist. The server log has the real reason and the matching line:

FATAL:  password authentication failed for user "no_pw_user"
DETAIL:  User "no_pw_user" has no password assigned.
	Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 125: "host    all             all             127.0.0.1/32            scram-sha-256"

Other DETAIL lines you will see are Role "ghost" does not exist. and, for a role that still has an MD5 hash on a scram-sha-256 line, User "legacy_user" does not have a valid SCRAM secret.. Fix: \password username in psql as a superuser. Find login roles without a password with SELECT rolname FROM pg_authid WHERE rolcanlogin AND rolpassword IS NULL; (postgres appears there by design: it logs in with peer).

FATAL: Peer authentication failed for user "postgres"

You ran psql -U postgres as a Linux user that is not postgres. Use sudo -u postgres psql. Do not "fix" this by changing peer to trust.

Connection refused or timeout from another machine

If the error says connection refused or times out instead of a FATAL message, the client never reached the server. Check listen_addresses (and that you restarted), and the firewall. pg_hba.conf is checked only after the TCP connection succeeds.

Edited the file but nothing changed

You did not reload, or the reload failed. Search the log for was not reloaded and query pg_hba_file_rules for errors.

Practice exercise

1. Create a role report_user with a password and allow it to connect only to database shop, only from the single address 10.0.0.30, with scram-sha-256. Check the rule with pg_hba_file_rules before you reload.
2. Write the same rule by mistake as 10.0.0.30/24, query pg_hba_file_rules, and look at the error, address and netmask columns. Which clients does this line really allow? Then fix it.

Quick reference

# Where is pg_hba.conf?
sudo -u postgres psql -c "SHOW hba_file;"

# Show the active rules without comments
sudo grep -vE '^\s*(#|$)' /etc/postgresql/18/main/pg_hba.conf

# Parse the file from SQL (on-disk contents, before reload)
sudo -u postgres psql -c "SELECT rule_number, line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"

# Create a role with a SCRAM password and its database
sudo -u postgres psql -c "CREATE ROLE app_user LOGIN PASSWORD 'Str0ng#Pass';"
sudo -u postgres psql -c "CREATE DATABASE shop OWNER app_user;"

# Listen on the network (restart needed)
echo "listen_addresses = '*'" | sudo -u postgres tee /etc/postgresql/18/main/conf.d/10-listen.conf
sudo systemctl restart postgresql@18-main

# Back up and edit pg_hba.conf, add:
#   host  shop  app_user  10.0.0.0/24  scram-sha-256
sudo cp -p /etc/postgresql/18/main/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf.bak
sudo nano /etc/postgresql/18/main/pg_hba.conf

# Check for errors before reloading
sudo -u postgres psql -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

# Reload and check the log
sudo systemctl reload postgresql@18-main
sudo tail -3 /var/log/postgresql/postgresql-18-main.log

# Test a connection over TCP
psql -h 10.0.0.11 -U app_user -d shop -c "SELECT current_user, inet_client_addr();"

# Find roles still using MD5 hashes
sudo -u postgres psql -c "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';"

Summary and what's next

• pg_hba.conf decides who may connect, to which database, from where, and with which method; on Ubuntu it is /etc/postgresql/18/main/pg_hba.conf
• The first matching line wins, and there is no fall-through: narrow rules go first
• Use peer for local admin access and scram-sha-256 for passwords; never trust on network lines
• Remote access needs listen_addresses (restart) plus a host rule (reload)
• Test with pg_hba_file_rules before every reload; a broken file is not loaded
• Find MD5 hashes in pg_authid and re-set those passwords, since PostgreSQL 18 deprecates MD5
• When a login fails, read the server log: it names the reason and the matching line

In Part 7 you will learn roles, users, groups and privileges: GRANT, REVOKE and default privileges.