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

File Location : /etc/postgresql/9.5/main/postgresql.conf

Please replace 9.5 with the version number of the Postgres you are using

# /etc/postgresql/9.5/main/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:

# /etc/postgresql/9.5/main/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.

If you want to whitelist your IP only you can use your network/mask instead of 0.0.0.0/0.

 

Step 3 : Restart your PostgreSQL instance.

That’s it, you’re done. Restart it.

sudo service postgresql restart

Now you can use a tool like PgAdmin or RubyMine to access your remote database.

 

Links and References

Link : https://www.digitalocean.com/community/questions/remote-connect-to-postgresql-with-pgadmin

Link : https://coderwall.com/p/cr2a1a/allowing-remote-connections-to-your-postgresql-vps-installation

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

%d bloggers like this: