Connections and security

Client authentication

Before a session starts, PostgreSQL reads pg_hba.conf from the top and uses the first line that matches the connection type, database, user and client address. That line's method decides how the client must prove its identity.

In plain words

When a client connects, PostgreSQL first decides whether and how to check its identity. The rules live in a small file, pg_hba.conf ("host-based authentication"). Each line says: for this kind of connection, to this database, as this user, from these addresses, use this method.

The file is read top to bottom and the first matching line wins, even if a later line would have been more convenient. If that line says reject, or no line matches at all, the connection is refused, without even asking for a password.

Common methods: scram-sha-256 (password, safely hashed), peer (local socket: the operating-system user name must match), cert (client SSL certificate), reject, and trust (anyone, no questions: avoid). Pick a client below and watch the rules being checked.

For DBAs and operators

Line format

# TYPE   DATABASE     USER        ADDRESS          METHOD   [options]
local    all          postgres                     peer
hostssl  sales        app         10.0.0.0/24      scram-sha-256
host     replication  replicator  10.0.0.0/24      scram-sha-256

TYPE: local (Unix socket), host (TCP, with or without SSL), hostssl / hostnossl, hostgssenc / hostnogssenc.
DATABASE: names, all, sameuser, samerole, @file, or replication. Physical replication connections only match the keyword replication, never all.
USER: names, all, +role (members of a role), regular expressions starting with / (PostgreSQL 16+).
ADDRESS: CIDR, host name, samehost, samenet.

The file is read at start-up and on reload (SELECT pg_reload_conf()); a syntax error on reload keeps the old rules and logs the problem. Check the parsed result, including errors, with:

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

Real error messages and what they mean

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

No line matched. Note the last part: no encryption versus SSL encryption often explains it (a hostssl line does not match a non-SSL attempt).

FATAL: pg_hba.conf rejects connection for host "192.168.1.20", user "app", database "sales", no encryption

A reject line matched first.

FATAL: password authentication failed for user "app"DETAIL: Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 3: "hostssl sales app 10.0.0.0/24 scram-sha-256"

The client sees only the FATAL line; the DETAIL (which line matched, and why it failed, e.g. User "app" has no password assigned.) is written to the server log only. Always look there.

Passwords: SCRAM, and MD5 is deprecated

scram-sha-256 is the default password_encryption since PostgreSQL 14. MD5 password support is deprecated in PostgreSQL 18 and will be removed; setting an MD5 password now warns:

WARNING: setting an MD5-encrypted passwordDETAIL: MD5 password support is deprecated and will be removed in a future release of PostgreSQL.

An md5 line in pg_hba.conf automatically uses SCRAM when the stored password is a SCRAM verifier, so migrate by re-setting passwords (\password) and then switching the lines to scram-sha-256. Find leftovers with SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'.

Logging

In PostgreSQL 18 log_connections takes a list of aspects (receipt, authentication, authorization, setup_durations, or all); the old on/off values are still accepted. Successful authentication is logged as:

LOG: connection authenticated: identity="app" method=scram-sha-256 (/etc/postgresql/18/main/pg_hba.conf:3)

PostgreSQL 18 also adds an oauth method (with oauth_validator_libraries). Put specific lines first and broad reject or catch-all lines last.

For developers: inside the source

Call flow

BackendInitialize()                 tcop/backend_startup.c
  ProcessSSLStartup(), ProcessStartupPacket()   user, database, options
PostgresMain() → InitPostgres()     utils/init/postinit.c
  PerformAuthentication()
    ClientAuthentication()          libpq/auth.c
      hba_getauthmethod() → check_hba()   libpq/hba.c: first match wins
      switch (port->hba->auth_method) { case uaPeer: auth_peer() … }
      ClientAuthentication_hook(port, status)
      STATUS_OK ? sendAuthRequest(AUTH_REQ_OK) : auth_failed()

Parsing and matching pg_hba.conf

load_hba(): open_auth_file() / tokenize_auth_file() (handles include, @file), then parse_hba_line() turns each line into an HbaLine. The new list replaces parsed_hba_lines only if the whole file parsed, which is why a bad reload keeps the old rules. The postmaster calls it at start and on SIGHUP; forked backends inherit the parsed list (with EXEC_BACKEND, PerformAuthentication() reloads it).
HbaLine (include/libpq/hba.h): linenumber, rawline, conntype (ConnType: ctLocal, ctHost, ctHostSSL …), databases/roles token lists, address and mask, auth_method (UserAuth: uaReject, uaTrust, uaPeer, uaMD5, uaSCRAM, uaCert, uaLDAP, uaOAuth …) and per-method options.
check_hba() loops over the list and tests connection type, check_ip()/check_hostname()/check_same_host_or_net(), check_db() and check_role(), returning on the first full match. If nothing matches it sets uaImplicitReject; ClientAuthentication() then raises "no pg_hba.conf entry for host …" (and "pg_hba.conf rejects connection …" for uaReject) before any password exchange. For a failed method, auth_failed() adds the "Connection matched file … line …" DETAIL, which goes to the server log only.
pg_hba_file_rules is fill_hba_view() in hbafuncs.c: it re-reads the file on disk, so it shows what the next reload would load.

Passwords

get_role_password() in crypt.c fetches pg_authid.rolpassword; get_password_type() classifies it as PASSWORD_TYPE_MD5 or PASSWORD_TYPE_SCRAM_SHA_256. For md5 and scram-sha-256 lines, CheckPWChallengeAuth() runs SCRAM whenever the stored secret is a SCRAM verifier, through CheckSASLAuth() (auth-sasl.c) and pg_be_scram_mech (scram_exchange(), verify_client_proof() in auth-scram.c). After success, set_authn_id() records the authenticated identity (the basis of system_user) and, when log_connections includes authentication, writes the "connection authenticated" log line. OAuth (new in 18) is in auth-oauth.c and calls validator modules loaded from oauth_validator_libraries.

Extend and experiment

ClientAuthentication_hook sees every attempt that gets past the reject checks, with its status: contrib/auth_delay sleeps on failure, contrib/sepgsql uses it too. Password policy goes in check_password_hook (contrib/passwordcheck).
TAP tests: src/test/authentication/t/ (001_password.pl, 003_peer.pl, 004_file_inclusion.pl), plus src/test/ldap, src/test/kerberos and src/test/modules/oauth_validator (an example validator).

Parameters that shape this

hba_file ident_file password_encryption md5_password_warnings scram_iterations authentication_timeout listen_addresses log_connections krb_server_keyfile ssl