ETL Postgres database connection

[JLehr]

Q: Why can't I connect to the postgresql database? The log file contains following statement:

2016-09-09 04:33:07 GMT 13316 export_etl_db FATAL:  pg_hba.conf rejects connection for host "10.11.12.13", user "postgres", database "export_etl_db", SSL off

A: The error message indicates that a user called postgres was trying to access the database export_etl_db from the host 10.11.12.13 but was rejected due to access-restrictions.

To fix the problem, you need to configure the postgresql-server to allow access. This is done by editing the configuration file pg_hba.conf placed in the folder /etc/isx-operations/postgresql/, adding the line host export_etl_db postgres 10.11.12.13/32 md5 so that the configuration file looks something like this:

# Database administrative login by UNIX sockets
local   all         postgres                          ident
  
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD    
 
# "local" is for Unix domain socket connections only
#local   all         all                               md5
# IPv4 local connections:
#host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
#host    all         all         ::1/128               md5
  
  
hostssl all all 10.216.73.208/32 cert
hostssl all all 127.0.0.1/32 cert
host staging_hp etluser_hp 10.216.73.208/32 md5
host staging_hp etluser_hp 127.0.0.1/32 md5
host staging_hp etluser_hp 10.216.0.0/16 md5
host export_export etluser_exp 10.216.73.208/32 md5
host export_export etluser_exp 127.0.0.1/32 md5
host export_export etluser_exp 10.216.0.0/16 md5
  
host export_etl_db postgres 10.11.12.13/32 md5

This tells postgres to allow host access (net-based access) to the database export_etl_db for user postgres IF and only if the source IP is 10.11.12.13 and if the correct password is provided.

If the IP is likely to change (DHCP), you can set a netmask as 10.11.12.0/24.

If different users need access, one line per user is needed or, alternatively, you can type in the all keyword which matches any user.

After editing the configuration file, you need to reload postgres.

From version 9.1.x:

# sudo systemctl restart postgresql