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

PostgreSQL — access, pg_hba.conf, dump and restore

14 min Ubuntu 24.04 · PostgreSQL 16

PostgreSQL is a relational DBMS: roles and databases, pg_hba.conf access rules, pg_dump dumps and pg_restore restores.

Cheat sheet

8 commands Printable version
  • 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

Overview

PostgreSQL is a relational DBMS. The postgresql service runs under systemd. Ubuntu calls a server a cluster: 16/main is the version and the name. Data lives in /var/lib/postgresql/16/main, configs in /etc/postgresql/16/main/, the log in /var/log/postgresql/postgresql-16-main.log.

The server has its own accounts — roles. The postgres role is the superuser. A role and a Linux user are different things: psql -U appuser logs in as the appuser role whoever you are on the system.

How a client gets into a database

The server checks a connection step by step, and each step has its own refusal:

Check What it looks at Refusal
network does the server listen on the address and port or socket Connection refused
rule is there a line in pg_hba.conf no pg_hba.conf entry
login the password or peer from that line authentication failed

psql without -h connects through the socket /var/run/postgresql/.s.PGSQL.5432 — local lines apply, with peer by default. With -h 127.0.0.1 it uses TCP, host lines and a password.

Configuration

7

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).

Directive What it does Example
listen_addresses postgresql.conf Which addresses accept TCP connections; '*' means all. Changes need a restart. listen_addresses = '*'
port postgresql.conf The TCP port and the number in the socket name .s.PGSQL.5432. Changes need a restart. port = 5432
password_encryption postgresql.conf How new role passwords are stored; pairs with the scram-sha-256 method in pg_hba.conf. password_encryption = scram-sha-256
max_connections postgresql.conf How many connections at once; extra ones get too many clients. Changes need a restart. max_connections = 100
log_connections postgresql.conf Log every connection — shows from which address and as which role clients logged in. log_connections = on
local / host pg_hba.conf The line type — a socket connection or TCP. host appdb appuser 10.0.0.5/32 scram-sha-256
peer / scram-sha-256 / reject pg_hba.conf The check method: the Linux user name, a password, or a refusal without checks. local all postgres peer

Nothing found. Try a shorter query.

Recipes

5

A role and a database for an application

bash 
sudo -u postgres psql -c "CREATE ROLE appuser LOGIN PASSWORD 'apppass'"
sudo -u postgres createdb -O appuser appdb
psql -h 127.0.0.1 -U appuser -d appdb -c 'SELECT current_user'

A new password — ALTER ROLE appuser PASSWORD '…';; list roles with \du in psql, databases with \l.

A password without typing: ~/.pgpass

bash 
echo '127.0.0.1:5432:appdb:appuser:apppass' > ~/.pgpass
chmod 600 ~/.pgpass
psql -h 127.0.0.1 -U appuser -d appdb

A line is host:port:database:role:password; * matches any value. The mode must be 600: otherwise psql says password file … has group or world access and skips the file. The PGPASSWORD variable takes precedence over the file.

Dump formats

pg_dump takes a logical dump — the commands and data the database is rebuilt from. Roles are not included: pg_dumpall --globals-only saves them.

Format Command Restored with
plain SQL pg_dump -f appdb.sql appdb psql -f
custom pg_dump -Fc -f appdb.dump appdb pg_restore
directory pg_dump -Fd -j 4 -f appdb.dir appdb pg_restore -j 4

Plain SQL is human-readable: CREATE TABLE, COPY with the data, setval for counters. At the start and end there are \restrict and \unrestrict lines with a random key: they protect psql from foreign commands in the data, this is expected. The custom format is compressed, starts with the bytes PGDMP and lets you list its contents and restore part of it:

bash 
sudo -u postgres pg_restore -l /tmp/appdb.dump
text 
216; 1259 16387 TABLE public orders appuser
215; 1259 16386 SEQUENCE public orders_id_seq appuser
3396; 0 16387 TABLE DATA public orders appuser
3404; 0 0 SEQUENCE SET public orders_id_seq appuser

Above the contents is the archive header: Archive created at 2026-10-20 10:05:53 UTC, dbname: appdb and the server versions. It tells which copy this is and where it came from. pg_restore -f - -t orders /tmp/appdb.dump prints one table's data without restoring — the table definition and the COPY rows.

Restore into a new database

bash 
sudo -u postgres createdb -O appuser appdb_copy
sudo -u postgres pg_restore -d appdb_copy /tmp/appdb.dump
sudo -u postgres psql -d appdb_copy -c 'SELECT count(*) FROM orders'

A new empty database has no conflicts: exit code 0. The dump restores the id sequence with setval('public.orders_id_seq', 3, true) — the next insert gets id 4.

Owners. createdb -O appuser sets the owner of the database itself. Run as postgres, pg_restore takes table owners from the archive — with ALTER TABLE … OWNER TO appuser lines, so that role must exist. With --no-owner the restoring role becomes the owner, and with --no-owner --role=r — role r.

pg_restore option What it does
-l only the archive's table of contents
-L list restore the TOC lines listed in a file
-f - print SQL instead of writing to a database
--exit-on-error stop at the first error
--single-transaction all or nothing: roll back on error
--clean --if-exists drop objects before creating them
--create -d postgres create the database named in the dump
--no-owner --role=r make role r the owner of objects

Compare a copy with the source

sql 
SELECT count(*) FROM orders;
SELECT md5(string_agg(t::text, ',' ORDER BY id)) FROM orders t;
SELECT last_value FROM orders_id_seq;

The same row count, selection checksum and counter value in the source and the copy mean the data matches. \dt shows the table owners.

Troubleshooting

7

Connection refused

connection to server at "127.0.0.1", port 5432 failed: Connection refused and Is the server running on that host and accepting TCP/IP connections?. The service is not running (pg_lsclusters, systemctl status postgresql) or does not listen on that address: sudo ss -tlnp | grep 5432, SHOW listen_addresses;. Over the socket the same looks like No such file or directory and Is the server running locally….

no pg_hba.conf entry

FATAL: no pg_hba.conf entry for host "127.0.0.1", user "appuser", database "appdb", no encryption — no line matched this address, database and role. The message comes twice: psql tries with SSL first, then without. Add a line host appdb appuser <address>/32 scram-sha-256, reload and check pg_hba_file_rules.

pg_hba.conf rejects connection for host … — a reject line matched; it sits above the allowing one.

password authentication failed

A rule matched, the password did not. For a role that does not exist the server says the same — check \du. ALTER ROLE … PASSWORD sets the password; remember that PGPASSWORD overrides ~/.pgpass. fe_sendauth: no password supplied — a password was asked for and there was nowhere to take it from.

Peer authentication failed

psql -U appuser without -h goes through the socket, where the rule is peer: the Linux user must have the role's name. Connect with -h 127.0.0.1 and a password, or use sudo -u postgres psql. role "student" does not exist — psql without -U takes the role from the Linux user name.

A pg_hba.conf change has no effect

The file was not re-read, or was re-read with an error — then the old rules apply, and pg_reload_conf() still returns t. The error shows in SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;, and the log says pg_hba.conf was not reloaded.

pg_restore: relation "…" already exists

A restore into a database that already has the objects: an error for each, then pg_restore: warning: errors ignored on restore: 4, exit code 1, and part of the data may have landed on top. Restore into a new empty database. --single-transaction rolls everything back at the first error. --create -d postgres while a database of that name exists gives database "appdb" already exists.

input file appears to be a text format dump

pg_restore got plain SQL — restore it with psql -f. The reverse error: psql -f on a custom dump says The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client….

Danger zone

2

trust in pg_hba.conf

trust admits anyone matching the line without a password. host all all 0.0.0.0/0 trust opens every database to anyone who can reach the port.

pg_restore --clean into a live database

--clean drops objects before creating them. In a live database that replaces current data with old data from the dump. Restore into a separate database and compare first.

Terms

7
pg_hba.conf
PostgreSQL's access rule file: connection type, database, role, address and authentication method. The first matching line wins; no match — «no pg_hba.conf entry». Also appears in: Unix sockets
PostgreSQL role
An account inside PostgreSQL; a role with LOGIN is a database user. It is separate from Linux users except for peer authentication.
peer authentication
Login to PostgreSQL over the local socket: the server asks the kernel which Linux user connected and admits them only to the role with the same name. Hence sudo -u postgres psql.
logical dump
A copy of a database as commands and data (CREATE, COPY), taken by pg_dump; it restores into a database of the same or a newer version. Roles are not included.
custom format (-Fc)
A compressed pg_dump archive that only pg_restore reads: you can list its contents (-l) and restore selectively. The file starts with the bytes PGDMP.
sequence
A PostgreSQL counter that new ids of a serial column come from; a dump stores its value via setval. A counter left behind after a restore gives a duplicate key error on the next insert.
~/.pgpass file
PostgreSQL passwords for the client: lines host:port:database:role:password, mode 0600 — otherwise the file is skipped with a warning. The PGPASSWORD variable takes precedence.

See also

Found an inaccuracy? Report an error in the article

Practice on this topic

Lessons on this topic

Topics
Loading…