Skip to content
Tutorials Center
My learning

Topics

Lesson 76 of 90 ยท Security and Administration

Authentication, TLS and Encryption

Configure pg_hba.conf, SCRAM passwords, TLS, pgcrypto, disk encryption and pgaudit, and learn how to keep database secrets out of code and logs.

Before a query can run, PostgreSQL has to answer three questions: who is connecting, is the connection private, and is the stored data safe if someone gets hold of the disk. Those are authentication, transport encryption and encryption at rest. Each one is configured in a different place, and a gap in any of them undoes the others.

This lesson walks through all three, plus auditing and secrets handling. Everything about the server (its configuration files, TLS, extensions) cannot run in a browser, so the PostgreSQL parts are read-only. Two live stand-ins in SQLite let you practice the reasoning: how pg_hba.conf picks a rule, and how to expose less data through a masked view.

Three layers of protection

Authentication pg_hba.conf decides who may connect and how they prove it.
In transit TLS encrypts the wire between client and server.
At rest Disk, backup and column encryption protect stored data.

Auditing and good secret handling sit on top of all three.

1 / 2

pg_hba.conf: who may connect, and how

The file pg_hba.conf (HBA means host-based authentication) lives in the data directory and holds a list of rules. For each new connection PostgreSQL reads the rules from top to bottom and uses the first one that matches the connection type, database, user and client address. If nothing matches, the connection is refused. Each rule has this shape:

TEXT
# TYPE      DATABASE   USER       ADDRESS         METHOD
local       all        postgres                   peer
hostssl     shop       app_user   10.0.0.0/16     scram-sha-256
host        all        all        10.0.0.0/16     reject
host        all        postgres   127.0.0.1/32    scram-sha-256
  • TYPE: local is a Unix socket, host is TCP with or without TLS, hostssl requires TLS, hostnossl forbids it.
  • ADDRESS: an IP range in CIDR form such as 10.0.0.0/16, or a host name. Keep ranges as small as possible; 0.0.0.0/0 means "the whole internet".
  • METHOD: scram-sha-256 (password, the one to choose), peer (the operating-system user name, Unix sockets only), cert (a client TLS certificate), reject, and others such as LDAP or GSSAPI. The old md5 method still works in many versions but is weak; PostgreSQL 18 deprecates it, and trust (no check at all) must never face a network.

Order is everything. In the file above the reject line comes after the hostssl line, so app_user from inside the network can connect over TLS, while everybody else in that range (including postgres) is refused. Swap the two lines and nobody gets in. After editing the file, reload the server (SELECT pg_reload_conf(); or pg_ctl reload); you don't need a restart. The view pg_hba_file_rules shows how the server parsed the file and reports syntax errors.

You can model the "first match wins" logic in SQL. This stand-in stores rules in a table and picks, for each test connection, the matching rule with the smallest line number. The address is matched by prefix instead of real CIDR to keep it short:

SQL First matching pg_hba rule wins

Press Run (or Ctrl + Enter) to see the result here.

Attempt 2 shows why the order matters: the app user without TLS skips line 1 (which needs hostssl) and lands on the reject rule at line 2. Attempt 5 comes from an address no rule covers, so it is refused outright.

Passwords: use SCRAM

A password is never stored as plain text. Since PostgreSQL 14 the default is password_encryption = scram-sha-256: the server keeps a salted, iterated verifier, and the client proves it knows the password without ever sending it. Older roles may still hold md5 hashes. To convert them, make sure password_encryption is scram-sha-256, set each password again, then switch the pg_hba.conf method:

SQL
SHOW password_encryption;                  -- scram-sha-256
SELECT rolname, left(rolpassword, 14) FROM pg_authid WHERE rolpassword IS NOT NULL;
-- SCRAM-SHA-256$ means converted, md5... means not yet

(pg_authid is readable by superusers only.) Set passwords in psql with \password app_user: the client hashes the password before sending it, so the plain text doesn't land in the server log. A statement like ALTER ROLE app_user PASSWORD 'secret' can appear in the log, in the shell history and in pg_stat_activity. Also set listen_addresses to only the interfaces you need, and keep port 5432 behind a firewall.

TLS: encrypting the connection

Without TLS, passwords are protected by SCRAM but every query and result crosses the network readable. Enable TLS in postgresql.conf with a certificate and key:

TEXT
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'          # must not be readable by other users
ssl_min_protocol_version = 'TLSv1.2'

Switching on TLS on the server doesn't force clients to use it. Pair it with hostssl rules in pg_hba.conf and set sslmode on the client side. The modes differ a lot in what they guarantee:

sslmode Encrypts Checks the server certificate
disable no no
prefer (default) if the server offers it no
require yes no, so a man-in-the-middle is possible
verify-ca yes against a trusted authority
verify-full yes authority and host name

Use verify-full for anything that crosses an untrusted network, with sslrootcert pointing at your certificate authority. A client certificate (cert method, or clientcert=verify-full as a rule option) lets the server authenticate the client too.

Encryption at rest

Community PostgreSQL has no built-in transparent data encryption. You choose from these layers, and they protect against different threats:

  • Disk or volume encryption (LUKS, cloud provider volume encryption, encrypted file systems). It protects a stolen disk or snapshot. It does not protect against someone who can log in, because the operating system decrypts everything for the running server. Encrypt backups and WAL archives too.
  • Column encryption with pgcrypto. The extension offers functions such as pgp_sym_encrypt(data, key) and pgp_sym_decrypt(bytea, key), plus digest() and gen_random_bytes(). The big catch is that the key travels in the SQL statement, so it can appear in logs and pg_stat_statements. For values you need to look up, you also lose indexing and LIKE.
  • Application-level encryption. The application encrypts sensitive fields before the insert and the database only ever stores ciphertext. This is the strongest option for fields like government IDs, and it keeps keys out of the database entirely.
SQL
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Hashing a password: prefer your framework's Argon2 or bcrypt in the app.
-- If you must do it in SQL, use a slow salted hash and never a bare digest():
SELECT crypt('correct horse', gen_salt('bf', 12));

-- Verify: the stored hash is passed as the salt
SELECT stored_hash = crypt('correct horse', stored_hash) AS password_ok FROM users WHERE id = 1;

Don't build your own scheme out of md5() or sha256() on a plain password: they are fast, which is the opposite of what password storage needs.

Exposing less: masking and least data

The cheapest security is not selecting what a role does not need. A view can hide or mask sensitive columns, and you grant the role access to the view instead of the table. SQLite has no GRANT, but this live stand-in shows the masking logic itself:

SQL A masked view for support staff

Press Run (or Ctrl + Enter) to see the result here.

In PostgreSQL you would then REVOKE ALL ON customers FROM support_role; GRANT SELECT ON customers_support TO support_role;. Remember that a view runs with its owner's rights, which is exactly what makes this pattern work. See row-level security for the matching rule on views over protected tables.

Auditing with pgaudit

PostgreSQL's normal log records errors and slow statements, not an audit trail. The pgaudit extension writes structured audit records for chosen statement classes. It must be loaded at server start, so add it to shared_preload_libraries, restart, then create the extension:

TEXT
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl, role, write'
pgaudit.log_parameter = off     # leave off if parameters can contain secrets

The classes include read, write, function, role, ddl and misc. Auditing everything produces huge logs, so pick what your compliance rules require, ship the logs to a separate system, and make sure the people being audited can't edit them.

Handling secrets

  • Never commit passwords or connection strings. Read them from environment variables or a secrets manager at start-up.
  • Use a separate role per application with only the privileges it needs, and rotate passwords on a schedule. See roles and privileges.
  • For command-line tools, use a .pgpass file (permissions 0600) or PGPASSFILE rather than putting PGPASSWORD in scripts or passwords on the command line, where other users can see them in the process list.
  • Keep key material for column encryption outside the database, and keep it separate from the backups of that database.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Two pg_hba.conf rules both match an incoming connection. Which one is used?

    PostgreSQL reads the rules top to bottom and uses the first match, so rule order decides the outcome.
  2. 2. Which sslmode checks both the certificate authority and the server's host name?

    verify-full validates the certificate chain and that the certificate matches the host you connected to, which defeats man-in-the-middle attacks.
  3. 3. Full-disk encryption is enabled on the database server. What does it NOT protect against?

    The operating system decrypts the volume for the running server, so anyone who can query the database sees plain data. Disk encryption protects data at rest only.
  4. 4. Why is column encryption with pgp_sym_encrypt in SQL risky?

    The key is sent as a query argument, so it can be recorded by logging or pg_stat_statements. Application-level encryption keeps keys out of the database.

Summary

  • pg_hba.conf rules are read top to bottom, first match wins; choose scram-sha-256 and narrow address ranges, and never use trust on a network.
  • Passwords are stored as SCRAM verifiers by default; set them with \password so plain text isn't logged.
  • Turn on TLS on the server, require it with hostssl, and use sslmode=verify-full on clients.
  • PostgreSQL has no built-in transparent data encryption: combine volume encryption, encrypted backups and, for the most sensitive fields, application-level encryption. pgcrypto works but exposes keys to SQL.
  • Mask or hide sensitive columns with views and least-privilege grants.
  • pgaudit produces audit records; keep secrets out of code, command lines and logs.

Next: SQL Injection and Safe Queries in PostgreSQL.