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