Enable Remote Access on a PostgreSQL Server
Steps to allow remote connections to a PostgreSQL server.
Requirements
- PostgreSQL 12
- Debian 10 (buster)
Procedure
The following example grants access to the IP address 192.168.32.1.
1. Allow Remote Addresses
Locate the postgresql.conf file.
sudo find / -name "postgresql.conf"
/etc/postgresql/12/main/postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf
...
Open the file in /etc/postgresql.
sudo nano /etc/postgresql/12/main/postgresql.conf
Add the following line at the end of the file.
listen_addresses = '*'
Example snippet:
postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# ----------------------------
...
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
listen_addresses = '*'
2. Configure Allowed IP Addresses
Locate the pg_hba.conf file.
sudo find / -name "pg_hba.conf"
/etc/postgresql/12/main/pg_hba.conf
...
Open the file within /etc/postgresql.
sudo nano /etc/postgresql/12/main/pg_hba.conf
Append the following line at the end.
host all all 192.168.32.1/32 md5
Example snippet:
pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
...
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
host all all 192.168.32.1/32 md5
Note:
- To allow a single IP, use mask
/32. Example:192.168.32.1/32. - To allow a range of IP addresses, use another mask. Example:
192.168.32.1/24grants access from192.168.32.1through192.168.32.254.
3. Restart the Service
Restart PostgreSQL to apply the changes.
sudo service postgresql restart
References
Published: June 2, 2020