By default, PostgreSQL is configured only to accept connections that come from the localhost. However, you may encounter such scenarios where you need to allow the remote connections to your PostgreSQL server.
This tutorial explores the various steps and techniques to configure a PostgreSQL server to accept the remote connections.
It is good to remember that allowing remote connections to your database increases the attack surface and can lead to attacks. Therefore, use this feature with caution.
Requirements:
Before proceeding with this tutorial, ensure that you have the following:
- Installed and running PostgreSQL server.
- Administrative access to the PostgreSQL server.
- Basic knowledge of PostgreSQL configuration files and command-line tools.
Allow the Remote IP Address
By default, PostgreSQL is configured to listen on the localhost address. Therefore, to allow remote connections, we need to let PostgreSQL listen on an external IP address by modifying the server configuration.
Start by locating the PostgreSQL configuration file. It is named “postgresql.conf”. The location may vary depending on the installed version and the target system.
However, on Linux, you can check the /etc/postgresql//main directory as follows:
If you are on Windows, you can check the C:\Program Files\PostgreSQL\<version>\data directory as follows:
Open the configuration file with your favorite text editor.
In the configuration file, locate the listen_address parameter and change the value from the localhost to the IP address that you wish to connect.
For example, the following entry allows PostgreSQL to listen on all addresses (not recommended):
Once you add the target IP address, save and close the configuration file.
NOTE: If you have a firewall configured, ensure that the firewall allows the connection to and from the PostgreSQL port.
Configure the Remote Authentication
The next step is to configure the authentication method for remote connections. PostgreSQL uses the “pg_hba.conf” file to manage the authentication rules.
By default, PostgreSQL allows the unauthenticated connections from the localhost. However, we need to disable this for remote connections.
Locate the “pg_hba.conf” file in the same directory as the “PostgreSQL.conf” file. Edit the configuration file with your text editor. Locate the IPv4 local remote connection or the IPv6 local remote connection entries in the configuration file.
The next step is to add an entry which allows the remote connection from a specific address or network range. We can do this by specifying the IP address, subnet, or host.
An example is as follows:
host all all 192.168.0.100 md5
The previous entry tells PostgreSQL to allow the remote connections from the IP address 192.168.0.100.
We also tell PostgreSQL to use the md5 authentication method which requires a username and password combination.
Close the file and save the modified changes.
Restart the PostgreSQL Server
Once you made some modifications to the server configuration, you need to restart it to apply the changes.
On Windows, you can do this by starting and stopping the PostgreSQL service. Use the net command as follows:
net start postgresql
If you are on Linux, you can run the following command:
Once the server is restarted, verify the connection from the remote host using the psql command-line tool.
Conclusion
This tutorial taught us how to allow the remote connections to a PostgreSQL server to modify the PostgreSQL configuration and configure the authentication in the “pg_hba.conf” file. However, as stated, allowing remote connections to a database can have severe security implications.
