Control access to PostgreSQL: pg_hba.conf, roles and enforced TLS

A PostgreSQL instance that is reachable is not necessarily protected. This lesson closes two flaws that an internal audit brings to light: a server that accepts connections in cleartext because a host line lets the client decide, and an application that runs with the owner role of its tables, and can therefore drop them. You write a hostssl line, you set up a dedicated certificate, you split privileges between three group roles, then you prove each result in the server and not in a file. It is aimed at whoever already administers an instance opened to the network.

Version used

The outputs of this lesson were captured with PostgreSQL 18.3 (Debian 18.3-1.pgdg12+1) on Debian 12. The lab runs on Debian 13 with major version 18: the commands are the same. The passages marked "captured on 18.6" were replayed on PostgreSQL 18.6 (Debian 13); the other outputs remain those of 18.3.

What you will learn

• Read a pg_hba.conf line and know which one applies to a connection
• Enforce encryption on the server side: ssl = on and hostssl together
• Set up a dedicated certificate whose private key is readable only by postgres
• Prove that a session is encrypted with pg_stat_ssl
• Split privileges into owner, writer and reader, and plan for future tables

Prerequisites

• a PostgreSQL 18 instance that listens on the network, with a host line for the appli role on the facturation database and a .pgpass in 0600, as the lesson Administer a PostgreSQL instance with psql leaves them;
• the distinction between a parameter that reloads and a parameter that waits for a restart, covered in Configure PostgreSQL;
• access to the superuser role through sudo -u postgres psql, and sudo access to write in /etc/postgresql/.

This lesson deals with access control: who gets in, through which channel, with which privileges. Auditing sensitive actions (who changed the schema, who read what) is not covered here; the basic logging settings are in Configure PostgreSQL. The server installation is in Install PostgreSQL.

Why does pg_hba.conf come before SQL privileges?

A connection goes through three checks in order, and each one ignores the next. pg_hba.conf first decides whether the attempt is accepted and how the identity is verified. Encryption then protects what travels once the door is crossed. SQL privileges only come into play at the end, when the session is open. A mistake in the first layer therefore cancels all the work done on roles: this is why the lesson follows this order.

The lab scenario is that of an internal audit on a machine db1.lab. Two findings: encryption is not required (the network line is a host), and the appli role owns its schema and its tables, while a reporting tool that only reads connects with the same role.

How do you read a pg_hba.conf line?

pg_hba.conf (Host-Based Authentication) is the list of access rules. Each line has five fields, and PostgreSQL applies the first line that matches the connection, from top to bottom.

TYPE    DATABASE    USER    ADDRESS    METHOD

Field	Meaning	Examples
TYPE	Connection channel	local (Unix socket), host (TCP), hostssl (encrypted TCP), hostnossl (cleartext TCP)
DATABASE	Target database(s)	all, facturation, sameuser
USER	Target role(s)	all, appli, +app_writer (members of a group)
ADDRESS	Client source network	127.0.0.1/32, 192.168.122.0/24, ::1/128
METHOD	Required proof of identity	peer, scram-sha-256, cert, reject

Swipe to see more

Here is the file as Debian ships it for local access:

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

local lines go through the Unix socket and the peer method, which compares the system user to the PostgreSQL role without a password. host lines go through TCP and require scram-sha-256. Network access for an application is added with one more line, like the one the lesson on psql set up for appli.

Which authentication method should you choose?

The METHOD field sets the proof of identity asked of the client. Not all of them work everywhere: peer asks the kernel which system user is at the other end of the socket, so it only works on local lines.

Method	How it works	Verdict
trust	Lets in without asking anything	To be banned from a network line
peer	Compares the system user to the role, Unix socket only	Good choice for postgres locally
password	Password sent as is	To be avoided
md5	Password hashed with MD5	Deprecated in PostgreSQL 18
scram-sha-256	Verifies the password without sending it in cleartext	Default choice for TCP
cert	TLS certificate presented by the client	Very strong, for services and automation
reject	Refuses the connection	Explicitly closes a role or a network

Swipe to see more

The PostgreSQL documentation allows trust in very limited cases, such as a single-user workstation. The baseline of this training bans it from network lines: a trust on a host line lets any client of the network in as a superuser.

In what order should you write the lines?

Since the first line that matches wins, a generic line placed too high masks the restrictive lines that follow it.

# ❌ Line 1 matches before line 2, the reject is useless
host    all    all       192.168.122.0/24    scram-sha-256
host    all    intrus    192.168.122.0/24    reject

# ✅ The reject is evaluated first
host    all    intrus    192.168.122.0/24    reject
host    all    all       192.168.122.0/24    scram-sha-256

To avoid guessing how the server read the file, query the pg_hba_file_rules view: the error column flags malformed lines.

SELECT rule_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
ORDER BY rule_number;

 rule_number | type  |   database    | user_name  |  address  |  auth_method
-------------+-------+---------------+------------+-----------+---------------
           1 | local | {all}         | {postgres} |           | peer
           2 | local | {all}         | {all}      |           | peer
           3 | host  | {all}         | {all}      | 127.0.0.1 | scram-sha-256
           4 | host  | {all}         | {all}      | ::1       | scram-sha-256
           5 | local | {replication} | {all}      |           | peer
           6 | host  | {replication} | {all}      | 127.0.0.1 | scram-sha-256
           7 | host  | {replication} | {all}      | ::1       | scram-sha-256

What shape should a restrictive file take?

A restrictive file lists the legitimate accesses, then explicitly closes the rest. The following example is illustrative and serves an application network 10.0.1.0/24: the superuser stays on the socket, the application and a monitoring account are confined to their database and their network, and the last two lines refuse everything that found no earlier rule.

# TYPE   DATABASE        USER            ADDRESS              METHOD
local    all             postgres                             peer
hostssl  facturation     appli           10.0.1.0/24          scram-sha-256
hostssl  facturation     supervision     10.0.1.50/32         scram-sha-256
host     all             all             0.0.0.0/0            reject
host     all             all             ::0/0                reject

Note what the reject lines change for a client that forgets encryption: without them, it receives FATAL: no pg_hba.conf entry for host "…", user "…", database "…", no encryption; with them, it receives FATAL: pg_hba.conf rejects connection for host …. In both cases, the connection is refused; only the message differs.

Keep a local session open

A badly written pg_hba.conf can lock you out. If the file contains a syntax error, the reload keeps the old rules and the error goes to the log; but a syntactically valid and wrong rule cuts your own TCP connections. Work with a sudo -u postgres psql session already open: the peer socket does not depend on the host lines.

How do you force encryption: ssl and hostssl, together?

Two settings govern encryption, in two files, and neither is enough alone. ssl in postgresql.conf says whether the server can encrypt. The line type in pg_hba.conf says whether the server requires it: host accepts an encrypted session and a cleartext one without distinction, so it is the client that decides, which imposes nothing. hostssl refuses any unencrypted session, on the server side.

ssl	Line type	What happens
on	host	Encrypted if the client asks, cleartext otherwise: nothing is enforced
on	hostssl	Cleartext is refused: this is the target state
off	hostssl	Inert line: the server starts, but when it reads pg_hba.conf it discards the line, which protects nothing. The log carries hostssl record cannot match because SSL is disabled (a LOG level message, captured on 18.6) and the error column of pg_hba_file_rules exposes it too
off	host	Everything happens in cleartext

Swipe to see more

The first row of the table is the audit trap: a file check sees ssl = on and declares the instance compliant, while a cleartext connection succeeds. On Debian, ssl = on is already set by the package: enabling it therefore changes nothing, and all the work lies in the pg_hba.conf line. Do not assume it, read it in the server:

SHOW ssl;

 ssl
-----
 on

How do you set up a dedicated certificate?

The certificate Debian sets up at installation (ssl-cert-snakeoil) is the same on every machine in the fleet: it encrypts, but identifies nobody. For a server named db1.lab, generate a key and certificate pair whose common name (CN) is the server's, then lock down the key: PostgreSQL refuses to load a private key more open than 0600 when postgres owns it, and it says so in the log rather than on screen.

sudo openssl req -new -x509 -days 365 -nodes \
  -keyout /etc/postgresql/18/main/server.key \
  -out /etc/postgresql/18/main/server.crt \
  -subj "/CN=db1.lab"
sudo chown postgres:postgres /etc/postgresql/18/main/server.key
sudo chmod 600 /etc/postgresql/18/main/server.key

A certificate sitting on disk that nobody declares is useless. On Debian, postgresql.conf already contains two lines that point to the snakeoil: replace their values instead of adding second ones, keeping a single configuration strategy as the lesson Configure PostgreSQL recommends.

ssl_cert_file = '/etc/postgresql/18/main/server.crt'
ssl_key_file  = '/etc/postgresql/18/main/server.key'

These three parameters (ssl, ssl_cert_file, ssl_key_file) have the sighup context: since PostgreSQL 10, the SSL configuration reloads without a restart, and enabling ssl therefore does not require one either. One exception: a key protected by a passphrase does not reload, which the key generated above (-nodes) avoids. Do not rely on memory anyway: apply the method of the configuration lesson, reload then read. The context column says how the parameter is changed, and pending_restart says whether the server is still waiting.

SELECT pg_reload_conf();
SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name IN ('ssl', 'ssl_cert_file', 'ssl_key_file');

The value of ssl_cert_file must be the new path. Then check the common name of the certificate the server declares: openssl x509 -noout -subject -in /etc/postgresql/18/main/server.crt prints a subject= line ending in db1.lab.

A self-signed certificate encrypts, it does not authenticate

This certificate is self-signed: no authority attests that db1.lab is really this server. Encryption works, but a client that asks for sslmode=verify-ca or verify-full fails by construction, for lack of an authority to trust. In production, use a certificate signed by an internal PKI or Let's Encrypt. Generating an internal authority is covered in Générer des certificats TLS avec openssl (in French), and reading a certificate error in Diagnostic TLS (in French).

How do you refuse cleartext on the server side?

The fix comes down to one word: in the appli role's network line, replace host with hostssl. The rest of the line does not change, and neither do the local lines: administration goes through the socket.

# Before: the client decides
host     facturation    appli    192.168.122.0/24    scram-sha-256

# After: the server requires
hostssl  facturation    appli    192.168.122.0/24    scram-sha-256

pg_hba.conf reloads, without a restart and without cutting open sessions: sudo systemctl reload postgresql@18-main, or SELECT pg_reload_conf(); from psql. The new rules apply to upcoming connections; check in pg_hba_file_rules that the line is read, with an empty error.

You prove it through behaviour, from the student account, with two opposing commands. The PGSSLMODE variable sets the mode requested on the client side: disable forbids encryption, require demands it. The address is that of the machine on the private network.

ADDRESS=$(hostname -I | cut -d' ' -f1)

# Must FAIL
PGSSLMODE=disable psql -w -h "$ADDRESS" -U appli -d facturation -c 'select 1'

# Must SUCCEED, and declare itself encrypted
PGSSLMODE=require psql -w -h "$ADDRESS" -U appli -d facturation \
  -At -c "select ssl from pg_stat_ssl where pid = pg_backend_pid()"

The first command must end with FATAL: no pg_hba.conf entry for host "…", user "appli", database "facturation", no encryption. The second must print t. To see more than this boolean, the pg_stat_ssl view details the session:

SELECT ssl, version, cipher, bits
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

 ssl | version |         cipher         | bits
-----+---------+------------------------+------
 t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256

pg_stat_ssl is what the server says about the session, not what the client believes. The two commands guard each other: a refused cleartext connection proves little if the server is down, and an encrypted connection that succeeds proves nothing if cleartext passes too. If both fail, the hostssl line does not match the role, the database or the network, or ssl is off.

What does sslmode guarantee on the client side?

On the client side, sslmode (or PGSSLMODE) decides the level of verification. It complements hostssl without replacing it: the server imposes encryption, the client chooses whether it verifies the server's identity.

sslmode	Encrypts	Verifies the certificate	Verifies the host name
disable	No	No	No
allow, prefer	If possible	No	No
require	Yes	No	No
verify-ca	Yes	Yes	No
verify-full	Yes	Yes	Yes

Swipe to see more

The PostgreSQL documentation judges prefer, the default, meaningless from a security standpoint: an attacker on the network can force an unencrypted session. require encrypts but does not protect against a man-in-the-middle, who can present their own certificate. Only verify-full, with a certificate signed by an authority the client knows, verifies the server in addition to encrypting.

How do you authenticate with scram-sha-256 rather than md5?

A role's password is stored in a form chosen by password_encryption. PostgreSQL 18 deprecates MD5 (the documentation announces a removal in a future version, not yet in effect): creating a role with an MD5 password prints the warning setting an MD5-encrypted password, and SCRAM (scram-sha-256) is the default value. Passwords created by an older version may remain in MD5: read their prefix in the catalog rather than assuming.

SELECT rolname, LEFT(rolpassword, 14) AS pass_prefix
FROM pg_authid WHERE rolpassword IS NOT NULL;

  rolname  |  pass_prefix
-----------+----------------
 admin_lab | SCRAM-SHA-256$
 alice     | SCRAM-SHA-256$
 bob_ro    | SCRAM-SHA-256$

This output comes from another instance than the one in the lesson's examples: it lists the roles admin_lab, alice and bob_ro, where you have appli, app_* and supervision. Read the shape of the result, not the names: on your instance, you will see your own.

A SCRAM-SHA-256$ prefix everywhere means no password is in MD5. To migrate a role left in MD5, check that password_encryption is scram-sha-256, then reassign its password with ALTER ROLE … PASSWORD.

How do you separate privileges: owner, writer, reader?

Least privilege gives each actor what it needs and nothing more. The pattern of this lesson rests on three group roles, roles with no login privilege (NOLOGIN) that carry privileges, to which login roles are then attached. Removing an access then amounts to removing a membership, without rewriting any GRANT.

Group role	Privileges	Who uses it
app_owner	Owns the schema and the tables, hence CREATE, ALTER, DROP	Migrations
app_writer	SELECT, INSERT, UPDATE, DELETE on the tables	The application
app_reader	SELECT only	Reporting, monitoring

Swipe to see more

In the lab, the schema is called appli, like the login role that owns it today, and it contains the clients and commandes tables. The following commands run as superuser on the facturation database (sudo -u postgres psql -d facturation), because only a superuser can transfer the ownership of objects to another role. The steps follow in this order: without groups there is nobody to transfer to, and without an owner there is nobody to base the default privileges on.

1. Create the three groups, with no login privilege:

   CREATE ROLE app_owner NOLOGIN;
   CREATE ROLE app_writer NOLOGIN;
   CREATE ROLE app_reader NOLOGIN;

2. Transfer the ownership of the schema, the tables and the database to the owner. After this step, appli no longer owns anything:

   ALTER SCHEMA appli OWNER TO app_owner;
   ALTER TABLE appli.clients OWNER TO app_owner;
   ALTER TABLE appli.commandes OWNER TO app_owner;
   ALTER DATABASE facturation OWNER TO app_owner;

3. Grant the privileges on the objects that already exist. Without USAGE on the schema, the privileges on the tables are unreachable:

   GRANT USAGE ON SCHEMA appli TO app_writer, app_reader;
   GRANT SELECT ON ALL TABLES IN SCHEMA appli TO app_reader;
   GRANT SELECT, INSERT, UPDATE, DELETE
     ON ALL TABLES IN SCHEMA appli TO app_writer;
   GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA appli TO app_writer;

4. Plan for future tables with ALTER DEFAULT PRIVILEGES, explained right after:

   ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA appli
     GRANT SELECT ON TABLES TO app_reader;
   ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA appli
     GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;
   ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA appli
     GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

5. Attach the login role to the writer group:

   GRANT app_writer TO appli;

The sequence in step 3 covers auto-incremented columns: without USAGE on the sequence, an INSERT that relies on it is refused, even with the INSERT privilege on the table. The appli role inherits the privileges of app_writer through its membership. The transfer of the database to app_owner is not graded by the lab: if appli owned the database, this transfer removes its right to drop it. \l displays the owner, and a DROP DATABASE facturation run by appli then answers ERROR: must be owner of database facturation (captured on 18.6).

Why do you need default privileges?

GRANT … ON ALL TABLES IN SCHEMA only targets the tables that exist at the time of the command. A table created tomorrow by a migration will carry no privilege for app_writer or app_reader: everything is correct on the day of setup, and wrong at the first migration. ALTER DEFAULT PRIVILEGES covers the future, and the catalog keeps it in pg_default_acl, which the \ddp meta-command displays:

\ddp

                 Default access privileges
   Owner   | Schema |   Type   |     Access privileges
-----------+--------+----------+---------------------------
 app_owner | appli  | sequence | app_writer=rU/app_owner
 app_owner | appli  | table    | app_writer=arwd/app_owner+
           |        |          | app_reader=r/app_owner

This output was captured with the three ALTER DEFAULT PRIVILEGES of step 4, on PostgreSQL 18.6 (official Docker image, Debian 13). The privilege letters mean the following: r for SELECT, a for INSERT, w for UPDATE, d for DELETE, U for USAGE on a sequence.

Default privileges depend on the creator

A default privilege only applies to objects created by the role named in FOR ROLE. If postgres or another role creates a table in the appli schema, app_writer and app_reader will have no access to this new object. The PostgreSQL documentation confirms it: when an object is created, only the default privileges of the current role apply, with no inheritance from the groups it belongs to. So start every migration script with SET ROLE app_owner;.

How do you check privileges in the catalog?

A .sql file left on disk does not prove what the server recorded. The catalog answers table by table with has_table_privilege: read it. This query compares, for each table of the schema, what the reader and the writer can do:

SELECT t.tablename,
       has_table_privilege('app_reader', 'appli.' || t.tablename, 'SELECT') AS reader_reads,
       has_table_privilege('app_reader', 'appli.' || t.tablename, 'DELETE') AS reader_deletes,
       has_table_privilege('app_writer', 'appli.' || t.tablename, 'DELETE') AS writer_deletes
FROM pg_tables t
WHERE t.schemaname = 'appli';

Each row must show t, f, t: the reader reads, the reader does not delete, the writer deletes rows. For ownership, \dt appli.* displays the Owner column, which must be app_owner for every table.

The proof of default privileges consists of creating a table the way a migration would, then reading the reader's privileges. Then drop the witness table: otherwise the lab report would count one more readable table.

SET ROLE app_owner;
CREATE TABLE appli.table_temoin (id int);
RESET ROLE;
SELECT has_table_privilege('app_reader', 'appli.table_temoin', 'SELECT');
DROP TABLE appli.table_temoin;

The read query must return t. Finally, check that the login role is no longer the owner. The BEGIN and ROLLBACK pair protects the table if, by mistake, the drop went through: without it, a badly transferred ownership would destroy appli.clients.

psql -w -h "$ADDRESS" -U appli -d facturation \
  -c 'BEGIN' -c 'DROP TABLE appli.clients' -c 'ROLLBACK'

PostgreSQL must answer ERROR: must be owner of table clients: appli writes to the tables, it can no longer drop them. With several -c, psql runs each command in turn and continues after an error: the ROLLBACK therefore goes through in both outcomes, and the table survives even if the DROP had succeeded. The psql exit code stays 0 despite the error (captured on 18.6): in a script, do not test it to know whether the DROP was refused, read the message.

What does PostgreSQL 15 change for the public schema?

Before version 15, all roles could create objects in the public schema, a classic attack vector through the search_path. Since 15, this privilege is revoked by default on a new database of that version or a later one, like the lab's. You can check it with a query:

SELECT has_schema_privilege('public', 'public', 'CREATE') AS public_can_create;

The expected answer is f. The lab does not grade this point, which it considers a given.

Databases coming from version 14 or earlier

A database restored or migrated from 14 or an older version did not get this revocation automatically. On these databases, run REVOKE CREATE ON SCHEMA public FROM PUBLIC;.

What is left to close around the database?

A service that does not listen on an interface cannot be attacked from it, whatever the quality of the authentication. listen_addresses (a postmaster parameter, which requires a restart) is set as in the lesson on psql: listen on the application network address, never * without reason. The system firewall adds a layer independent of pg_hba.conf: its guides are Linux firewalls: understand before you configure, UFW and nftables. A connection pooler such as PgBouncer, placed in front of the database, funnels network traffic through a single entry point.

Two finer access controls exist, outside the scope of this lesson. Row-Level Security (ALTER TABLE … ENABLE ROW LEVEL SECURITY and CREATE POLICY) restricts a role to certain rows of a table, useful in multi-tenant setups when the table-level privilege is too broad. OAuth 2.0 authentication, new in PostgreSQL 18, delegates identity to an external provider: it requires a configured identity provider, a token validation library and the oauth_validator_libraries parameter, empty by default, in which case OAuth connections are refused.

Put it into practice

The lab starts from an instance that listens on the network and whose network line is a host, with the Debian certificate and an appli role that owns its schema. You set up a dedicated certificate with common name db1.lab whose key is in 0600 under postgres, you turn the network line into a hostssl, you create the three group roles, transfer the ownership, grant the privileges and plan for future tables, then you fill in a report of three values read from the server. The validation is about behaviour: a cleartext connection must fail and an encrypted connection succeed while declaring itself encrypted in pg_stat_ssl, the privileges are read in the catalog, and each value of the report is compared with what the server answers.

Guided lab Practice

Learn how to do it

Refuse cleartext connections and apply least privilege

$ dsoxlab run pg-04-securiser

Environment: VM·Duration: ~40 min

How it is checked: the state of the system is checked after a reboot, not the command you typed.

First time? What DSOXLab is and how to install it

DSOXLab is the command line that prepares a disposable environment (an isolated shell or a VM), runs the lab scenario, then checks the real state of the system and computes a score. Nothing is installed or broken on your workstation.

It needs Python 3.11 or later, uv or pipx, and git. The installation guide details these prerequisites; on Windows, or on a Mac without Python, use the appliance instead.

This lab starts virtual machines: it also needs ansible-playbook, a hypervisor (KVM or Incus), Terraform and hardware virtualization (/dev/kvm), which inside a VirtualBox VM means nested virtualization. The first time, dsoxlab start pg-04-securiser checks all of it before creating the machines. Prepare your machine for vm labs.

Install the CLI and add the catalogue, once:

uv tool install dsoxlab   # or: pipx install dsoxlab
dsoxlab catalog add https://github.com/stephrobert/bdd-dsoxlab-training

Before you run the lab, check:

• DSOXLab installed
• catalogue available
• compatible environment (isolated shell or VM)

To check, nothing is probed from this page: run dsoxlab doctor. Read the diagnosis

Run this lab, then validate your work:

dsoxlab run pg-04-securiser
dsoxlab check pg-04-securiser

See every lab: dsoxlab list-labs. Track your progress: dsoxlab progress. The full dsoxlab guide covers scoring and the pitfalls.

Troubleshooting

Hardening a database mechanically produces refusals, and the difficulty is telling a wanted refusal from a configuration error. PostgreSQL's messages name the layer that blocked: pg_hba.conf for a connection refusal, privileges for an action refusal. Find the keyword of the message in the left column: it points to the file or command to fix.

Symptom	Likely cause	Fix
FATAL: no pg_hba.conf entry for host "…", user "…", database "…", no encryption	A hostssl line exists and the client asked for cleartext (wanted), or no line matches	On the client side, PGSSLMODE=require; otherwise add the line and check the order
FATAL: pg_hba.conf rejects connection for host …	A reject line matches before any accept line	Place the accept line above, reload, read pg_hba_file_rules again
Cleartext and encrypted both fail	The hostssl line does not match the role, the database or the network, or ssl is off	SHOW ssl; then pg_hba_file_rules
Cleartext still succeeds after the reload	The line is still a host, or another host line matches higher up	Read pg_hba_file_rules again: the first line that matches wins
The server does not load the certificate	Private key more open than 0600, or owner other than postgres	chown postgres:postgres and chmod 600, then read journalctl -u postgresql@18-main
root certificate file … does not exist	sslmode=verify-ca without the authority's certificate on the client side	Provide the authority (sslrootcert), or stay on require with a self-signed certificate
permission denied for table …	The role does not have the requested privilege	GRANT … TO app_writer or app_reader, and check the membership
permission denied for schema …	USAGE on the schema is missing	GRANT USAGE ON SCHEMA appli TO app_writer, app_reader;
A table created afterwards is unreadable for the reader	It was not created by app_owner, default privileges do not apply	Create it under SET ROLE app_owner;, or grant the privileges by hand on this table
must be owner of table …	The role is not the owner: expected behaviour for the writer	Route the schema change through app_owner
pg_reload_conf() changes nothing	Syntax error in pg_hba.conf	Read the error column of pg_hba_file_rules and the log

Swipe to see more

When a connection fails, test in the order of the chain: the line that applies (pg_hba_file_rules), the encryption (SHOW ssl;, then pg_stat_ssl), finally the privileges (has_table_privilege). A connection refusal is never fixed with a GRANT, and an action refusal is never fixed in pg_hba.conf.

Key takeaways

• A connection goes through pg_hba.conf, then encryption, then SQL privileges; the first line that matches wins, reject lines go at the top.
• The peer (Unix socket) and scram-sha-256 (TCP) methods are fine; trust and password have no place on a network line, and md5 is deprecated.
• ssl = on alone enforces nothing: with a host line, the client decides. It is hostssl that makes the server refuse cleartext, and it only counts if ssl is on.
• A dedicated certificate requires a private key in 0600 owned by postgres, declared in ssl_cert_file and ssl_key_file; a self-signed certificate encrypts but does not allow verify-full.
• The proof of encryption is a pair: the cleartext connection fails, the encrypted connection succeeds and pg_stat_ssl answers t.
• Three group roles with no login privilege separate who creates, who writes, who reads; the login role becomes a member and owns nothing.
• GRANT … ON ALL TABLES only targets what exists: ALTER DEFAULT PRIVILEGES FOR ROLE app_owner covers future tables, provided they are created by that role.
• Privileges are checked in the catalog (has_table_privilege, pg_default_acl), never in a file.

Knowledge check

Eight questions on the first line that matches in pg_hba.conf, the ssl and hostssl pair, the private key of a dedicated certificate, the group roles of least privilege and the default privileges of future tables.

Knowledge check

Check what you have learned with this interactive quiz

8 questions

8 min

70% to pass

How it works

• The timer starts when you click Start
• Multiple choice, true/false and short answer questions
• You can move between questions
• Detailed results are shown at the end

Start the quiz

Starts the quiz and the timer

×

See in the glossary →