remote connection in postgresql

How to Setup Remote Connection to PostgreSQL

You can easily enable remote access in PostgreSQL and allow remote connections from anywhere you want. In this article we will look at how to setup remote connection to PostgreSQL.


How to Setup Remote Connection to PostgreSQL

Here is how to setup remote connection in PostgreSQL.


1. Modify PostgreSQl.conf

PostgreSQL uses two configuration files postgresql.conf and pg_hba.conf that we need to update, to enable remote connection. Open terminal and run the following command to get the location of postgresql.conf file.

# psql -U postgres -c 'SHOW config_file'

You will see the location of postgresql

# /var/lib/pgsql/data/postgresql.conf

Open postgresql.conf file using a text editor

# sudo vi /var/lib/pgsql/data/postgresql.conf

Add the following line at its end.

listen_addresses = '*'

Sometimes you may find the following line already present in your file

listen_addresses = 'localhost'

In such cases, please modify this line to the following, instead of adding it separately.

listen_addresses = '*'

Save and close the file.

Also read : How to List databases and tables in PostgreSQL


2. Modify pg_hba.conf

Similarly, find the location of pg_hba.conf file.

# grep pg_hba.conf /var/lib/pgsql/data/postgresql.conf

You will see the location of pg_hba.conf file

# /var/lib/pgsql/data/pg_hba.conf

If you want to allow remote connection from a specific IP such as 210.201.102.100 then add the following line to pg_hba.conf file.

host    all         all         210.201.102.100/32    trust

The above line allows remote access from 201.201.102.100 IP, and uses CIDR notation to specify the IP address. Using CIDR notation, you can enable access for a single IP, as well as a range of IPs. If you want to allow access to multiple range of IPs, then add a separate line for each IP range.

Also, it uses a trust-based authentication. If you want to use a more secure authentication method like MD5 (recommended), modify the above line to

host    all         all         210.201.102.100/32    md5

If you want to allow remote access from all IPs, then use 0.0.0.0/0 as shown below

host    all         all         0.0.0.0/0    md5

Also read : How to Create Read Only User in PostgreSQL


3. Restart PostgreSQL Server

Restart PostgreSQL server to apply changes

# sudo /etc/init.d/postgresql restart

Also read : How to Compare Two Schemas in PostgreSQL


4. Test Remote connection

From your remote machine at 210.201.102.100, run the following command, to log into your PostgreSQL server (e.g IP – 144.41.14.44). Replace your_username below with your database username

# psql -U your_username -h 144.41.14.44 
Welcome to psql 8.1.11 (server 8.4.18), the PostgreSQL interactive terminal. 
postgres=#

Hopefully, the above article will help you enable remote access to PostgreSQL server. Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!