Enable Remote access to Postgres Database
In this article we will learn how to enable remote access for Postgres Database
Prerequisite Knowledge
To understand this guide you dont need to be an expert but having some basic knowledge of linux terminal commands and postgres Database will help you.
Steps to Enable Remote access
Step 1 : Modify postgresql.conf Step 2 : Modify pg_hba.conf Step 3 : Restart your PostgreSQL instance.
Caution for Developers
Enabling Remote access to your Postgres Database makes your database venerable to malicious attacks by hackers. Remote access should be enabled only for temporary period when its required and you should disable it as soon as your needs are solved.
Let us begin the step by step Guide to enable remote access to Postgres Database
Step 1 : Modify postgresql.conf
Change the address PostgreSQL listens to Using Nano (or vim/emacs/whatever), edit your postgresql.conf file. We’re specifically looking for a line that says listen_addresses. We’re going to modify where PostgreSQL is listening on. Uncomment the following Line in the file postgresql.conf located at the /etc/postgres/9.5/main
Please replace 12 with the version number of your Postgres Installation
Code
cd /etc/postgresql/12/main/postgresql.conf
Open postgresql.conf file in a text editor and uncomment the line "listen_address='*'"
Code
sudo nano postgresql.conf
listen_addresses='*'
Step 2 : Modify pg_hba.conf
Allow remote connections to actually reach your PostgreSQL instance Next we’re going to edit our pg_hba.conf file and configure it to allow remote connections. At the very end of the file, add the following line:
Please replace 12 with the version number of your Postgres Installation
Code
cd /etc/postgresql/12/main/pg_hba.conf
sudo nano pg_hba.conf
hostssl all all 157.48.77.115/32 md5
This is a rule that basically tells PostgreSQL to allow only the host with given IP to access the instance as long as they have proper credentials. This is recommended for security reasons.
Code
cd /etc/postgresql/12/main/pg_hba.conf
sudo nano pg_hba.conf
host all all 0.0.0.0/0 md5
This is a rule that basically tells PostgreSQL to allow anyone to access the instance as long as they have proper credentials. This isn't recommended because it makes your installation very insecure. So please white list only one IP address. If you want to whitelist your IP only you can use your network/mask instead of 0.0.0.0/0. (Look example above)
Step 3 : Restart your PostgreSQL instance.
That’s it, you’re done. Restart it.
Code
sudo service postgresql restart
Now you can use a tool like PgAdmin or RubyMine to access your remote database.