skip to content

Database Security

PostgreSQL checks pg_hba.conf before a session exists, role privileges on every statement, and row policies inside each query. Interviewers probe all three layers and the superuser shortcuts.

part ofPostgreSQLread it →
on this pageshow

explore

questions

17

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

In PostgreSQL, what do the LOGIN, SUPERUSER, CREATEROLE and REPLICATION role attributes allow, and why doesn't role membership pass them on?

level: juniorimportance: must knowfreq 64%

basics

~20 s

LOGIN lets a role open a session, SUPERUSER skips every permission check except login, CREATEROLE creates and administers roles it holds ADMIN on, and REPLICATION opens WAL-sender connections. Attributes belong to the role itself, so members never inherit them.

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

In PostgreSQL, why do tables created by a migration login stay unreadable to analysts after an admin ran ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES?

level: middleimportance: must knowfreq 58%

basics

~20 s

ALTER DEFAULT PRIVILEGES applies only to objects later created by the role named in FOR ROLE, which defaults to whoever ran it. Tables the migration login creates use that login's own entries, so the admin's entry never fires.

open as a page

In PostgreSQL, which roles bypass a table's row-level security policies once it is enabled, and what does FORCE ROW LEVEL SECURITY change?

level: middleimportance: must knowfreq 62%

basics

~20 s

Superusers and roles with the BYPASSRLS attribute always bypass row security, and the table owner bypasses it by default. ALTER TABLE ... FORCE ROW LEVEL SECURITY subjects the owner to its policies; superusers and BYPASSRLS roles still bypass.

open as a page

In PostgreSQL, a tasks policy compares org_id with current_setting('app.org_id', true)::uuid; why does set_config's is_local argument matter, and what can current_setting return?

level: seniorimportance: must knowfreq 55%

basics

~20 s

set_config('app.org_id', id, true) keeps the tenant only until the transaction ends, so it cannot leak to the next request on a reused connection. current_setting(name, true) returns NULL if never set, but an empty string after a local value expires.

open as a page

In PostgreSQL, what happens to an ordinary role's SELECT, UPDATE and INSERT on a table after ENABLE ROW LEVEL SECURITY but before any CREATE POLICY?

level: juniorimportance: should knowfreq 48%

basics

~20 s

Default deny applies: SELECT returns zero rows and UPDATE or DELETE touch zero rows without error, while INSERT fails with a row-level security error. Superusers, BYPASSRLS roles and, unless FORCE is set, the table owner are unaffected.

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

In PostgreSQL 16 and later, what do the INHERIT, SET and ADMIN options on a role membership control, and which combination fits a migration login?

level: middleimportance: should knowfreq 42%

basics

~20 s

INHERIT makes the granted role's privileges usable without switching, SET allows SET ROLE to it and making it an object's owner, and ADMIN allows granting membership onward. A migration login fits SET TRUE, INHERIT FALSE, ADMIN FALSE on the owner role.

open as a page

In PostgreSQL 18, what do the predefined roles pg_read_all_data and pg_maintain grant, and what is risky about giving them to analysts?

level: middleimportance: should knowfreq 37%

basics

~20 s

pg_read_all_data acts as SELECT on every table, view and sequence plus USAGE on every schema; pg_maintain (17+) allows VACUUM, ANALYZE, CLUSTER, REINDEX, REFRESH MATERIALIZED VIEW and LOCK TABLE on every relation. Both are cluster-wide and cannot be narrowed per table.

open as a page

In PostgreSQL, how are several row-level security policies on one table combined when some are PERMISSIVE and others are RESTRICTIVE?

level: middleimportance: should knowfreq 45%

basics

~20 s

Applicable PERMISSIVE policies are OR-ed, applicable RESTRICTIVE policies are AND-ed, and the two results are AND-ed. With no permissive policy for that command, access is denied, so restrictive policies can only narrow what a permissive one grants.

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

In PostgreSQL, why is CREATE on the public schema combined with the default search_path a privilege-escalation risk, and what changed in PostgreSQL 15?

level: seniorimportance: should knowfreq 46%

basics

~20 s

With search_path "$user", public, anyone who can create objects in public can plant a function or operator that a privileged session's unqualified call resolves to. Since 15, new databases give PUBLIC only USAGE on public; upgraded databases keep CREATE until revoked.

open as a page

In PostgreSQL, DROP ROLE for a departed contractor fails because some objects depend on it; how do you remove the role safely across the cluster?

level: seniorimportance: should knowfreq 31%

basics

~20 s

In every database holding its objects, run REASSIGN OWNED BY the role TO a successor, then DROP OWNED BY the role to revoke its remaining grants and default-privilege entries; only then DROP ROLE, which is cluster-wide.

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

In PostgreSQL, why can a query on a row-level-security table stop using an index, and how do LEAKPROOF functions and security_barrier views relate?

level: seniorimportance: nice to knowfreq 24%

basics

~20 s

Policy conditions run before any user condition using a non-LEAKPROOF function, so that condition cannot drive an index scan: jsonb @> loses its GIN index for a role subject to RLS, while leakproof uuid equality keeps its index.

open as a page