Nyssa and I have been busy deploying Notifier For Reddit and one thing I wanted to do was securely expose its PostgreSQL server to remote connections so I could use the fantastic and free DBeaver Universal Database Manager GUI with it. Notifier for Reddit (NFR) runs in AWS on an EC2 instance.

In an ideal world you wouldn't need to connect a Database GUI Tool to a production web application. However, this is real life and for the first few months of running the app live I wanted to be able to easily monitor what is going on in the database!

The desired functionality

I want to be able to remotely connect to PostgreSQL on my AWS EC2 Instance BUT only from my home IP address.

Note that this still allows me to connect when away from home as I have a VPN server on my home network and can securely connect to it using my iPhone, Mac, etc.

Edit postgresql.conf

First find your postgresql.conf file:

sudo find / -iname "postgresql.conf"

Mine was in: /etc/postgresql/9.5/main/postgresql.conf

Switch to the postgreSQL User:

sudo su postgres

Edit the file to allow remote connections:

...

listen_addresses = '*'           # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = XXXX                             # (change requires restart)
...

This defines what ip address the PostgreSQL Server listens on. * means that it will listen on all available interfaces.

Be sure to also change the default port address if you want.

Edit pg_hba.conf

vim /etc/postgresql/9.5/main/pg_hba.conf

Add the following line to the bottom of that file, replacing db_name and db_user with the appropriate values. Also change X.X.X.X/32 to be the specific ip you want to restrict connections to.

host    {db_name}         {db_user}          X.X.X.X/32       md5

Restart PostgreSQL

sudo systemctl restart postgresql.service

AWS Security Groups

Last but not least make sure you setup your security group in AWS to only allow incoming connections for whatever port you chose to ONLY come from your specific home ip address.