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- gt available drivers true    drivers   DBI- gt installed drivers    my  dbh   DBI- gt connect  DBI PgPP database chaosLRdb host 192 168 0 1 port 5433    postgres    chaos123      May I know what i miss here

User · Answer

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

User · Answer

Add the following in line in pg hba conf hostnossl    all          all            0 0 0 0 0  trust And then restart the Service

User · Answer

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

User · Answer

Verify the postgres connection hostname address in pgadmin and use the same in your connection parameter  DBI connect  database chaosLRdb host  quot keep what is mentioned quot   port 5433   postgres

User · Answer

If you can change this line   host    all        all         192 168 0 1 32        md5   With this   host    all        all         all                   md5   You can see if this solves the problem   But another consideration is your postgresql port 5432  is very open to password attacks with hackers  maybe they can brute force the password   You can change your postgresql port 5432 to  33333  or another value  so they can t know this configuration

User · Answer

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   gt  d6766c33ba6cf0bb249b37151b068f10  -   So then your connect line would like something like   my  dbh   DBI- gt 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

User · Answer

For those who have the similar problem trying to connect to local db and trying like con   psycopg2 connect database  my db   user  my name   password  admin    try to pass the additional parameter  so the following saved me a day  con   psycopg2 connect database  my db   user  my name   password  admin   host  localhost

User · Answer

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

User · Answer

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   lt dataSource      jndiName  jdbc tableauPostgreSQL       type  javax sql ConnectionPoolDataSource  gt       lt jdbcDriver          javax sql ConnectionPoolDataSource  org postgresql ds PGConnectionPoolDataSource           javax sql DataSource  org postgresql ds PGPoolingDataSource           libraryRef  PostgreSqlJdbcLib   gt       lt properties          url  jdbc postgresql   server port mydb user fred amp amp password secret   gt   lt  dataSource gt    This would not work and was getting the error    lt properties      user  fred      password  secret      url  jdbc postgresql   server port mydb   gt

User · Answer

in my case i just changed spring postgresql jdbc url that contained IPV4  i changed it to 127 0 0 1

User · Answer

also check the PGHOST variable      ECHO  PGHOST   to see if it matches the local machine name

User · Answer

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

User · Answer

To resolve this problem  you can try this   first  you have found out your pg hba conf by   cd  etc postgresql 9 5 main from your root directory  and open file using   sudo nano pg hba conf   then add this line   local   all         all                               md5   to your pg hba conf and then restart by using the command   sudo service postgresql restart

[perl] no pg_hba.conf entry for host

Examples related to perl

Examples related to postgresql

Examples related to database-permissions