no pg_hba.conf entry for host

Question

I get following error when I try to connect using DBI

DBI connect('database=chaosLRdb;host=192.168.0.1;port=5433','postgres',...) 
failed: FATAL:  no pg_hba.conf entry for host "192.168.0.1", user "postgres", database "chaosLRdb", SSL off

Here is my pg_hba.conf file:

# "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

host    all         postgres    127.0.0.1/32          trust

host    all        postgres     192.168.0.1/32        trust

host    all        all         192.168.0.1/32        trust

host    all        all         192.168.0.1/128        trust

host    all        all         192.168.0.1/32        md5

host    chaosLRdb    postgres         192.168.0.1/32      md5
local    all        all         192.168.0.1/32        trust

My perl code is

#!/usr/bin/perl-w
use DBI;
use FileHandle;

print "Start connecting to the DB...\n";

@ary = DBI->available_drivers(true);
%drivers = DBI->installed_drivers();
my $dbh = DBI->connect("DBI:PgPP:database=chaosLRdb;host=192.168.0.1;port=5433", "postgres", "chaos123");

May I know what i miss here?

Answer 1

For those who are getting this error in DBeaver the solution was found here at line:

@lcustodio on the SSL page, set SSL mode: require and either leave the SSL Factory blank or use the org.postgresql.ssl.NonValidatingFactory

Under Network -> SSL tab I checked the Use SLL checkbox and set Advance -> SSL Mode = require and it now works.

Answer 2

In your pg_hba.conf file, I see some incorrect and confusing lines:

# fine, this allows all dbs, all users, to be trusted from 192.168.0.1/32
# not recommend because of the lax permissions
host    all        all         192.168.0.1/32        trust

# wrong, /128 is an invalid netmask for ipv4, this line should be removed
host    all        all         192.168.0.1/128       trust

# this conflicts with the first line
# it says that that the password should be md5 and not plaintext
# I think the first line should be removed
host    all        all         192.168.0.1/32        md5

# this is fine except is it unnecessary because of the previous line
# which allows any user and any database to connect with md5 password
host    chaosLRdb  postgres    192.168.0.1/32        md5

# wrong, on local lines, an IP cannot be specified
# remove the 4th column
local   all        all         192.168.0.1/32        trust

I suspect that if you md5'd the password, this might work if you trim the lines. To get the md5 you can use perl or the following shell script:

 echo -n 'chaos123' | md5sum
 > d6766c33ba6cf0bb249b37151b068f10  -

So then your connect line would like something like:

my $dbh = DBI->connect("DBI:PgPP:database=chaosLRdb;host=192.168.0.1;port=5433",
    "chaosuser", "d6766c33ba6cf0bb249b37151b068f10");

For more information, here's the documentation of postgres 8.X's pg_hba.conf file.

Answer 3

Your postgres server configuration seems correct

host    all         all         127.0.0.1/32          md5
host    all         all         192.168.0.1/32        trust

That should grant access from the client to the postgres server. So that leads me to believe the username / password is whats failing.

Test this by creating a specific user for that database

createuser -a -d -W -U postgres chaosuser

Then adjust your perl script to use the newly created user

my $dbh = DBI->connect("DBI:PgPP:database=chaosLRdb;host=192.168.0.1;port=5433", "chaosuser", "chaos123");

Answer 4

BTW, in my case it was that I needed to specify the user/pwd in the url, not as independent properties, they were ignored and my OS user was used to connect

My config is in a WebSphere 8.5.5 server.xml file

<dataSource 
    jndiName="jdbc/tableauPostgreSQL" 
    type="javax.sql.ConnectionPoolDataSource">
    <jdbcDriver 
        javax.sql.ConnectionPoolDataSource="org.postgresql.ds.PGConnectionPoolDataSource" 
        javax.sql.DataSource="org.postgresql.ds.PGPoolingDataSource" 
        libraryRef="PostgreSqlJdbcLib"/>
    <properties 
        url="jdbc:postgresql://server:port/mydb?user=fred&amp;password=secret"/>
</dataSource>

This would not work and was getting the error:

<properties 
    user="fred"
    password="secret"
    url="jdbc:postgresql://server:port/mydb"/>

Answer 5

To resolved this problem, you can try this.

first you have find out your pg_hba.conf and write :

local all all md5

after that restart pg server:

postgresql restart

or

sudo /etc/init.d/postgresql restart

Answer 6

If you are getting an error like the one below:

OperationalError: FATAL:  no pg_hba.conf entry for host "your ipv6",
                  user "username", database "postgres", SSL off

then add an entry like the following, with your mac address.

host   all    all       [your ipv6]/128         md5