No pg hba conf entry for host ошибка - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

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?

brian d foy

129k31 gold badges203 silver badges586 bronze badges

asked Sep 10, 2009 at 15:33

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.

Randall

2,8101 gold badge21 silver badges24 bronze badges

answered Jan 3, 2016 at 14:41

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.

answered Jan 18, 2011 at 23:16

GrayGray

115k23 gold badges291 silver badges354 bronze badges

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

answered Sep 10, 2009 at 21:11

adamadam

6,5424 gold badges29 silver badges28 bronze badges

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

answered Mar 17, 2020 at 14:58

Add the following in line in pg_hba.conf

hostnossl all all 0.0.0.0/0 trust

And then restart the Service.

answered Dec 1, 2020 at 11:11

Sumant SinghSumant Singh

8841 gold badge14 silver badges16 bronze badges

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.

answered Feb 22, 2022 at 12:29

Amit BaderiaAmit Baderia

4,2843 gold badges26 silver badges19 bronze badges

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

sudo /etc/init.d/postgresql restart

answered Mar 19, 2014 at 17:13

umauma

2,90226 silver badges20 bronze badges

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

Vincent Doba

4,1733 gold badges21 silver badges41 bronze badges

answered Jan 30, 2022 at 11:31

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

John Moutafis

21.9k11 gold badges68 silver badges112 bronze badges

answered Aug 2, 2017 at 8:57

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

answered Apr 3, 2015 at 15:25

YauhenYauhen

2,4251 gold badge16 silver badges18 bronze badges

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.

answered Oct 11, 2018 at 9:53

Guy LoweGuy Lowe

2,1151 gold badge27 silver badges37 bronze badges

also check the PGHOST variable:

ECHO $PGHOST

to see if it matches the local machine name

answered Sep 3, 2014 at 17:58

Lynx KeplerLynx Kepler

6621 gold badge9 silver badges21 bronze badges

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

answered Feb 28, 2018 at 18:17

Florin DFlorin D

1,59017 silver badges17 bronze badges

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

answered May 19, 2022 at 18:44

Sumant SinghSumant Singh

8841 gold badge14 silver badges16 bronze badges

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

  ssl: {
    rejectUnauthorized: false
  }

answered May 25, 2022 at 10:40

PakDragoonPakDragoon

1192 silver badges10 bronze badges

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

answered Sep 20, 2022 at 5:31

BNJBNJ

1662 silver badges11 bronze badges

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.

answered Feb 5 at 5:49

KVSVKKVSVK

594 bronze badges

Add the following in line in pg_hba.conf

hostnossl all all 0.0.0.0/0 trust

restart the server

answered Apr 11 at 16:40

Use SSL in the connection. This solves it instantly

answered Dec 15, 2022 at 19:44

AntonyAntony

1313 silver badges4 bronze badges

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’,…)

answered Oct 7, 2020 at 6:44

Источник

Authentication failures and related problems generally manifest themselves through error messages like the following:

FATAL:  no pg_hba.conf entry for host "123.123.123.123", user "andym", database "testdb"

This is what you are most likely to get if you succeed in contacting the server, but it does not want to talk to you. As the message suggests, the server refused the connection request because it found no matching entry in its pg_hba.conf configuration file.

FATAL:  password authentication failed for user "andym"

Messages like this indicate that you contacted the server, and it is willing to talk to you, but not until you pass the authorization method specified in the pg_hba.conf file. Check the password you are providing, or check your Kerberos or ident software if the complaint mentions one of those authentication types.

FATAL:  user "andym" does not exist

The indicated database user name was not found.

FATAL:  database "testdb" does not exist

The database you are trying to connect to does not exist. Note that if you do not specify a database name, it defaults to the database user name, which might or might not be the right thing.

Tip

The server log might contain more information about an authentication failure than is reported to the client. If you are confused about the reason for a failure, check the server log.

Источник

Introduction

This document describes how to resolve «FATAL: no pg_hba.conf entry for host» error when login to CloudCenter Manager Postgres standalone server with the use of PGAdmin tool.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

PostgreSQL
PGAdmin tool

Components Used

The information in this document is based on these software versions:

CloudCenter version 4.8.2
MGMTPOSTGRES_STANDALONE
Posrgres9.6

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, ensure that you understand the potential impact of any command.

Problem

When you try to connect the CloudCenter Postgres server with the use of pgAdmin, it fails with the «UNAUTHORIZED» error as shown in the image.

Solution

This authentication issue happens when you try to connect to the Postgres SQL server remotely other than the CloudCenter Manager server. In order to resolve this error, follow these steps:

1. Log in to Postgres SQL server with the use of ssh console.

2. cd to /var/lib/pgsql/9.6/data/.

3. Open pg_hba.conf file in an editor.

4. Add an entry of the host IP address from which you try to connect. You can input the entry of the host which you would like to provide access to as shown in the image.

5. Restart the postgres SQL server.

systemctl restart postgresql-9.6.service

6. Try again in order to connect with the use of pgAdmin tool and you should be able to connect without any errors as shown in the image.

Источник

I am trying to run a website sent to me but after doing so this error appeared

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host «4X.XXX.XX.XXX», user «userXXX», database «dbXXX», SSL off in C:xampphtdocsxmastoolindex.php on line 37

I found this answer that says that I just need to add an entry in the pg_hba.conf file for that particular user.

This is my pg_hba.conf file.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
local dbXXX userXXX md5
host    dbXXX  userXXX  XX.XXX.XXX.XXX           md5
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

but after doing so, the error still persists. I restarted my XAMPP server several times but nothing changes.

What do I need to change in pg_hba.conf?

mustaccio

24.3k20 gold badges54 silver badges69 bronze badges

asked Dec 1, 2014 at 10:54

Add or edit the following line in your postgresql.conf :

listen_addresses = '*'

Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

Restart Postgresql after adding this with service postgresql restart or the equivalent command for your setup. For brew, brew services restart postgresql

answered Dec 1, 2014 at 13:49

Jérôme RadixJérôme Radix

4,2411 gold badge11 silver badges3 bronze badges

This solution works for IPv4 / IPv6:

Edit pga_hba.conf File

Open up the pga_hba.conf file in your favourite editor:

[root@localhost ~]#  nano /var/lib/pgsql/data/pg_hba.conf

Append To pga_hba.conf File

Append the following lines to the end of the pga_hba.conf file:

host all all      ::1/128      md5
host all postgres 127.0.0.1/32 md5

Quit and save the editor of your preference.

Restart Service

Restart the postgresql service with the following command:

[root@localhost ~]# /etc/init.d/postgresql restart

John K. N.

16.6k10 gold badges47 silver badges106 bronze badges

answered Nov 27, 2015 at 21:18

Jose NobileJose Nobile

5234 silver badges7 bronze badges

The way I solved this was:

Added the line as below in pg_hba.conf:

hostnossl    all          all            0.0.0.0/0  trust

and this was modified in postgresql.conf, as shown:

listen_addresses = '*'

I had this instance running on a Centos 7.3 and Postgres 9.5 in a VM in Azure, given this was a POC (proof of concept) you won’t want to connect without SSL in your actual prod environment.

To connect to the instance I was using pgAdmin 4 on macOS Sierra.

joanolo

13.1k7 gold badges35 silver badges65 bronze badges

answered Jun 5, 2017 at 6:23

Fresh Postgres 9.5 install, Ubuntu.

The key was the local connection type, since psql uses domain socket connection.

pg_hba.conf

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
local all all md5
host  all  all 0.0.0.0/0 md5

answered Dec 5, 2017 at 23:02

willianptswillianpts

1511 silver badge3 bronze badges

Instructions for Debian users.

$ sudo su - postgres

Get the location of pg_hba.conf by quering the database:

$ psql -c "SHOW hba_file;"

              hba_file               
-------------------------------------
/etc/postgresql/11/main/pg_hba.conf
(1 row)

Open pg_hba.conf:

nano /etc/postgresql/11/main/pg_hba.conf

Add configuration where it says «Put your actual configuration here»:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

Logout to your user:

$ exit
logout

Restart your postgres server for changes to take effect:

$ sudo systemctl restart postgresql

answered May 18, 2019 at 23:45

Add the following line in the bottom of pg_hba.conf:

hostnossl all all 0.0.0.0/0 md5
Add/modify the line in postgresql.conf:

listen_addresses = '*'
MAKE SURE THAT the user that is connecting has a password: (Example connect user named postgres)

a. Run the following psql command with the postgres user account:

sudo -u postgres psql postgres

b. Set the password:

# password postgres

answered Nov 6, 2018 at 2:04

nguyênnguyên

2212 silver badges4 bronze badges

This below worked for me: (pg_hba.conf)

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only     
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0               trust

trust

Allow the connection unconditionally. This method allows anyone that
can connect to the PostgreSQL database server to login as any
PostgreSQL user they wish, without the need for a password or any
other authentication.

md5

Require the client to supply a double-MD5-hashed password for
authentication.

refer for more here

answered Mar 30, 2018 at 13:06

RavistmRavistm

1891 silver badge5 bronze badges

In my case I ran into this where I didn’t have access to edit any conf files on the server (.NET connecting to a managed db on DigitalOcean) so the other answers weren’t an option.

The host provided me a postgresql:// connection URL which had a ?sslmode= option on the end. I got the exact same error until I added "SSL Mode=Prefer;Trust Server Certificate=true;" to my translated .NET connectionString.

That may not be the optimal solution for me or for you, but I wanted to point out it’s possible that this is an issue with the connection string rather than the server config.

answered Oct 21, 2020 at 13:01

CrazyPyroCrazyPyro

2214 silver badges7 bronze badges

I had the same error when I tried to connect to a local database using an SSH tunnel. I solved it by changing the host name from localhost to 127.0.0.1.

answered Mar 24, 2018 at 2:02

FinesseFinesse

2113 silver badges3 bronze badges

In my case, I had to add the exact line as suggested by the error information. Cannot bypass it by adding «all» users with all IPs as rule. Now it is like:

PosgreSQL 10.5 on CentOS 7.

# IPv4 local connections:
host    all             all             127.0.0.1/32                    md5
host    <db_name>       postgres        <my_client_machine_ip>/32       md5

answered Oct 9, 2018 at 10:41

Find the correct configuration file:

su - postgres -c "psql -t -P format=unaligned -c 'show hba_file';"

Add the following at the end of file:

local all all peer

Then restart your PostgreSQL application:

/bin/systemctl restart postgresql*.service

answered Aug 26, 2019 at 14:24

Источник

I’ve had a docker swarm setup running for months now but started receiving the following error today:
FATAL: no pg_hba.conf entry for host "10.0.7.2", user "xxxx", database "xxxx", SSL off

This is how my docker-compose.yml looked like initially:

version: '3.6'

services:
  web:
    image: myimage
    networks:
      - frontend
      - backend
    depends_on:
      - db
    deploy:
      replicas: 3
    environment:
      DEBUG: '0'
      DATABASE_URL: 'postgres://xxxx@xxxx:5432/xxxx'
    command: uwsgi --ini etc/uwsgi.ini --http-socket 0.0.0.0:8001
    logging:
      driver: "json-file"
      options:
        max-size: "10M"
        max-file: "20"

  db:
    image: postgres:9.6.9
    networks:
      - backend
    ports:
      - "5432:5432"
    volumes:
      - ./data/postgres:/var/lib/postgresql/data
    deploy:
      placement:
        constraints: [node.role == manager]
    logging:
      driver: "json-file"
      options:
        max-size: "10M"
        max-file: "20"

networks:
  frontend:
  backend:
    driver: overlay
    attachable: true

After reading how I could potentially solve this I also added this to the db’s volume:
- ./pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf

with a modified pg_hba.conf where I added the following for testing purposes:
host all all 0.0.0.0/0 trust

Afterwords I removed the stack and deployed it again. I’ve checked the db container and pg_hba.conf reflected the changes but the issues was still there.

Источник