Install PostgreSQL

This guide installs PostgreSQL from official Debian/Ubuntu repositories on a HolyCloud Linux VPS, creates an application user and database, then restricts connections via pg_hba.conf.

Prerequisites

• HolyCloud VPS Ubuntu 22.04/24.04 or Debian 12
• At least 1 GB RAM recommended for PostgreSQL alone
• sudo access
• Snapshot or backup before network exposure

Tip: in production, do not expose PostgreSQL on the Internet except via VPN or IPs allowed in pg_hba.conf.

Step 1: installation

sudo apt update
sudo apt install -y postgresql postgresql-contrib

The installed version depends on the distribution (postgresql --version). Enable and start the service:

sudo systemctl enable --now postgresql
sudo systemctl status postgresql

Step 2: first access (postgres user)

Debian/Ubuntu creates the system superuser postgres. Switch to it:

sudo -i -u postgres
psql -c "SELECT version();"

Exit with exit.

Step 3: create a role and database

Replace monapp and passwords with your values:

sudo -u postgres psql <<'EOF'
CREATE ROLE monapp_user WITH LOGIN PASSWORD 'MotDePasseFortIci!';
CREATE DATABASE monapp_db OWNER monapp_user ENCODING 'UTF8' LC_COLLATE='fr_FR.UTF-8' LC_CTYPE='fr_FR.UTF-8' TEMPLATE template0;
GRANT ALL PRIVILEGES ON DATABASE monapp_db TO monapp_user;
EOF

If locale fr_FR.UTF-8 does not exist:

sudo locale-gen fr_FR.UTF-8
sudo update-locale

Test connection as the application user:

psql -h 127.0.0.1 -U monapp_user -d monapp_db -c "\conninfo"

Step 4: configure pg_hba.conf

Find the configuration directory (PostgreSQL 16 example):

sudo -u postgres psql -t -P format=unaligned -c 'SHOW hba_file;'

Edit the file (typical path):

sudo nano /etc/postgresql/16/main/pg_hba.conf

Example for local access only (recommended default):

# TYPE  DATABASE        USER            ADDRESS         METHOD
local   all             postgres                        peer
local   monapp_db       monapp_user                     scram-sha-256
host    monapp_db       monapp_user     127.0.0.1/32    scram-sha-256
host    monapp_db       monapp_user     ::1/128         scram-sha-256

To allow another HolyCloud VPS (fixed IP):

host    monapp_db       monapp_user     203.0.113.50/32 scram-sha-256

Network listen: in postgresql.conf, set listen_addresses if needed:

sudo nano /etc/postgresql/16/main/postgresql.conf

listen_addresses = 'localhost'

To listen on a private interface: listen_addresses = '10.0.0.5'.

Reload configuration:

sudo systemctl reload postgresql

Step 5: firewall and hardening

If you use UFW and expose port 5432 (discouraged without VPN):

sudo ufw allow from 203.0.113.50 to any port 5432 proto tcp

Change the postgres password if you use it:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'AutreMotDePasseFort';"

Verification

sudo -u postgres psql -c "\du"
sudo -u postgres psql -c "\l"
ss -tlnp | grep 5432

From an authorized machine:

psql -h IP_VPS -U monapp_user -d monapp_db -c "SELECT current_database(), current_user;"

On failure, check logs: sudo journalctl -u postgresql -n 50.

Need help?

HolyCloud support can help with network connectivity; application PostgreSQL configuration remains your responsibility.