How to enable remote access to PostgreSQL server on a Plesk server?

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:

1. Connect to the PostgreSQL server via SSH.

2. 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’

3. Open postgresql.conf file and add the following line to the end:

   listen_addresses = '*'

4. Get the location of pg_hba.conf file:

   # grep pg_hba.conf /var/lib/pgsql/data/postgresql.conf
   /var/lib/pgsql/data/pg_hba.conf

   where /var/lib/pgsql/data/postgresql.conf is the file from output of step 2

5. 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.

6. 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.