Articles in this section

See more

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

Avatar
Renan Genova Ferreira
Updated
Follow
Plesk for Linux kb: how-to ABT: Group B

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:

  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:

    CONFIG_TEXT: 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:

    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.

  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.

Comments

5 comments

Sort by Date Votes
  • Avatar
    Unknown User

    It's crazy to suggest `trust` as authentication method for a remote connection, please take down this guide ASAP.

    0
    Comment actions Permalink
  • Avatar
    Artyom Volov

    Hello @Michael Banck,

    Thank you for your feedback!

    The article was updated to include "md5" authentification method to avoid any security issues.

    0
    Comment actions Permalink
  • Avatar
    vpnnetworks17

    No nothing has been fixed! Still cant connect remotely !!

    0
    Comment actions Permalink
  • Avatar
    Lev Iurev

    @vpnnetworks17 check that connection is allowed by firewall. Should the issue persist please create a ticket to our support.

    0
    Comment actions Permalink
  • Avatar
    Javier (Edited )

    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.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles