1. PostgreSQL Installation (Ubuntu)

First, install the latest stable version of PostgreSQL on your Ubuntu server:

sudo apt install postgresql

2. Configure Network Listening Address

The core configuration file, postgresql.conf, controls which IP addresses the server listens on. Navigate to the appropriate configuration directory (the version number, such as 16, may change in the future):

cd /etc/postgresql/16/main

Now, open the configuration file for editing:

sudo nano postgresql.conf

Find the line for listen_addresses (usually commented out) and modify it to listen on all available network interfaces by setting the value to '*'. This is required for external access:

# Before:
#listen_addresses = 'localhost'         # what IP address(es) to listen on;

# After:
listen_addresses = '*'                 # Listen on all interfaces

3. Define Client Authentication (pg_hba.conf)

Next, we must allow connections through the pg_hba.conf file, which controls client authentication. Open the file in the same directory:

sudo nano pg_hba.conf

Add the following line to the top of the file. This rule allows all users from all networks (0.0.0.0/0) to connect using the secure scram-sha-256 method:

# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
host    all       all   0.0.0.0/0     scram-sha-256

⚠️ Security Note: This setup is deliberately broad to ensure connectivity, but it requires immediate external firewall protection!

4. Secure External Access with UFW Firewall

Since the pg_hba.conf file is currently wide-open, we use the Uncomplicated Firewall (UFW) to control access strictly by IP address. This is the most secure method for external access.

For detailed UFW setup, please refer to our guide on UFW. To allow access from a specific static IP (e.g., your separate server's IP, 8.8.8.8), run the following rule:

sudo ufw allow from 8.8.8.8 to any port 5432

Any other IPs attempting to connect to PostgreSQL will be blocked by the firewall.

5. Create Database and User

Finally, let's create the database and a dedicated user. Access the PostgreSQL prompt as the system's postgres user:

sudo -u postgres psql

Once the prompt shows postgres=#, run the following commands in order. Remember to use a strong password and replace the placeholder names! A semicolon (;) is required at the end of each command.

CREATE DATABASE dbname;
CREATE USER dbuser WITH PASSWORD 'strong_password';
ALTER ROLE dbuser SET client_encoding TO 'utf8';
ALTER ROLE dbuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE dbuser SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;
GRANT ALL ON SCHEMA public TO dbuser;
ALTER DATABASE dbname OWNER TO dbuser;
\q

Pro Tip: Stick to lowercase for database and user names to avoid potential case-sensitivity issues later on.

6. Restart PostgreSQL

The configuration changes will not take effect until the service is restarted:

sudo systemctl restart postgresql

Your PostgreSQL server is now installed, configured for external connections, and secured by your UFW firewall rules!