Applicable to:
- Plesk for Linux
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.conffile by executing the command (it should be something like/var/lib/pgsql/data/postgresql.conf):# psql -U postgres -c 'SHOW config_file'
-
Open
postgresql.conffile and add the following line to the end:CONFIG_TEXT: listen_addresses = '*'
- Get the location of pg_hba.conf file:
# grep pg_hba.conf /var/lib/pgsql/data/postgresql.conf
where
/var/lib/pgsql/data/pg_hba.conf/var/lib/pgsql/data/postgresql.confis the file from output of step 2 -
Add the following line to the end of
/var/lib/pgsql/data/pg_hba.conffile:CONFIG_TEXT: 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.
Comments
4 comments
It's crazy to suggest `trust` as authentication method for a remote connection, please take down this guide ASAP.
Hello @Michael Banck,
Thank you for your feedback!
The article was updated to include "md5" authentification method to avoid any security issues.
No nothing has been fixed! Still cant connect remotely !!
@vpnnetworks17 check that connection is allowed by firewall. Should the issue persist please create a ticket to our support.
Please sign in to leave a comment.