Install pgAdmin 4.19 and setup a SSH tunnel to access to AWS EC2 PostgreSQL database.

In this post will show you how install pgAdmin 4.19 for Windows and how configure postgreSQL 12.2 to be accessed by pgAdmin ussing SSH.

If you do not have a AWS EC2 instance you can go to my post: Launch and setup a free tier AWS EC2 instance with Red Hat Enterprise Linux 8.

If you did not install PostgresSQL Server yet you can go to my post: Install PostgreSQL Server 12.2 on RHEL 8

1. Go to PostgreSQL home page and dowload the file pgadmin4-4.19-x86.exe

2. Install pgAdmin 4.19 using the installer pgadmin4-4.19-x86.exe

1. Click on Next button.

1. Select the option “I accept the agreement”
2. Click on the “Next” button.

1. Enter the location where pgAdmin will be installed.
2. Click on the “Next” button.

1. Select Start Menu folder
2. Click on the “Next” button.

3. Enable SSH in the AWS EC2 instance.

a. Verify that SSH is enabled in the EC2 instance through a Security Group.

If the EC2 instance does not have enable SSH , configure a Security group and add a new inbound rule with access to SSH.

Click on Services > EC2 > Security Groups > Create security group

1. Enter Security group name: SECURITY_TO_EC2_POSTGRESQL
2. Enter a description for the Security Group
3. Select Type: SSH
4. Select Source My IP
5. The public IP will have access to the AWS EC2 instance
6. Click on “Create security group” button.

4. Create a SSH tunnel using PuTTY , if you do not have you can download it from PuTTY Download Page

a. Create a new session in PuTTY.

1. In the field Host Name enter the public IP of the EC2 instance where was installed PostgresSQL.
2. In the field Saved Sessions enter a name for the new session.

b. Configure a SSH tunnel.

1. Click on Connection > SSH > Auth > Tunnels
2. In Source port field enter a port, in this case I chose 5433 but you can enter any available port of your client machine.
3. In the Destination field enter the value “127.0.0.1:5432” that correspond with the IP of the listener of PostgreSQL is currently listen in the port 5432.
4. Click on the “Add” button.

c. Save the PuTTY Session

1. Select Session
2. Click on the “Save” button.

4. Open the SSH tunnel

1.Click on the “Open” button.

d. Login in the server EC2

e. After the SSH tunnel is established, the client machine will be locally listen in “127.0.0.1: 5433” and will forward all requests to the remote server (Public IP 54.210.168.203) that is listening in “127.0.0.1:5432”

5. By default PostgreSQL uses IDENT-based and does not allow authentication based on user/password being necessary modify the configuration file pg_hba.conf.

a. Modify the file /var/lib/pgsql/data/pg_hba.conf to allow password authentication method replacing the parameter ‘ident’ with ‘md5’ for the IP 127.0.0.1 entry.

[dbadmin@dbserver ~]$ sudo su - postgres
Last login: Wed Apr 22 03:23:12 UTC 2020 on pts/1

[postgres@dbserver ~]$ vi /var/lib/pgsql/12/data/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5  #<=== Replace ident by MD5

6. Reboot PostgreSQL server

[postgres@dbserver bin]$ /usr/pgsql-12/bin/pg_ctl stop
waiting for server to shut down.... done
server stopped

[postgres@dbserver bin]$ /usr/pgsql-12/bin/pg_ctl start
waiting for server to start....2020-03-31 00:58:19.954 UTC [4227] LOG:  starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
2020-03-31 00:58:19.955 UTC [4227] LOG:  listening on IPv6 address "::1", port 5432
2020-03-31 00:58:19.955 UTC [4227] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-03-31 00:58:19.956 UTC [4227] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-03-31 00:58:19.959 UTC [4227] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-03-31 00:58:19.969 UTC [4227] LOG:  redirecting log output to logging collector process
2020-03-31 00:58:19.969 UTC [4227] HINT:  Future log output will appear in directory "log".
 done
server started

7. Open PgAdmin and create a server connection.

a. Create a new server

1.Right Click on Servers > Create > Server

b. Configure the new server

1. Click on the General tab.
2. Enter the name of the new server .

c. Setup the connection

1. Click on the Connection Tab.
2. In the Host name enter the IP 127.0.0.1 that is the local IP used in the SSH tunnel.
3. In the port enter the port 5433 that is source port used in the SSH tunnel.
4. In the field Maintenance database enter postgres
5. In the field Username enter the default user postgres or you can enter other user of the database
6. In the field Password enter the password of the user used in the step 5.
7. Click on Save button to save the server configuration and open a session of pgAdmin.