Connect pgAdmin4 from your local machine to OCI PostgreSQL Service

The new OCI PostgreSQL service does not allow provisioning of public endpoints, and only allows databases with private endpoint inside your VCN.

So how do you connect from a local pgAdmin4 client installed on your laptop or workstation in your home network to a private OCI PostgreSQL instance? The answer is; using the OCI Bastion service and doing a port forwarding on port 5432

Step 1. Create a Port forwarding session on the OCI Bastion service on port tcp/5432

Let us first create a OCI Bastion on Demo-VCN and Private Subnet-Demo-VCN which is the same vcn/subnet in which the OCI Postgres instance is provisioned.

*Important : Make sure you add port tcp/5432 into the security list for the private subnet as ingress rule

Create Port Forwarding Session on Private IP of Postgres Instance and Port 5432

10.20.4.220 is IP of the OCI PostgreSQL instance in my case.

If you don’t know your IP, Go to OCI Console > Databases > PostgreSQL Databases > DBsystems > Click on your Instance > Connection details > Endpoint

Step 2. Open Terminal and Paste SSH command

Change the private key file to the priv key file downloaded earlier from Step 1, and give a local port.

This will create a port forwarding session on local port 5432 to remote port 5432

ssh -i <privateKey> \
 -N -L <localPort>:10.200.4.220:5432 \
 -p 22 ocid1.bastionsession.oc1.phx.amaaaaaalnulpsaamkm3gznnbria7bfvlagwlpeoapmzqw3bfwsuba7imzoq@host.bastion.us-phoenix-1.oci.oraclecloud.com

Example:

ssh -i 'key' \
-N -L 5432:10.200.4.220:5432 \
-p 22 ocid1.bastionsession.oc1.phx.amaaaaaalnulpsaamkm3gznnbria7bfvlagwlpeoapmzqw3bfwsuba7imzoq@host.bastion.us-phoenix-1.oci.oraclecloud.com

This creates a SSH tunnel with a maximum validity of 180mins.

_________________________________________________________________

If you get error -> Permissions 0444 for ‘key’ are too open.It is required that your private key files are NOT accessible by others.

Then run:

$ chmod 400 key

_________________________________________________________________

Step 3. Connect with pgAdmin4 installed on your local machine via localhost

We have now successfully established a connection from your local work/home network to OCI PostgreSQL instance in a private subnet.

You can also use any other Postgres client like psql or DBeaver to connect to OCI PostgreSQL instance from your machine with localhost and port 5432 while the SSH tunnel is up

psql -h localhost -p 5432 -U postgres -d postgres