Self-Hosting PostgreSQL on Oracle Cloud – Remote Access Setup Guide

Self-Hosting PostgreSQL on Oracle Cloud – Remote Access Setup Guide

on this page

If you're deploying PostgreSQL on your own Oracle Cloud VM and want to access it remotely (e.g., from your local machine), you’ll need to pass through three separate firewalls:

  1. Oracle Cloud firewall
  2. OS-level firewall (iptables)
  3. PostgreSQL's internal configuration

Here’s how to get everything working.

1. Oracle Cloud Firewall (a.k.a. Security List Rules)

By default, Oracle Cloud blocks all incoming traffic. You need to allow TCP traffic on PostgreSQL’s default port (5432):

Steps:

  1. Go to the Oracle Cloud Console.

  2. Navigate to Virtual Cloud Network (VCN) → your VCN → Security Lists.

  3. Select the security list associated with your VM’s subnet.

  4. Under Ingress Rules, click Add Ingress Rules.

  5. Configure as follows:

    • Source CIDR: 0.0.0.0/0 (allows access from anywhere — use with caution in production!)
    • IP Protocol: TCP
    • Destination Port Range: 5432

Tip: For better security, replace 0.0.0.0/0 with your own IP or IP range if you know it.

2. OS-Level Firewall (Ubuntu Example with iptables)

Your VM likely has its own firewall. You’ll need to allow inbound traffic on port 5432 here too.

Run these commands:

sudo apt update
sudo apt install -y iptables-persistent
 
# Allow PostgreSQL port
sudo iptables -I INPUT 1 -p tcp --dport 5432 -j ACCEPT
 
# Save rules
sudo netfilter-persistent save

3. PostgreSQL Configuration

PostgreSQL won’t allow remote connections by default. You need to edit two files:

1. postgresql.conf

This file controls PostgreSQL’s runtime config.

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

(Replace 15 with your actual PostgreSQL version.)

Find and update:

# Listen on all IPs
listen_addresses = '*'

2. pg_hba.conf

This file controls client authentication.

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

Add the following line at the end:

host    all             all             0.0.0.0/0               md5

Again, you can replace 0.0.0.0/0 with a specific IP range for tighter security.

Restart PostgreSQL

After making changes, restart the service:

sudo systemctl restart postgresql

✅ Done!

You should now be able to connect to your Postgres server remotely using any client (e.g., psql, DBeaver, or a Python script), like:

psql -h your_vm_ip -U your_user -d your_db