Linux Labs
Where to start First steps track · 15 stages
Linux Labs
EN
Back to the article

PostgreSQL — access, pg_hba.conf, dump and restore

cheat sheet · Ubuntu 24.04 · PostgreSQL 16 https://lab.dimsanych.ru/en/wiki/postgresql/

Cheat sheet

  • sudo systemctl start postgresql the service is off in the labs
  • pg_lsclusters version, port, status
  • sudo -u postgres psql superuser console
  • psql -h 127.0.0.1 -U appuser -d appdb TCP login with a password
  • sudo -u postgres psql -c 'TABLE pg_hba_file_rules' access rules
  • sudo ss -tlnp | grep 5432 which address it listens on
  • sudo -u postgres pg_dump -Fc -f /tmp/appdb.dump appdb
  • sudo -u postgres pg_restore -l /tmp/appdb.dump table of contents

Configuration

Two files in /etc/postgresql/16/main/: postgresql.conf holds server parameters, pg_hba.conf says who may log in and how. What the server actually read: SHOW hba_file;, SHOW listen_addresses;.

pg_hba.conf

pg_hba.conf holds access rules, one per line. Ubuntu's defaults:

text 
# TYPE  DATABASE  USER      ADDRESS        METHOD
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
Field Meaning
TYPE local — over the socket, host — over TCP
DATABASE a database name or all
USER a role or all
ADDRESS the client address with a mask; empty for local
METHOD how to check: peer, scram-sha-256, reject

peer admits a Linux user only to the role with the same name, which is why the superuser logs in as sudo -u postgres psql. scram-sha-256 is a password. reject refuses without checking.

Lines are read top to bottom and the first matching line wins; the rest are not looked at. A reject line above an allowing one blocks access even with the right password. Put a new rule for a specific database and role above the general ones.

sudo systemctl reload postgresql or SELECT pg_reload_conf(); applies the change. The pg_hba_file_rules view shows what the server read: line number, fields and an error column. If the file has an error, the server rejects it and keeps working with the old rules, and the log says pg_hba.conf was not reloaded.

Address and port

By default listen_addresses = 'localhost' — only 127.0.0.1 and ::1, port 5432. To accept connections from the network you need three things: the address in listen_addresses ('*' means all), a host line for the client address in pg_hba.conf, and the port open in the firewall. listen_addresses changes only with sudo systemctl restart postgresql: after reload the parameter is marked pending_restart and the server listens as before.

Your own setting is best kept in a separate file: postgresql.conf reads the conf.d directory next to it (include_dir = 'conf.d'), and a package upgrade leaves such a file alone:

bash 
echo "listen_addresses = '127.0.0.1'" \
  | sudo tee /etc/postgresql/16/main/conf.d/listen.conf
sudo systemctl restart postgresql
sudo ss -tlnp | grep 5432

conf.d is read after the include_dir line at the end of postgresql.conf, and the last value wins. With listen_addresses = '*' in postgresql.conf and '127.0.0.1' in conf.d/listen.conf the server listens on 127.0.0.1. pg_settings shows where the value came from and whether it waits for a restart:

text 
student@ubuntu:~$ sudo -u postgres psql -Atc "SELECT setting,
> sourcefile, sourceline, pending_restart FROM pg_settings
> WHERE name='listen_addresses'"
127.0.0.1|/etc/postgresql/16/main/conf.d/listen.conf|1|f

After editing the file and a reload, the value is unchanged, pending_restart is t, and /var/log/postgresql/postgresql-16-main.log says:

text 
LOG:  parameter "listen_addresses" cannot be changed without restarting
  the server
LOG:  configuration file "/etc/postgresql/16/main/conf.d/listen.conf"
  contains errors; unaffected changes were applied

pg_file_settings shows every line of every file and what happened to it (applied, error — for example, setting could not be applied).
Loading…