Applicable to:
- Plesk for Linux
Question
How to enable remote access to PostgreSQL databases hosted in Plesk?
Answer
-
Connect to the Plesk server via SSH.
-
Modify
postgresql.conf:2.1. Open the file
/var/lib/pgsql/data/postgresql.confin a text editor. In this example, we are using the vi editor:# vi /var/lib/pgsql/data/postgresql.conf
2.2. Add the following line at the end of the file:
CONFIG_TEXT: listen_addresses = '*'
2.3. Save the changes and close the file.
-
Modify
pg_hba.conf:3.1. Open the file
/var/lib/pgsql/data/pg_hba.confin a text editor:# vi /var/lib/pgsql/data/pg_hba.conf
3.2. Add the following line at the end of the file:
CONFIG_TEXT: host samerole all 203.0.113.2/32 md5
where:
- 203.0.113.2/32 - a remote IP address from which connection is allowed. To allow connections from any IP address, specify 0.0.0.0/0.
- md5 - authentication method, which requires the client to supply a double-MD5-hashed password for authentication. To learn more about authentication methods, visit this PostgreSQL documentation page.
3.3. Save the changes and close the file.
-
Restart PostgreSQL service to apply the changes:
# service postgresql restart
-
Connect to the Plesk server via SSH.
-
Get PosgreSQL release version to identify the installation path:
# psql -V
psql (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)In this example, PosgreSQL release version is 14, so the installation path will be
/etc/postgresql/14/. -
Modify
postgresql.conf:3.1. Open the file
/etc/postgresql/14/main/postgresql.confin a text editor. In this example, we are using the vi editor:# vi /etc/postgresql/14/main/postgresql.conf
3.2. Add the following line at the end of the file:
CONFIG_TEXT: listen_addresses = '*'
3.3. Save the changes and close the file.
-
Modify
pg_hba.conf:4.1. Open the file
/etc/postgresql/14/main/pg_hba.confin a text editor:# vi /etc/postgresql/14/main/pg_hba.conf
4.2. Add the following line at the end of the file:
CONFIG_TEXT: host samerole all 203.0.113.2/32 md5
where:
- 203.0.113.2/32 - a remote IP address from which connection is allowed. To allow connections from any IP address, specify 0.0.0.0/0.
- md5 - authentication method, which requires the client to supply a double-MD5-hashed password for authentication. To learn more about authentication methods, visit this PostgreSQL documentation page.
4.3. Save the changes and close the file.
-
Restart PostgreSQL service to apply the changes:
# service postgresql restart
Comments
5 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.
Hi,
I've done the modifications showed in this article, I've enabled the access for all IP but I'm not able to access to the server.
I have installed and enabled the Firewall and I've activated the rules but I cannot connect neither.
Do you have any idea?
Thanks in advance.
Please sign in to leave a comment.