How to enable remote PostgreSQL access

Introduction

This article shows how to enable remote access for PostgreSQL.

Procedure

1. SSH into the server as the 'root' user.
2. Open /var/lib/pgsql/data/postgresql.conf in a text editor, such as nano or vim.
3. Find the "listen_addresses" line and add the IP address PostgreSQL should listen on, or add the '*' character to allow it to listen on all addresses.

   listen_addresses = '*'

4. Save the changes and exit the text editor.
5. Open /var/lib/pgsql/data/pg_hba.conf in a text editor, such as nano or vim.
6. Add a new 'host' entry at the end of the file.
   • To allow access for each IP address that will connect to the PostgreSQL server, add a new line for each connecting address using the following format.

     host       database  user  IP-address  IP-mask  auth-method  [auth-options]

   • To allow access for all users from all addresses, add the following line.

     host       samerole       all    0.0.0.0/0      md5

     Please note, we strongly recommend using the md5 auth option to force authentication as a good security practice.
7. Save the changes and exit the text editor.
8. Restart the PostgreSQL service.

   /scripts/restartsrv_postgresql