PostgreSQL 17 Installation & Remote Access Configuration (Ubuntu 24.04)

This guide details the steps to install PostgreSQL 17 on Ubuntu 24.04 and configure it for remote access using password authentication (scram-sha-256).

Step 1: Install Common PostgreSQL Dependencies

sudo apt update
sudo apt install -y postgresql-common

Step 2: Enable the PostgreSQL APT Repository

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Note: Press Enter when prompted to proceed.

Step 3: Install PostgreSQL Database Server

sudo apt update
sudo apt install -y postgresql

Step 4: Verify PostgreSQL Service Status

sudo systemctl status postgresql

Step 5: Secure the Default postgres User

sudo -u postgres psql
ALTER USER postgres WITH ENCRYPTED PASSWORD 'your_strong_password';
\q

Step 6: Configure PostgreSQL for Remote Password Authentication

6.1 Edit pg_hba.conf:

sudo sed -i '/^local\s\+all\s\+postgres\s\+/s/peer/scram-sha-256/' /etc/postgresql/17/main/pg_hba.conf
echo "host    all    all    0.0.0.0/0    scram-sha-256" | sudo tee -a /etc/postgresql/17/main/pg_hba.conf

6.2 Edit postgresql.conf to Allow Remote Connections:

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

Change the following line:

listen_addresses = '*'

Step 7: Restart PostgreSQL Service

sudo systemctl restart postgresql

Step 8: Confirm Remote Access (Optional)

Ensure port 5432 is open and test with:

psql -h <your_server_ip> -U postgres -d postgres -W

Notes