skip to content

In PostgreSQL's pg_hba.conf, what do the fields of a record mean, and how do the local, host, hostssl and hostnossl connection types differ?

level: juniorimportance: must knowfreq 65%

answer

  1. type, database, user, address, method
  2. Unix socket versus TCP
  3. hostssl needs ssl = on
  4. plus sign means members

basics

~20 s

A pg_hba.conf record lists a connection type, database, user, client address (absent on local lines) and an authentication method with optional options. local means Unix sockets, host any TCP/IP, hostssl only TLS-encrypted TCP, hostnossl only unencrypted TCP.

solid answer

~40 s

Each record in `pg_hba.conf` reads left to right: **connection type**, **database**, **user**, **address** (not on `local` lines), **auth method**, then optional `name=value` options such as `map=localops` or `clientcert=verify-full`. The database column takes names, `all`, `sameuser`, `samerole` or `replication` (physical replication only); the user column takes role names, `all`, or `+group` for members of a role. `local` matches Unix-domain sockets, `host` matches every TCP/IP connection whether encrypted or not, `hostssl` matches TCP only when SSL/TLS is in use, and `hostnossl` only when it is not; `hostgssenc` and `hostnogssenc` split on GSSAPI encryption the same way. PostgreSQL uses the first record that matches and never falls through. A `hostssl` line does nothing while `ssl = off`, the default, and remote clients never reach the file unless `listen_addresses` covers a non-loopback interface.

code

console · 7 lines
console
$ cat "$PGDATA/pg_hba.conf"
# TYPE    DATABASE  USER            ADDRESS        METHOD
local     all       postgres                       peer
hostssl   clinic    clinic_app      10.20.0.0/16   scram-sha-256
hostssl   clinic    +clinic_staff   10.30.0.0/24   scram-sha-256
hostnossl all       all             0.0.0.0/0      reject
hostnossl all       all             ::0/0          reject

go deeper

for a junior

Recite the columns in order and say what local, host, hostssl and hostnossl each match. That is the screening answer.

for a middle

Explain the column keywords (sameuser, samerole, replication, +role, @file, samenet) and why the first matching record is final.

for a senior

Show how you lay out a production file: narrow records first, hostssl for remote access, an explicit reject for plaintext, and a pg_hba_file_rules check before every reload.

for a principal

Treat pg_hba.conf as one layer of network policy, alongside firewalls and listen_addresses. Decide which layer owns each control so an audit can trace every rule.

## What pg_hba.conf decides `pg_hba.conf` (HBA stands for **host-based authentication**) is PostgreSQL's client authentication file. `initdb` writes a default one into the data directory, and the `hba_file` parameter can point elsewhere. For each new connection the server walks the records from top to bottom and takes the **first record** whose connection type, database, user and address all match. That record's method is the only one tried: if it fails, later records are not consulted, and if no record matches at all, the connection is refused. The file is read at start-up and on a reload, not on every connection (Windows is the exception and applies edits immediately). The general theory of ordered host rules belongs to the engine-neutral security topic; this answer is about what each PostgreSQL field means. ## The fields of a record | Field | What it holds | Typical values | |---|---|---| | type | how the client arrived | `local`, `host`, `hostssl`, `hostnossl`, `hostgssenc`, `hostnogssenc` | | database | which database names | `all`, `sameuser`, `samerole`, `replication`, `clinic`, `@dbs`, a `/regex` | | user | which roles | `all`, `clinic_app`, `+clinic_staff`, `@admins`, a `/regex` | | address | client address, absent on `local` | `10.20.0.0/16`, `::1/128`, `samehost`, `samenet`, `all`, `.clinic.internal` | | method | how to authenticate | `scram-sha-256`, `peer`, `cert`, `ldap`, `gss`, `oauth`, `reject`, `trust` | | options | `name=value` pairs for the method | `map=localops`, `clientcert=verify-full` | Several names can share one field, separated by commas. An address can also be written as two columns, an IP address and a mask such as `255.255.255.0`. Besides records, the file accepts `include`, `include_if_exists` and `include_dir` directives that splice other files in place. ## The connection types - **`local`** matches Unix-domain socket connections only. Without a `local` record, socket connections are refused. A connection to `127.0.0.1` is TCP, not `local`. - **`host`** matches every TCP/IP connection: with or without SSL, with or without GSSAPI encryption. - **`hostssl`** matches TCP/IP only when the connection uses SSL/TLS. If the server runs with `ssl = off`, which is the default, the record is ignored apart from a logged warning that it cannot match anything. - **`hostnossl`** is the opposite: TCP/IP connections that do not use SSL. Paired with `reject`, it turns plaintext clients away explicitly. - **`hostgssenc`** and **`hostnogssenc`** make the same split for GSSAPI encryption. ## Keywords in the database and user columns - `all` matches everything; writing `"all"` in quotes turns it back into an ordinary name. - `sameuser` matches when the database has the same name as the role; `samerole` when the role is a member of a role named like the database. - `replication` matches **physical** replication connections, which name no database. Logical replication connections name a real database and are matched by ordinary database entries. - `+clinic_staff` matches any role that is a direct or indirect member of `clinic_staff`. Superusers count only if they are explicitly members. - A leading `/` makes the value a regular expression, and `@file` reads the names from a file. ## A clinic example After a security audit, a clinic-records cluster might end up with: ``` # TYPE DATABASE USER ADDRESS METHOD local all postgres peer hostssl clinic clinic_app 10.20.0.0/16 scram-sha-256 hostssl clinic +clinic_staff 10.30.0.0/24 scram-sha-256 hostnossl all all 0.0.0.0/0 reject hostnossl all all ::0/0 reject ``` The first line lets the `postgres` OS account in over the socket. The next two accept only TLS connections from the application and staff subnets, with SCRAM passwords. The last two make plaintext TCP fail with an explicit rejection; two lines are needed because an IPv4 entry never matches an IPv6 client. Without them, plaintext would still be refused, just with a `no pg_hba.conf entry` message instead. ## Mistakes beginners make 1. Adding a `host` line while `listen_addresses` is still at its default, `localhost`, so remote clients never reach the file at all. 2. Writing an IPv4 range and then connecting over IPv6. 3. Expecting `hostssl` lines to work before `ssl = on` and a server certificate are in place. 4. Editing the file and forgetting to reload it with `pg_ctl reload` or `SELECT pg_reload_conf()`. 5. Treating the file as the only gate. A role that passes it still needs the `CONNECT` privilege on the database, and that belongs to role and privilege management.

  • A correct-looking host line never matches a remote client. What do you check first?
    Whether the connection reaches PostgreSQL at all. `listen_addresses` defaults to `localhost`, so remote TCP is never accepted until it lists another interface, and changing it needs a restart. Then check the address family, because an IPv4 CIDR never matches an IPv6 client. For host-name entries, check that the reverse and forward DNS lookups agree. The server's `no pg_hba.conf entry for host` message shows the address PostgreSQL actually saw.
  • How do replication connections appear in pg_hba.conf?
    A physical replication connection, such as a standby's WAL receiver or `pg_basebackup`, names no database. It matches only records whose database column is `replication`, and `all` does not cover it. A logical replication connection names a real database and matches ordinary database entries. Either way, the role also needs the REPLICATION attribute, which is a role setting rather than something this file controls.

  • A host line matches only unencrypted TCP; hostssl is the encrypted one.
  • A local line also covers TCP connections to 127.0.0.1.
  • The replication keyword also matches logical replication connections.
  • A +group entry includes superusers automatically.
  • Once a host line exists, remote clients can connect whatever listen_addresses says.