Question
How to enable remote access to PostgreSQL server on a Plesk server?
Answer
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.conf
file by executing the command (it should be something like/var/lib/pgsql/data/postgresql.conf
):# psql -U postgres -c ‘SHOW config_file’
-
Open
postgresql.conf
file 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/pg_hba.confwhere
/var/lib/pgsql/data/postgresql.conf
is the file from output of step 2 -
Add the following line to the end of
/var/lib/pgsql/data/pg_hba.conf
file: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.