Article info

Follow
  • Created by Jackie Lehr, last updated
Was this article helpful?
1 out of 1 found this helpful

In this article

    ETL Postgres database connection

    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/postgresql (for versions up to 7.x, the folder is: /etc/postgresql/9.1/main), 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 (netbased 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.169.115.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 8.x:

    # sudo systemctl restart postgresql-9.5

    For versions up to 7.x:

    # sudo service postgresql reload

    0 comments

    Please sign in to leave a comment.