AWS EC2 — Allowing Remote Login to the PostgreSQL Database

Once the PostgreSQL database is installed in the server, by default the listen address is set to the localhost for security reasons. But sometimes we need to access it from the remote. The steps involved in the process of allowing remote access to the database is as follows

Changing the Postgres configuration file in the remote server

First, we have to connect to the remote server via ssh login as root. Once we are connected to the server, we have to open the configuration file, which is usually in the path /etc/postgresql/9.5/main/postgresql.conf, the version of 9.5 may vary.

Once we open the file, the listen address in the file will be localhost by default. We should change it to any ip.address to accept connection from that machine or set to ‘*’ so that it allows access to every machine.

Then we have to allow remote connections to reach your server. For that, we have to add the following line at the end of another file pg_hba.conf which is in the same path /etc/postgresql/9.5/main/pg_hba.conf.

host all all 0.0.0.0/0 md5

After finishing the above task, we have to restart the PostgreSQL services before leaving the ssh session by the following command /etc/init.d/postgresql restart.

Opening the port in the AWS EC2

We can able to connect to the Postgres database in the remote only if the Postgres server port is open. Let’s see how we can do it through AWS EC2 services.

• Log in to the AWS account and navigate to the EC2 management console.
• Move to Security groups under Network and Security menu

img-src: https://bsscommerce.com/confluence/setup-ftp-for-amazon-cloud-server/

• Select your EC2 server in the Security Groups screen and then under Actions menu select edit inbound rules.

• Now the inbound rules are displayed on the screen where you can add/edit/delete your inbound rules.
• If you want to enable port 5432(for PostgreSQL server), choose Type as Postgresql, Protocol as TCP, port range as 5432. and source as 0.0.0.0/0 to accept all the incoming requests or you can provide a particular IP address to give access.
• Then you have to save these settings. Once you save, the port 5432 will be opened.

References

https://bosnadev.com/2015/12/15/allow-remote-connections-postgresql-database-server/