Articles in this section

See more

How to enable remote access to PostgreSQL databases hosted in Plesk

Avatar
Kuzma Ivanov
Updated
kb: how-to Plesk for Linux ABT: Group B

Applicable to:

  • Plesk for Linux

Question

How to enable remote access to PostgreSQL databases hosted in Plesk?

Answer

 

For Plesk on CentOS/RHEL

 

  1. Connect to the Plesk server via SSH.

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

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

  4. Restart PostgreSQL service to apply the changes:

    # service postgresql restart

 

For Plesk on Debian/Ubuntu

 

  1. Connect to the Plesk server via SSH.

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

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

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

  5. Restart PostgreSQL service to apply the changes:

    # service postgresql restart

 

Comments

1 comment

Sort by Date Votes
  • Avatar
    Yunus @ dlabsit.nl (Edited )

    Hello

    To make the remote access work from the Plesk Panel this is my setup

    Step 1: edit postgresql.conf change the line to:

    listen_addresses = '*'

    Step 2: add the following lines in pg_hba.conf:

    # Allow all IPv4 addresses with SCRAM-SHA-256
host    all    all    0.0.0.0/0    scram-sha-256

# Allow all IPv6 addresses with SCRAM-SHA-256
host    all    all    ::/0         scram-sha-256

    Step 3: In the Plesk Admin panel. Change the firewall service for PostgreSQL server to DENY.

    Now when you make a user there you can specify for each database the remote access. Every change needs to be applied in the Firewall page.

    0

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles