How to allow remote access to PostgreSQL?

In this example we will see how we can access a PostgreSQL database from a remote machine.

1. Find out your PostgreSQL port

First, find out the port where your PostgreSQL is running, by default it will be 5432. To find out the port, go to psql and enter

postgres=# \conninfo 

2. Search & Edit pg_hba.conf

Next locate the pg_hba.conf file, a simple way is to try locate pg_hba.conf and if you find a single result (mostly, in /var/lib/pgsql). Then that is the file you would be editing.

Otherwise login to psql and enter the following command

postgres=# show hba_file;
              hba_file              
------------------------------------
 /var/lib/pgsql/12/data/pg_hba.conf
(1 row)

Now, let us edit the file. The last lines of the file looks almost like…

local   replication     all                                   peer
host    replication     all           127.0.0.1/32            ident
host    replication     all           ::1/128                 ident
host    mydb            mydb          127.0.0.1/32         md5
host    replication     postgres      127.0.0.1/32            md5
host    mydb            postgres      127.0.0.1/32          md5

Copy the last line and paste it but with the IP address changed to the IP of your remote machine and the database username-schema pair changed to what you would like to connect to.

Example to allow all remote hosts

host    mydb   mydb   0.0.0.0/0   md5

The 2nd and 3rd columns are database name and user respectively. You can replace the 4th column with your remote ip.

If you would like to give access to any system in the network, instead of IP of your remote machine, you can give 0.0.0.0/0 and save the file.

3. Apply the configuration

Now, to apply the configuration, login to psql or any client and fire the below select query.

select pg_reload_conf();

This will reload the configuration. Now, you can connect from your remote machine.

4. Troubleshooting

If you still cannot connect to your PostgreSQL, check if you are able to telnet to that port.

telnet <postgres_ip>:<postgres_port> (or 5432)

If not check your iptables rules or your firewall configuration and allow the port. If your linux machine uses ufw then you can try the following command.

sudo ufw allow 5432

For iptables, you can use

iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

and check your telnet once again to see if it is working.
If it is still not working, check if you are able to at least ping to your PostgreSQL server. If not, you may need to change your DNS or proxy settings. If your PostgreSQL is run on a VM created in cloud, you may want to check your security group rules there.

ధన్యవాదాలు (Thanks)