How to Setup Remote Connection to PostgreSQL

Last updated on August 16th, 2024 at 09:40 am

Often database administrators need to enable remote connections to their PostgreSQL database. 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.

Why Enable Remote Access in PostgreSQL

Remote access is often required in applications that access database from different IP addresses or geo locations. It is also required if your database developers and software programmers are working remotely. It is also useful if you want to fix issues that require remote assistance. While it is important to allow remote access we also need to be careful about not allowing unauthorized access.

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. Update Firewall

PostgreSQL server listens to port 5432. By default, it is closed to remote connections. So you need to open port 5432 for incoming connections. For more security, you may allow only the above mentioned IP address to access port 5432, instead of giving access to all IP addresses.

4. Restart PostgreSQL Server

Restart PostgreSQL server to apply changes

# sudo /etc/init.d/postgresql restart

Also read : How to Compare Two Schemas in PostgreSQL

5. 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=#

Conclusion

In this article, we have learnt how to enable remote access to PostgreSQL database to support remote connections. Allowing remote connection is a common requirement for all database administrators. It is important to be careful while allow remote access. Give access to only specific IP addresses and ranges, instead of all IP addresses. If you need to give only temporary access, the undo step #1 or #2, once the requirement is over. That will disable remote access once again. 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 from PostgreSQL database. Try Ubiq for free.