PostgreSQL 16: Install, Users and Remote Access

sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt jammy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update && sudo apt install -y postgresql-16

Create an application user and database:

sudo -u postgres psql -c "CREATE ROLE shop LOGIN PASSWORD 'change-me';"
sudo -u postgres psql -c "CREATE DATABASE shop OWNER shop;"

Allow a private network in two files under /etc/postgresql/16/main/:

# postgresql.conf
listen_addresses = 'localhost,10.0.1.30'
# pg_hba.conf
host  shop  shop  10.0.1.0/24  scram-sha-256

sudo systemctl restart postgresql
psql "host=10.0.1.30 dbname=shop user=shop"

Use scram-sha-256, never trust, for network connections. Tune shared_buffers (about 25 percent of RAM) and work_mem carefully, and schedule pg_dump plus WAL archiving for backups.