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?

Bennink

Answer 1

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.

Naturalism

I tried to do it like "host all all * md5" . But it didn't worked. But I got succeeded in your way. Thanks a lot. – Hautboy

This solution made me realize that I did not understand the fourth parameter at all, thank you! – Intermolecular

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.

Lacedaemon

Yeah, it probably refused to load the pg_hba.conf file because of the errors. – Tidal

Answer 3

If you are getting this error using node and pg module you can set ssl to not reject unauthorized access like this

const pool = new Pool({
    connectionString: "your connection string",
    ssl: {
        rejectUnauthorized: false
    }
})

Lossa

Answer 4

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");

Gaggle

The fact that he got an error about a missing pg_hba.conf line means that it hasn't gotten to checking the password yet. – Tidal

Answer 5

Add the following in line in pg_hba.conf

hostnossl all all 0.0.0.0/0 trust

And then restart the Service.

Depilate

Isn't this super insecure? – Nidanidaros

Answer 6

I faced the same issue. My db was on cloud

Error:

ERROR: no pg_hba.conf entry for host ".......", user ".........", database "....", SSL off

I add this configuration to resolve this,

    "dialect": "postgres",
    "dialectOptions": {
        "ssl": {
            "require": true,
            "rejectUnauthorized": false
        }
    }

SSL parameter is the key telling DB to always use SSL for making connections.

Lambent

There is no param dialectOptions.ssl.require in pg package, see source file: node_modules\pg\lib\connection-parameters.js, so it is useless. – Folger

Answer 7

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

Vaivode

Hello 'local' is relevant for unix socket connections. Why do you think it should help with the network problem? (I have this line in my pg_hba.conf, too, and have the same problem, too, plus my postgresql server does not listen on unix socket.) – Panarabism

Answer 8

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

Cohette

Answer 9

Use SSL in the connection. This solves it instantly

Nostomania

Answer 10

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

Myrtice

My problem was related to the IPV6 address, so, adding specifically my IPV6 address solved the problem. Thanks Shiddu – Unhandled

Answer 11

while making the connection include ssl prop in configugration like this:

  ssl: {
    rejectUnauthorized: false
  }

Sparkie

Answer 12

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

Telegraphy

Using 'local' saved my day too! The full host name works remotely but it did not work locally. – Isotone

Answer 13

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.

Napier

Answer 14

I have used Docker container to run postgres. I faced this issue and to resolve this, I used POSTGRES_HOST_AUTH_METHOD=trust env variable at the time of starting container.

Here is an example command docker run --name postgres --net appnet -e POSTGRES_PASSWORD=password -p 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust -d postgres

If you want to connect postgres container to any other application which is running inside a docker container, it is best to run both the containers in same network. In this case, I have created a network appnet using docker network create command, used it to create my application container and postgres container.

Cantabrigian

this, this one works in Kubernetes. – Administration

Answer 15

On my server that was running postgresql, I edited the file /etc/postgresql/11/main/pg_hba.conf and added the following line:

host    all             all             10.2.3.100/32            md5

I then restarted the postgresql server using the command service postgresql restart to pick up this change. This allowed pgAdmin to connect to my postgresql server. pgAdmin was running on my desktop computer (the client) which has IP address 10.2.3.100. The postgresql server was running on a server (the server) with IP address 10.2.3.50

Nescience

Answer 16

also check the PGHOST variable:

ECHO $PGHOST

to see if it matches the local machine name

Soso

Answer 17

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

Genoa

Answer 18

Please add the following line in /etc/postgresql/14/main/pg_hba.conf file

#IPv4 local connections:

host all all 127.0.0.1/32 scram-sha-256

host all all all md5

Depilate

Answer 19

I've got the same issue in Azure Data Factory while connecting to Azure Database for PostgreSQL.

28000: no pg_hba.conf entry for host "", user "", database "postgres", SSL off

Here the issue was due to PostgreSQL database has ssl_min_protocol_version set to TLSV1.2 expecting a encrypted connection and the client connection was not using any encryption.

I've resolved the issue by setting the property "Encryption method" to SSL

Debatable

Answer 20

In one project I was facing this issue and after all changes like in config the issue was still same. Then the steps that I followed: I restart the PgSQL from WHM Home / Restart Services / SQL Server (PgSQL)

Create the new db

Create new user

Assign user to db

that was missing in earlier "I created new because it was live server and uploading files on that server for the first time."

Add new db name and password in env

Import the DB from terminal psql -U {_new_user} -d {new_db} -f {db.sql}

Now it's done and run the application If it's still not working after env change then try with cache clear

Apollonian

Answer 21

In NodeJS using postgres-pool package there is one option specifically for the AWS RDS connections.

const pgPool = new Pool({
  connectionString: connectionString,
  ssl: 'aws-rds' // AWS RDS specific TLS settings for connections
});

Below refer this package interface comment for your ready reference.

Concave

Answer 22

I had a similar issue then realized it was something silly. Using Java Spring Boot, my connection url looked something like:

spring.datasource.url = jdbc:postgresql://database:5432/database_dev?sslmode=disable

This is fine for dev environments, but the ?sslmode=disable needs to be remove for prod environments:

spring.datasource.url = jdbc:postgresql://database:5432/database_prod

After that, it worked! :)

Heterogamy

Answer 23

Reporting my experience where users are facing the error message:

FATAL:  no pg_hba.conf entry for host "my.host", user "my_user", database "myDB", SSL off

Enabling SSL (DBeaver connecting to Redshift) as per:

helped realising that their credentials have been updated by having the easier error message:

FATAL: password authentication failed for user "my_user"

Acantho

Answer 24

Interesting thing, but this is fact.

--package.json---
dependencies:
   "pg": "^8.11.3",
   "sequelize": "^4.44.4"
}

This is test app with dialectOptions - connection is alive.

const sequelize = new Sequelize({
  database: "my_database",
  host: "myhost",
  port: 5432,
  username: "myusername",
  password: "myPass",
  dialect: "postgres",
  dialectOptions: {
    ssl: {
      rejectUnauthorized: false
    },
  },
});

sequelize
  .authenticate()
  .then(() => {
    console.log("******************we are good - connection is alive****************");
  })
  .catch((e) => {
    console.error("Troubles. Error", e);
  });

If dialectOptions is empty, no ssl property, app fails with error:

Unhandled rejection SequelizeConnectionError: no pg_hba.conf entry for host "myhost", user "myusername", database "my_database", no encryption

What is interesting, if dialectOptions: {ssl:{}} (ssl is empty object, which means all flags set to false) it works too - connection is alive.

Not sure if it is a defect of pg@8 or something else.

Folger

Answer 25

Add the following in line in pg_hba.conf

hostnossl all all 0.0.0.0/0 trust

restart the server

Friedland

Answer 26

Verify the postgres connection hostname/address in pgadmin and use the same in your connection parameter.

DBI connect('database=chaosLRdb;host="keep what is mentioned" ;port=5433','postgres',...)

Lamentation

Welcome to Stack Overflow! The user never mentions it can access the database using pgAdmin. – Adeline