pg_hba.conf and PostgreSQL authentication methods

Every connection to PostgreSQL passes through host-based authentication, configured in the file pg_hba.conf. It answers one question: for this connection type, database, user and client address, which authentication method applies? If no line matches, the connection is refused.

The file lives in the data directory by default; Debian and Ubuntu keep it in /etc/postgresql/<version>/main/. Ask the server where its file is with SHOW hba_file;.

The format of a rule

Each non-comment line is one record with these fields: type, database, user, address (not for local), method and optional options.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     scram-sha-256
host    appdb           appuser         10.0.0.0/24             scram-sha-256
hostssl all             all             192.0.2.0/24            scram-sha-256
host    replication     replicator      10.0.0.12/32            scram-sha-256
host    all             all             0.0.0.0/0               reject

• TYPE: local (Unix-domain socket), host (TCP, with or without SSL), hostssl (TCP with SSL only), hostnossl (TCP without SSL only), plus hostgssenc/hostnogssenc for GSSAPI encryption.
• DATABASE: a name, all, sameuser, a comma-separated list, or the keyword replication, which matches physical replication connections only. all does not match replication connections.
• USER: a role name, all, or +groupname for members of a role.
• ADDRESS: a CIDR range such as 10.0.0.0/24, a single host as /32, a host name, or samehost/samenet.
• METHOD: how the client must authenticate (see below).

First match wins, no fall-through

Rules are read top to bottom. The first line that matches the connection type, client address, database and user is used, and only that line. If its authentication fails, PostgreSQL does not try later lines. That is why specific rules and reject rules go near the top and broad catch-all rules go at the bottom.

Passing pg_hba.conf is not the whole story: the role also needs the CONNECT privilege on the database, and then normal object privileges.

Authentication methods

• scram-sha-256: challenge-response password authentication. It never sends the password over the wire and is the most secure of the built-in password methods. The default password_encryption is scram-sha-256.
• md5: older challenge-response method. If the stored password is already a SCRAM hash, the server automatically uses SCRAM. MD5 password support is deprecated and planned for removal, so migrate to SCRAM.
• password: sends the password in clear text. Avoid it except over SSL.
• peer: for local socket connections only. The server asks the kernel for the client’s OS user name and requires it to match the database user (or a mapping in pg_ident.conf). This is why sudo -u postgres psql works without a password.
• ident: the TCP equivalent of peer, using an ident server on the client. Rarely a good idea today.
• cert: SSL client certificate authentication.
• ldap, radius, pam, gss, sspi: delegate to an external system.
• trust: anyone who matches the line may connect as any role it names, with no password. Use it only on single-user test machines.
• reject: always refuse. Useful to block a range before a broader allow rule.

Moving from md5 to scram-sha-256

1. Make sure all client drivers support SCRAM.
2. Set password_encryption = 'scram-sha-256' (already the default in recent versions).
3. Have every user set their password again, for example with \password in psql. Only a newly set password is stored as a SCRAM hash.
4. Change the method in pg_hba.conf from md5 to scram-sha-256 and reload.

Testing and applying changes

Changes to pg_hba.conf need a reload, not a restart. Before reloading, the pg_hba_file_rules view shows how the server parses the file on disk; rows with a non-empty error column point at broken lines.

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

SELECT pg_reload_conf();

From the shell you can also run sudo systemctl reload postgresql or pg_ctl reload. When a connection is refused, the server log usually has more detail than the message the client sees.

Official documentation

• The pg_hba.conf file
• Password authentication
• Peer authentication