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
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
If you want to allow remote connection from a specific IP such as 126.96.36.199 then add the following line to pg_hba.conf file.
host all all 188.8.131.52/32 trust
The above line allows remote access from 184.108.40.206 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 220.127.116.11/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
Also read : How to Compare Two Schemas in PostgreSQL
4. Test Remote connection
From your remote machine at 18.104.22.168, run the following command, to log into your PostgreSQL server (e.g IP – 22.214.171.124). Replace your_username below with your database username
# psql -U your_username -h 126.96.36.199 Welcome to psql 8.1.11 (server 8.4.18), the PostgreSQL interactive terminal. postgres=#