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 offHere 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 trustMy 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?
16 Answers
If you can change this line:
host all all 192.168.0.1/32 md5With this:
host all all all md5You 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.
2In 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 trustI 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.
1Your postgres server configuration seems correcthost all all 127.0.0.1/32 md5
host all all 192.168.0.1/32 trust
Test this by creating a specific user for that databasecreateuser -a -d -W -U postgres chaosuser
Then adjust your perl script to use the newly created usermy $dbh = DBI->connect("DBI:PgPP:database=chaosLRdb;host=192.168.0.1;port=5433", "chaosuser", "chaos123");
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.confthen add this line:
local all all md5to your pg_hba.conf and then restart by using the command:
sudo service postgresql restartTo resolved this problem, you can try this.
first you have find out your pg_hba.conf and write :
local all all md5after that restart pg server:
postgresql restartor
sudo /etc/init.d/postgresql restartAdd 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 an error like the one below:
OperationalError: FATAL: no pg_hba.conf entry for host "your ipv6", user "username", database "postgres", SSL offthen add an entry like the following, with your mac address.
host all all [your ipv6]/128 md5I 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.
For those who have the similar problem trying to connect to local db and trying likecon = 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")
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.
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 }
})also check the PGHOST variable:
ECHO $PGHOST
to see if it matches the local machine name
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&password=secret"/>
</dataSource>This would not work and was getting the error:
<properties user="fred" password="secret" url="jdbc:postgresql://server:port/mydb"/>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 }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',...)
1
