How to enable remote access to PostgreSQL server on a Plesk server?
Note: the instructions below were tested on a Plesk server, however, they can be applicable to a non-Plesk server as well keeping in mind the possible different paths to the files.
To enable remote access to PostgreSQL server:
Connect to the PostgreSQL server via SSH.
Get location of
postgresql.conffile by executing the command (it should be something like
# psql -U postgres -c ‘SHOW config_file’
postgresql.conffile and add the following line to the end:
listen_addresses = '*'
- Get the location of pg_hba.conf file:
# grep pg_hba.conf /var/lib/pgsql/data/postgresql.conf
/var/lib/pgsql/data/postgresql.confis the file from output of step 2
Add the following line to the end of
host samerole all 203.0.113.2/32 md5
- 203.0.113.2/32 is the remote IP from which connection is allowed. If you want to allow connection from any IP specify 0.0.0.0/0 .
- md5 is the authentication method, which requires the client to supply a double-MD5-hashed password for authentication.
- User john.doe from database example1 has access to database example1 only.
As for other authentication methods refer to PostgreSQL documentation.
Restart PostgreSQL server to apply the changes: Plesk > Tools & Settings > Services
Note: if PostgreSQL port was not customized then port 5432 can be configured on the firewall in order to allow access to specific IPs using the following article.