skip to content

Login Methods & TLS

PostgreSQL matches each connection to the first fitting pg_hba.conf line, then runs its method: scram-sha-256, peer, cert or 18's oauth. Interviewers probe rule order, md5's deprecation and sslmode.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

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.
open as a page

Moving a PostgreSQL cluster from md5 to scram-sha-256, how do password_encryption and the pg_hba.conf method interact, and in what order do you switch them?

level: middleimportance: must knowfreq 60%

basics

~20 s

password_encryption decides how a newly set password is hashed; the pg_hba.conf method decides which exchange the server demands. An md5 line still accepts SCRAM-hashed roles, but a scram-sha-256 line fails MD5-hashed ones, so rehash every password first, then switch lines.

open as a page

After editing a PostgreSQL pg_hba.conf, how do you use pg_hba_file_rules and the server's FATAL messages to check the change before and after reloading?

level: middleimportance: should knowfreq 42%

basics

~20 s

Before reloading, query pg_hba_file_rules: it parses the file on disk and fills error for broken lines, and a broken file is not reloaded. Afterwards, the FATAL text and the log's Connection matched detail show which record decided.

open as a page

In PostgreSQL, how do peer and cert authentication identify a user without a password, and when do you need a pg_ident.conf map?

level: middleimportance: should knowfreq 35%

basics

~20 s

peer asks the kernel for the OS user on a Unix-socket connection; cert takes the client certificate's CN over TLS. Each must equal the requested role unless a pg_ident.conf map, named with map=, allows the pair.

open as a page

A PostgreSQL client connects with libpq's default sslmode; what does that actually guarantee, and what do sslmode=verify-full, sslrootcert=system and sslnegotiation=direct change?

level: seniorimportance: should knowfreq 48%

basics

~20 s

libpq's default sslmode=prefer tries TLS but silently falls back to plaintext and never checks the server certificate. verify-full adds CA and host-name checks, sslrootcert=system uses the OS trust store and forces verify-full, and sslnegotiation=direct starts TLS immediately against 17+ servers.

open as a page

What must be in place before a PostgreSQL 18 pg_hba.conf oauth record can admit a single sign-on user, and what does PostgreSQL itself not provide?

level: seniorimportance: nice to knowfreq 20%

basics

~20 s

PostgreSQL 18's oauth method needs a validator library in oauth_validator_libraries (empty by default, which refuses OAuth logins), issuer and scope on the pg_hba.conf record, and a token-capable client. PostgreSQL ships no validator and no authorization server.

open as a page