How to Install and Secure PostgreSQL

Install the server and contrib extensions, then confirm the service is running.

sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql
systemctl status postgresql --no-pager   # verify it is active (running)

Keep the server bound to the loopback interface so it is never reachable from the internet. This is the most important network control for a database.

sudo nano /etc/postgresql/*/main/postgresql.conf
# Set:
listen_addresses = 'localhost'   # accept connections only from this machine

Set the password hashing algorithm to scram-sha-256 (far stronger than md5) and require it in the client auth rules. New passwords are then stored as SCRAM hashes.

sudo nano /etc/postgresql/*/main/postgresql.conf
# Set:
password_encryption = scram-sha-256
# Then require it for local TCP connections:
sudo nano /etc/postgresql/*/main/pg_hba.conf
# Ensure the local host lines use scram-sha-256, e.g.:
host    all    all    127.0.0.1/32    scram-sha-256
host    all    all    ::1/128         scram-sha-256
sudo systemctl restart postgresql

Give each application its own role and database. The role owns its database and nothing else, following least privilege. Set the password AFTER enabling scram so it is hashed correctly.

sudo -i -u postgres psql
-- Inside psql:
CREATE ROLE myappuser WITH LOGIN PASSWORD 'CHANGE_ME_long_random_password';
CREATE DATABASE myapp OWNER myappuser;
-- Make sure no broad public access remains on the new DB
REVOKE ALL ON DATABASE myapp FROM PUBLIC;
\q

Connect as the new role over TCP to confirm scram auth works, and check that the stored secret is a SCRAM hash.

# -h 127.0.0.1 forces a TCP connection so the scram-sha-256 rule is exercised
psql -h 127.0.0.1 -U myappuser -d myapp -c '\conninfo'   # enter the password; should connect
# Confirm the password is stored as a SCRAM hash, not md5:
sudo -i -u postgres psql -c "SELECT rolname, substring(rolpassword for 14) FROM pg_authid WHERE rolname='myappuser';"
# Expected prefix: SCRAM-SHA-256

Double-check the bind so the database is not exposed beyond the host.

sudo ss -tlnp | grep 5432   # should show 127.0.0.1:5432, NOT 0.0.0.0:5432