Allow Remote Connections To Postgresql

Working with the PostgreSQL in server is fine. But, sometimes we need to access the server or cloud database from our local machine. But, when we try to connect to the PostgresSQL server database from local it will not connect. Because, by the default configuration PostgreSQL only allows host "localhost" or "12.0.0.1". To allow remote connections to the PostgreSQL database we need to modify the database configuration files.

Configuring postgresql.conf

It is the configuration file of the postgres database. It contains the configuration settings like application port number, maximum connections, ssl, etc. To allow connections from any IP address then we need open the "postgresql.conf" file and find for configuration variable listen_addresses and replace it with listen_addresses="*". In ubuntu operating system the file can be found in the location /etc/postgresql/10/main . The file location is different for other operating systems. Now, we need to allow the authentication of clients.

Configuring pg_hba.conf

The pg_hba.conf file allows authentication between the PostgreSQL server and the client application. By default it only allows the connections from the host localhost. But, we need to connect from anywhere in the world. So, we need to add the below lines at the end of the file pg_hba.conf. In ubuntu operating system the file can be found in the location /etc/postgresql/10/main . The file location is different for other operating systems.

host all all 0.0.0.0/0 md5
host all all ::/0 md5

In above lines first line is for IPV4 and second is for IPV6.

Now, restart the postgresql database service and connect to the remote database from your local machine.