主机没有 pg_hba. conf 条目

当我尝试使用 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

这是我的 pg _ hba. conf 文件:

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

我的 perl 代码是

#!/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");

我能知道我错过了什么吗？

330455 次浏览
小开

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

小开

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.

小开

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
小开

also check the PGHOST variable:

ECHO $PGHOST

to see if it matches the local machine name

小开

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

小开

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.

小开

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
小开

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

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.

小开

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
小开

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',...)

小开

Add the following in line in pg_hba.conf

hostnossl all all 0.0.0.0/0 trust

And then restart the Service.

小开

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
}
})
小开

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.

小开

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

小开

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

  ssl: {
rejectUnauthorized: false
}
小开

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

enter image description here


提交答案