Tools

Configure access to an embedded PostreSQL database from multiple hosts

By default, PostgreSQL does not allow external connections. This article describes how to configure embedded PostgreSQL in Microsoft® Windows®, Linux, and a Virtual Appliance to allow connections from a machine other than the PostgreSQL server.

First published date

10/18/2018 9:57 PM

Last published date

6/5/2019 7:11 PM

Overview

PostgreSQL does not allow external connections, by default. This article describes how to configure embedded PostgreSQL in Microsoft® Windows®, Linux, and a Virtual Appliance to allow connections from a machine other than the PostgreSQL server itself.

Product section

Web Help Desk

Resolution

Linux/CentOS (Virtual Appliance)

  1. Connect to the virtual appliance VA using an SSH client (such as PuTTY).
  2. Enter admin in the user name and password fields to log in.
  3. Change directories to /data1.
    The Linux path is:
     /usr/local/webhelpdesk/bin/pgsql/var/lib/pgsql/9.2/data
    Command: cd /data1
  4. Use the 'sudo' command to edit the config files with Vi editor. Make a copy of the original file before editing.
Command:  
sudo cp pgdata/postgresql.conf pgdata/postgresql.conf.original.
Command:  
sudo vi pgdata/postgresql.conf.
  1. In the connections and authentication section, uncomment listen_addresses and set to:
    listen_addresses = '*'
  2. Save your changes.
    Type :w and press Enter. 
  3. Exit vi.
    Type :q and press Enter. 

  4. Edit the pg_hba.conf file. Make a copy of the original file before editing.
    Type: 

        sudo cp pgdata/pg_hba.conf pgdata/pg_hba.conf.original.
        sudo vi pgdata/pg_hba.conf.
    The pg_hba.conf file should contain following lines:

    local all all trust
    host all all 127.0.0.1/32 trust
    host all all    ::1/128 trust
    host all whd 0.0.0.0/0 md5

  5. Add a line with your_ip. your_ip that represents the IP address of the host from which you want to connect with pg Admin.
    For example,

    local all all trust
    host all all 127.0.0.1/32 trust
    host all all your_ip/32 trust
    host all all    ::1/128 trust
    host all whd 0.0.0.0/0 md5

  6. Restart WHD.

    1. Change to the WHD directory.
      Type:
      cd /usr/local/webhelpdesk

    2. Stop Web Help Desk.
      Type:
      ./whd stop

    3. Start Web Help Desk.
      Type:
      ./whd start 

Microsoft Windows

  1. Log in to the Web Help Desk server as an administrator.
  2. Navigate to <WebHelpDesk>\pgsql9\data.
  3. Back up the following files to a separate location:
    - postgresql.conf 
    - pg_hba.conf 
  4. Open the postgresql.conf file in a text editor (such as Notepad).
  5. In the connections and authenticationsection, uncomment listen_addresses and set the parameter to:  listen_addresses = '*'
  6. Save your changes.
  7. Open the pg_hba file in a text editor. 
  8. Locate the following lines: 
    # IPv4 local connections:
    host all all 127.0.0.1/32 password
    # IPv6 local connections:
    host all all ::1/128 password
  9. Add a line with your_ip. your_ip that represents the ip address from which you want to connect with pg Admin.
    For example,
    # IPv4 local connections:
    host all all 127.0.0.1/32 password
    host all all your_IP/32 password
  10. Click Save.
  11. Stop WHD using the Start Menu options (right-click and select Run as Admin).
  12. Stop PostgreSQL service under services.msc.
  13. Start PostgreSQL service under services.msc.
  14. Start WHD using the Start Menu options (right-click and select Run as Admin).