13.4 Connecting to a Remote PostgreSQL Database

If your PostgreSQL database is installed on a separate server, you need to change the default settings in the postgresql.conf and pg_hba.conf files in the remote database.

  1. Change the listening address in the postgresql.conf file.

    By default, PostgreSQL allows to listen for the localhost connection. It does not allow a remote TCP/IP connection. To allow a remoteTCP/IP connection, add the following entry to the C:\NetIQ\idm\postgres\data\postgresql.conf file:

    listen_addresses = '*'

    If you have multiple interfaces on the server, you can specify a specific interface to be listened.

  2. Add a client authentication entry to the pg_hba.conf file.

    By default, PostgreSQL accepts connections only from the localhost. It refuses remote connections. This is controlled by applying an access control rule that allows a user to log in from an IP address after providing a valid password (the md5 keyword). To accept a remote connection, add the following entry to the C:\NetIQ\idm\postgres\data\pg_hba.conf file.

    host all all md5

    For example, trust

    This works only for IPv4 addresses. For IPv6 addresses, add the following entry:

    host all all ::0/0 md5

    If you want to allow connection from multiple client computers on a specific network, specify the network address in the CIDR-address format in this entry.

    The pg_hba.conf file supports the following client authentication formats.

    • local database user authentication-method [authentication-option]

    • host database user CIDR-address authentication-method [authentication-option]

    • hostssl database user CIDR-address authentication-method [authentication-option]

    • hostnossl database user CIDR-address authentication-method [authentication-option]

    Instead of CIDR-address format, you can specify the IP address and the network mask in separate fields using the following format:

    • host database user IP-address IP-mask authentication-method [authentication-option]

    • hostssl database user IP-address IP-mask authentication-method [authentication-option]

    • hostnossl database user IP-address IP-mask authentication-method [authentication-option]

  3. Test the remote connection.

    1. Restart the remote PostgreSQL server.

    2. Log in to the server remotely using the username and password.