How to enable remote access to PostgreSQL databases hosted in Plesk

Connect to the Plesk server via SSH.

Modify postgresql.conf:

2.1. Open the file /var/lib/pgsql/data/postgresql.conf in 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.conf in 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.conf in 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.conf in 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