Skip to content
Hands-on IT IT Drills

Day 3: Configuration and Access

Goal: You find every parameter along with its source, change it via ALTER SYSTEM, know when a reload is enough and when a restart is needed, control access through pg_hba.conf and secure connections with your own CA. · Duration: approx. 75 min · Prerequisite: Day 0

Briefing

  • On Ubuntu, the configuration lives in /etc/postgresql/18/main; ALTER SYSTEM writes to postgresql.auto.conf in the data directory – and that one wins.
  • Every parameter has a context: user (immediately, per session), sighup (reload), postmaster (restart).
  • pg_hba.conf is read from top to bottom; the first matching line decides.
  • ssl = on with “snakeoil” encrypts, but doesn’t prove who you’re talking to. Only your own CA plus sslmode=verify-full does that.

All commands run on pg1 unless it says pg2.

Drill 1: Find files and sources

psql -Atc 'SHOW config_file' -c 'SHOW hba_file' -c 'SHOW data_directory'
ls /etc/postgresql/18/main/
grep -nE '^(include|data_directory|hba_file)' /etc/postgresql/18/main/postgresql.conf

Expected:

/etc/postgresql/18/main/postgresql.conf
/etc/postgresql/18/main/pg_hba.conf
/var/lib/postgresql/18/main
conf.d  environment  pg_ctl.conf  pg_hba.conf  pg_ident.conf  postgresql.conf
start.conf
42:data_directory = '/var/lib/postgresql/18/main'       # use data in another directory
44:hba_file = '/etc/postgresql/18/main/pg_hba.conf' # host-based authentication file
879:include_dir = 'conf.d'          # include files ending in '.conf' from

The reading order: postgresql.conf, then conf.d/*.conf, finally postgresql.auto.conf. With duplicate entries, the one read last wins. pg_settings shows which value applies and where it comes from:

psql -c "SELECT name, setting, unit, context, source FROM pg_settings
  WHERE name IN ('shared_buffers', 'work_mem', 'listen_addresses',
  'max_connections', 'effective_cache_size') ORDER BY name;"
psql -c "SELECT context, count(*) FROM pg_settings GROUP BY 1 ORDER BY 2 DESC;"

Expected:

         name         | setting | unit |  context   |       source
----------------------+---------+------+------------+--------------------
 effective_cache_size | 524288  | 8kB  | user       | default
 listen_addresses     | *       |      | postmaster | configuration file
 max_connections      | 100     |      | postmaster | configuration file
 shared_buffers       | 16384   | 8kB  | postmaster | configuration file
 work_mem             | 4096    | kB   | user       | default
(5 rows)

      context      | count
-------------------+-------
 user              |   151
 sighup            |   104
 postmaster        |    69
 superuser         |    50
 internal          |    20
 superuser-backend |     4
 backend           |     2
(7 rows)

setting is given in the unit from unit: 16384 × 8 kB = 128 MB. internal are fixed values such as block_size. pg_file_settings shows what’s in the files (including values not yet applied):

psql -c "SELECT sourcefile, sourceline, name, setting, applied FROM pg_file_settings
  WHERE name IN ('shared_buffers', 'listen_addresses', 'max_connections');"

Expected:

                    sourcefile                    | sourceline |       name       | ...
--------------------------------------------------+------------+------------------+----
 /etc/postgresql/18/main/postgresql.conf          |         65 | max_connections  | ...
 /etc/postgresql/18/main/postgresql.conf          |        132 | shared_buffers   | ...
 /var/lib/postgresql/18/main/postgresql.auto.conf |          3 | listen_addresses | ...
(3 rows)

Save your progress? Sign up for free

Drill 2: ALTER SYSTEM and reload

work_mem has the context user – a reload is enough, new values apply immediately to new queries:

psql -c "ALTER SYSTEM SET work_mem = '16MB';"
sudo cat /var/lib/postgresql/18/main/postgresql.auto.conf
psql -Atc 'SHOW work_mem'
psql -Atc 'SELECT pg_reload_conf()'
psql -Atc 'SHOW work_mem'

Expected:

ALTER SYSTEM
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
work_mem = '16MB'
4MB
t
16MB

ALTER SYSTEM only changes the file; only the reload (SIGHUP) reads it again. Equivalent: sudo systemctl reload postgresql@18-main. Values can also be set more narrowly – for the session, a role or a database:

psql -c "SET work_mem = '64MB'" -c 'SHOW work_mem'
psql -c "CREATE ROLE tag03_report LOGIN PASSWORD 'lab-tag03'"
psql -c "ALTER ROLE tag03_report SET work_mem = '256MB'"
PGPASSWORD=lab-tag03 psql -h 127.0.0.1 -U tag03_report -d notes \
  -Atc 'SHOW work_mem' -c "SELECT source FROM pg_settings WHERE name = 'work_mem'"
psql -c '\drds'

Expected (abridged):

SET
 work_mem
----------
 64MB
(1 row)

CREATE ROLE
ALTER ROLE
256MB
user
             List of settings
     Role     | Database |    Settings
--------------+----------+----------------
 tag03_report |          | work_mem=256MB
(1 row)

Precedence from weak to strong: file, database (ALTER DATABASE … SET), role, role in database, SET in the session. The password is a lab value.

Save your progress? Sign up for free

Drill 3: Restart parameters and rules of thumb

shared_buffers is PostgreSQL’s cache in shared memory – context postmaster. A reload doesn’t load it:

psql -c "ALTER SYSTEM SET shared_buffers = '2GB';"
psql -Atc 'SELECT pg_reload_conf()'
psql -c "SELECT name, setting, unit, pending_restart FROM pg_settings
  WHERE pending_restart;"
psql -c "SELECT name, setting, applied, error FROM pg_file_settings
  WHERE name = 'shared_buffers';"
sudo tail -n 3 /var/log/postgresql/postgresql-18-main.log

Expected (abridged):

      name      | setting | unit | pending_restart
----------------+---------+------+-----------------
 shared_buffers | 16384   | 8kB  | t
(1 row)

      name      | setting | applied |            error
----------------+---------+---------+------------------------------
 shared_buffers | 128MB   | f       |
 shared_buffers | 2GB     | f       | setting could not be applied
(2 rows)

... LOG:  received SIGHUP, reloading configuration files
... LOG:  parameter "shared_buffers" cannot be changed without restarting the server
... LOG:  configuration file "/var/lib/postgresql/18/main/postgresql.auto.conf"
contains errors; unaffected changes were applied

pending_restart = t is the view your monitoring should query. Now the restart with the Debian tool:

psql -Atc 'SELECT pg_postmaster_start_time()'
sudo pg_ctlcluster 18 main restart
psql -Atc 'SELECT pg_postmaster_start_time()' -c 'SHOW shared_buffers' \
  -c 'SELECT count(*) FROM pg_settings WHERE pending_restart'

Expected:

2026-11-19 04:42:46.985518+00
2026-11-19 04:50:30.658521+00
2GB
0

Under systemd, pg_ctlcluster redirects to systemctl restart postgresql@18-main – both ways are the same. A restart drops all connections.

Rules of thumb as starting values, then measure (Day 7):

Parameter Starting value Meaning
shared_buffers 25 % RAM own cache, restart
effective_cache_size 50–75 % RAM planner assumption only, allocates nothing
work_mem 4–64 MB per sort/hash step, not per session
max_connections 100–300 every connection is a process

work_mem is the dangerous number: a query with three sorts and 200 connections can use 600 × work_mem. Better to keep it small globally and raise it specifically for report roles (Drill 2). Many clients belong behind a pooler (Day 12), not in a higher max_connections.

Save your progress? Sign up for free

Drill 4: Read and change pg_hba.conf

Each line: type, database, role, address, method. The first matching line decides – including reject:

H=/etc/postgresql/18/main/pg_hba.conf
cat <<'EOF' | sudo tee -a $H
host  notes  tag03_report  172.16.0.123/32  reject
host  notes  tag03_report  172.16.0.0/24    scram-sha-256
EOF
psql -Atc 'SELECT pg_reload_conf()'
psql -c "SELECT line_number AS nr, type, database, user_name, address, auth_method
  FROM pg_hba_file_rules WHERE user_name <> '{all}';"

Expected (abridged):

 nr  | type  | database |   user_name    |   address    |  auth_method
-----+-------+----------+----------------+--------------+---------------
 118 | local | {all}    | {postgres}     |              | peer
 133 | host  | {notes}  | {notes_app}    | 172.16.0.0   | scram-sha-256
 134 | host  | {notes}  | {tag03_report} | 172.16.0.123 | reject
 135 | host  | {notes}  | {tag03_report} | 172.16.0.0   | scram-sha-256
(4 rows)

On pg2:

PGPASSWORD=lab-tag03 psql -h 172.16.0.182 -U tag03_report -d notes -Atc 'SELECT 1'

Expected (abridged):

psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:
pg_hba.conf rejects connection for host "172.16.0.123", user "tag03_report",
database "notes", SSL encryption

Line 134 matches first, line 135 is never reached. Back on pg1: delete the reject line and deliberately add a typo. pg_hba_file_rules reads the file fresh and checks it before the reload:

sudo sed -i '/tag03_report  172.16.0.123\/32  reject$/d' $H
echo 'host  notes  tag03_report  172.16.0.0/24  scram-sha-265' | sudo tee -a $H
psql -c "SELECT line_number AS nr, error FROM pg_hba_file_rules
  WHERE error IS NOT NULL;"
psql -Atc 'SELECT pg_reload_conf()'
sudo tail -n 2 /var/log/postgresql/postgresql-18-main.log
sudo sed -i '$d' $H
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL' \
  -c 'SELECT pg_reload_conf()'

Expected (abridged):

 nr  |                     error
-----+-----------------------------------------------
 135 | invalid authentication method "scram-sha-265"
(1 row)

t
... CONTEXT:  line 135 of configuration file "/etc/postgresql/18/main/pg_hba.conf"
... LOG:  /etc/postgresql/18/main/pg_hba.conf was not reloaded
0
t

A broken pg_hba.conf doesn’t take down the running server – it keeps the old version. On the next restart, however, it would not come up. Therefore: check pg_hba_file_rules first, then reload. Now tag03_report gets in from pg2:

PGPASSWORD=lab-tag03 psql -h 172.16.0.182 -U tag03_report -d notes \
  -Atc 'SELECT current_user, inet_client_addr()'

Expected:

tag03_report|172.16.0.123

Save your progress? Sign up for free

Drill 5: scram-sha-256 instead of md5

MD5 passwords are deprecated as of PostgreSQL 18. We deliberately create a role with an old hash, the kind still lying around after an upgrade from old versions:

psql -Atc 'SHOW password_encryption'
psql <<'EOF'
SET password_encryption = 'md5';
CREATE ROLE tag03_alt LOGIN PASSWORD 'lab-tag03';
RESET password_encryption;
EOF
psql -c "SELECT rolname, left(rolpassword, 14) AS hash FROM pg_authid
  WHERE rolname LIKE 'tag03%';"
echo 'host  notes  tag03_alt  172.16.0.0/24  scram-sha-256' | sudo tee -a $H
psql -Atc 'SELECT pg_reload_conf()'

Expected (abridged):

scram-sha-256
SET
WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release
of PostgreSQL.
CREATE ROLE
RESET
   rolname    |      hash
--------------+----------------
 tag03_report | SCRAM-SHA-256$
 tag03_alt    | md598d97dd2d87
(2 rows)

On pg2:

PGPASSWORD=lab-tag03 psql -h 172.16.0.182 -U tag03_alt -d notes -Atc 'SELECT 1'

Expected (abridged):

psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:
password authentication failed for user "tag03_alt"

The server log on pg1 names the real reason, which the client isn’t told for security reasons:

sudo tail -n 3 /var/log/postgresql/postgresql-18-main.log

Expected (abridged):

... tag03_alt@notes DETAIL:  User "tag03_alt" does not have a valid SCRAM secret.
    Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 135: "host
notes  tag03_alt  172.16.0.0/24  scram-sha-256"

The fix: find old hashes and set the password again – with password_encryption = scram-sha-256 a SCRAM hash is created:

psql -Atc "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'"
psql -c "ALTER ROLE tag03_alt PASSWORD 'lab-tag03'"
psql -Atc "SELECT left(rolpassword, 14) FROM pg_authid WHERE rolname = 'tag03_alt'"

Expected:

tag03_alt
ALTER ROLE
SCRAM-SHA-256$

After that, the login from pg2 works. The method md5 in pg_hba.conf also accepts SCRAM hashes (it then negotiates SCRAM) – scram-sha-256 there, on the other hand, allows only SCRAM. Goal: scram-sha-256 everywhere, no md5 hashes left.

Save your progress? Sign up for free

Drill 6: TLS with your own CA

First on pg2: the client should verify the certificate.

C="host=172.16.0.182 dbname=notes user=tag03_report"
PGPASSWORD=lab-tag03 psql "$C sslmode=verify-full" -Atc 'SELECT 1'

Expected (abridged):

psql: error: connection to server at "172.16.0.182", port 5432 failed: root
certificate file "/home/ubuntu/.postgresql/root.crt" does not exist

verify-full needs a CA it trusts. On pg1 we build a lab CA and a server certificate. The name the client dials must be in the subjectAltName – here the IP:

mkdir -p ~/pg-ca && cd ~/pg-ca
openssl req -x509 -newkey rsa:4096 -nodes -days 3650 -subj "/CN=Lab PostgreSQL CA" \
  -keyout ca.key -out ca.crt
openssl req -newkey rsa:2048 -nodes -subj "/CN=172.16.0.182" \
  -keyout server.key -out server.csr
printf 'subjectAltName = IP:172.16.0.182\nextendedKeyUsage = serverAuth\n' \
  > server.ext
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
  -days 825 -extfile server.ext -out server.crt
openssl x509 -in server.crt -noout -subject -issuer -ext subjectAltName

Expected (abridged):

Certificate request self-signature ok
subject=CN = 172.16.0.182
subject=CN = 172.16.0.182
issuer=CN = Lab PostgreSQL CA
X509v3 Subject Alternative Name:
    IP Address:172.16.0.182

The CA (only ca.crt, never ca.key) goes onto the client. From your own machine:

ssh [email protected] cat pg-ca/ca.crt | ssh [email protected] \
  'mkdir -p ~/.postgresql && cat > ~/.postgresql/root.crt'

On pg2 – the server still delivers the snakeoil certificate:

PGPASSWORD=lab-tag03 psql "$C sslmode=verify-full" -Atc 'SELECT 1'
openssl s_client -starttls postgres -connect 172.16.0.182:5432 </dev/null 2>/dev/null \
  | grep -E 'subject=|issuer=|Verify return'

Expected:

psql: error: connection to server at "172.16.0.182", port 5432 failed: SSL error:
certificate verify failed
subject=CN = argo
issuer=CN = argo
Verify return code: 18 (self-signed certificate)

Install the new pair on pg1. The key must belong to postgres and be readable only by it. The ssl_* parameters have the context sighup:

D=/etc/postgresql/18/main
sudo install -o postgres -g postgres -m 644 ~/pg-ca/server.crt $D/server.crt
sudo install -o postgres -g postgres -m 600 ~/pg-ca/server.key $D/server.key
psql -c "ALTER SYSTEM SET ssl_cert_file = '$D/server.crt';" \
  -c "ALTER SYSTEM SET ssl_key_file = '$D/server.key';"
psql -Atc 'SELECT pg_reload_conf()'
sudo tail -n 2 /var/log/postgresql/postgresql-18-main.log

Expected (abridged):

ALTER SYSTEM
ALTER SYSTEM
t
... LOG:  parameter "ssl_cert_file" changed to "/etc/postgresql/18/main/server.crt"
... LOG:  parameter "ssl_key_file" changed to "/etc/postgresql/18/main/server.key"

On pg2:

PGPASSWORD=lab-tag03 psql "$C sslmode=verify-full" -c '\conninfo' \
  | grep -E 'Host|SSL (Conn|Proto)'
openssl s_client -starttls postgres -connect 172.16.0.182:5432 \
  -CAfile ~/.postgresql/root.crt </dev/null 2>/dev/null \
  | grep -E 'subject=|issuer=|Verify return'

Expected:

 Host                 | 172.16.0.182
 SSL Connection       | true
 SSL Protocol         | TLSv1.3
subject=CN = 172.16.0.182
issuer=CN = Lab PostgreSQL CA
Verify return code: 0 (ok)

So far the client decides whether it wants TLS. hostssl instead of host enforces it on the server side. On pg1:

sudo sed -i 's/^host  notes  tag03_report /hostssl  notes  tag03_report /' $H
psql -Atc 'SELECT pg_reload_conf()'

On pg2:

export PGPASSWORD=lab-tag03
psql "$C sslmode=disable" -Atc 'SELECT 1'
psql "$C sslmode=verify-full" \
  -Atc 'SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid()'
unset PGPASSWORD

Expected (abridged):

psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:  no
pg_hba.conf entry for host "172.16.0.123", user "tag03_report", database "notes",
no encryption
t|TLSv1.3

sslmode levels: prefer (default) uses TLS if possible but verifies nothing; require enforces TLS without verification; verify-ca checks the chain; verify-full also checks the name. Only verify-full protects against a server that inserts itself in between.

Warning: From today on, the certificate stays on pg1 (ssl_cert_file and ssl_key_file in postgresql.auto.conf), and ~/.postgresql/root.crt stays on pg2. The CA with its key lives in ~/pg-ca on pg1 – fine in the lab, but in production the CA key doesn’t belong on the database server.

Warning: If ~/.postgresql/root.crt exists, libpq checks the chain even with sslmode=require (like verify-ca), and prefer silently falls back to plain text with a server that uses a different CA. On pg2, psql "host=127.0.0.1 sslmode=require" therefore shows SSL error: certificate verify failed (snakeoil is still running there). For other servers, specify sslrootcert=<file> or sslrootcert=system explicitly.

Save your progress? Sign up for free

Drill 7: Cancel and terminate sessions

On pg2, start a hanging query (in a second terminal or in the background):

PGPASSWORD=lab-tag03 psql "$C application_name=report" -c 'SELECT pg_sleep(600)'

On pg1, find it and first cancel it politely, then terminate it hard:

psql -c "SELECT pid, usename, application_name AS app, client_addr, state,
  left(query, 24) AS query FROM pg_stat_activity WHERE usename = 'tag03_report';"
psql -Atc "SELECT pg_cancel_backend(pid) FROM pg_stat_activity
  WHERE usename = 'tag03_report'"

Expected (abridged):

 pid  |   usename    |  app   | client_addr  | state  |        query
------+--------------+--------+--------------+--------+----------------------
 3699 | tag03_report | report | 172.16.0.123 | active | SELECT pg_sleep(600)
(1 row)

t

On pg2 you see:

ERROR:  canceling statement due to user request

pg_cancel_backend only cancels the running query; the session lives on. pg_terminate_backend ends the whole process – including open transactions that hold locks. Start the query on pg2 once more, then on pg1:

psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE usename = 'tag03_report';"
psql -Atc "SELECT count(*) FROM pg_stat_activity WHERE usename = 'tag03_report'"

Expected:

 pg_terminate_backend
----------------------
 t
(1 row)

0

On pg2:

FATAL:  terminating connection due to administrator command
SSL connection has been closed unexpectedly
connection to server was lost

Save your progress? Sign up for free

Exercise: Speed Drills

  1. Show all parameters waiting for a restart. Target time: 20 s
  2. Show the file and line that max_connections comes from. Target time: 30 s
  3. Check pg_hba.conf for errors without reloading. Target time: 20 s
  4. Find all roles that still have an MD5 hash. Target time: 30 s
  5. Terminate all sessions of the role tag03_report. Target time: 30 s

Solution

psql -Atc 'SELECT name FROM pg_settings WHERE pending_restart'
psql -Atc "SELECT sourcefile || ':' || sourceline FROM pg_settings
  WHERE name = 'max_connections'"
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL'
psql -Atc "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'"
psql -Atc "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE usename = 'tag03_report'"

Expected (abridged):

/etc/postgresql/18/main/postgresql.conf:65
0

After the drills, the first, fourth and fifth queries return no rows.

Save your progress? Sign up for free

Checkpoint

psql -Atc 'SHOW ssl_cert_file'

Expected: /etc/postgresql/18/main/server.crt

psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL'

Expected: 0

psql -Atc 'SHOW password_encryption'

Expected: scram-sha-256

On pg2:

psql "host=172.16.0.182 dbname=notes user=notes_app sslmode=verify-full" \
  -Atc 'SELECT version FROM pg_stat_ssl WHERE pid = pg_backend_pid()'

Expected: TLSv1.3 without a password prompt (from .pgpass, Day 1).

openssl x509 -in ~/.postgresql/root.crt -noout -subject

Expected: subject=CN = Lab PostgreSQL CA

Challenge

Make sure that sessions of tag03_report that sit idle in an open transaction are terminated automatically after 5 seconds – without a restart, without a reload and only for this role. Prove it from pg2.

Hint: The parameter is called idle_in_transaction_session_timeout.

Solution

psql -c "ALTER ROLE tag03_report SET idle_in_transaction_session_timeout = '5s'"

On pg2:

PGPASSWORD=lab-tag03 psql "$C" <<'EOF'
BEGIN;
SELECT 1 AS eins;
\! sleep 7
SELECT 2 AS zwei;
EOF

Expected:

BEGIN
 eins
------
    1
(1 row)

FATAL:  terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly
connection to server was lost

Role settings apply from the next login. Open, idle transactions hold locks and slow down VACUUM (Day 6) – a timeout is cheap insurance.

Save your progress? Sign up for free

Cleanup

shared_buffers and work_mem back to their defaults, test rules and roles gone. Your own certificate stays:

psql -c 'ALTER SYSTEM RESET shared_buffers' -c 'ALTER SYSTEM RESET work_mem'
sudo systemctl restart postgresql@18-main
sudo sed -i '/ tag03_/d' /etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -c 'DROP ROLE tag03_report' -c 'DROP ROLE tag03_alt'
sudo cat /var/lib/postgresql/18/main/postgresql.auto.conf

Expected:

ALTER SYSTEM
ALTER SYSTEM
t
DROP ROLE
DROP ROLE
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
ssl_cert_file = '/etc/postgresql/18/main/server.crt'
ssl_key_file = '/etc/postgresql/18/main/server.key'

Interview Note

  • “Reload or restart?” – pg_settings.context tells you: user/superuser/sighup via reload, postmaster only via restart; pending_restart shows open cases.
  • “Why can’t a client get in even though the password is correct?” – The first matching line in pg_hba.conf (often a reject or a wrong address), the method doesn’t match the hash (MD5 vs. SCRAM), or a forgotten reload. The real reason is in the server log.
  • “What does sslmode=verify-full give you?” – It checks the chain and the host name; without it, TLS protects against eavesdropping but not against a fake server.

Enterprise Note

  • Manage configuration via Ansible/Salt in conf.d/*.conf or postgresql.conf; lock ALTER SYSTEM with allow_alter_system = off (from PG 17) so nothing gets changed behind the repository’s back.
  • Certificates from the internal PKI (Vault, cert-manager, ACME) with expiry monitoring and automatic reload; clients with sslmode=verify-full, client certificates if needed (hostssl … cert).
  • pg_hba.conf as tight as possible: a separate role and database per application, only its network, no all all 0.0.0.0/0; changes only after a pg_hba_file_rules check.
  • Derive memory parameters from load tests, not from tables; tools like PGTune only provide the starting point.
  • For report and batch roles, separate work_mem, statement_timeout and idle_in_transaction_session_timeout values via ALTER ROLE.