strange behavior of pg_hba.conf file

The entries that I changed were to replace the md5 with scram-sha-256 and
remove unnecessary remote IPs.

But it has nothing to do with connecting the server locally with "psql -d
postgres -U postgres -h localhost"

But when I try to connect it locally I get this error. So it is related to
local connections only and when I pass the hostname or ip of the server it
works fine without any issue.

Regards.

On Wed, Nov 22, 2023 at 9:55 PM Ron Johnson <ronljohnsonjr@gmail.com>
wrote:

On Wed, Nov 22, 2023 at 11:22 AM Atul Kumar <akumar14871@gmail.com>
wrote:

Hi,

I have postgres 12 running in centos 7, recently I changed the
authentication of entries of pg_hba.conf to scram-sh-256 for localhost.

I think you changed something else, at the same time.

Since then I have started getting the below error:

no pg_hba.conf entry for host "::1", user "postgres", database "postgres

The entry of pg_hba.conf is like below:

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all
scram-sha-256

# IPv4 local connections:

host all postgres 127.0.0.1/32
scram-sha-256

What I am missing here, please suggest.

A definition for host "::1", user "postgres", database "postgres". It's
right there in the error message.

The entries that I changed were to replace the md5 with scram-sha-256 and
remove unnecessary remote IPs.

But it has nothing to do with connecting the server locally with "psql -d
postgres -U postgres -h localhost"

But when I try to connect it locally I get this error. So it is related to
local connections only and when I pass the hostname or ip of the server it
works fine without any issue.

The entry of pg_hba.conf is like below:

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all
scram-sha-256

# IPv4 local connections:

host all postgres 127.0.0.1/32
scram-sha-256

What I am missing here, please suggest.

A definition for host "::1", user "postgres", database "postgres".
It's right there in the error message.

On 11/22/23 09:03, Atul Kumar wrote:

The entries that I changed were to replace the md5 with scram-sha-256
and remove unnecessary remote IPs.

FYI from:

https://www.postgresql.org/docs/current/auth-password.html

md5

The method md5 uses a custom less secure challenge-response
mechanism. It prevents password sniffing and avoids storing passwords on
the server in plain text but provides no protection if an attacker
manages to steal the password hash from the server. Also, the MD5 hash
algorithm is nowadays no longer considered secure against determined
attacks.

The md5 method cannot be used with the db_user_namespace feature.

To ease transition from the md5 method to the newer SCRAM method,
if md5 is specified as a method in pg_hba.conf but the user's password
on the server is encrypted for SCRAM (see below), then SCRAM-based
authentication will automatically be chosen instead.

But it has nothing to do with connecting the server locally with "psql
-d postgres -U postgres -h localhost"

The error:

no pg_hba.conf entry for host "::1", user "postgres", database "postgres

says it does and the error is correct as you do not have an IPv6 entry
for localhost in pg_hba.conf. At least in the snippet you showed us.

But when I try to connect it locally I get this error. So it is related

When you say connect locally do you mean to localhost or to local(socket)?

to local connections only and when I pass the hostname or ip of the
server it works fine without any issue.

Regards.

--
Adrian Klaver
adrian.klaver@aklaver.com

Am 22.11.23 um 18:44 schrieb Atul Kumar:

I am giving this command
psql -d postgres -U postgres -p 5432 -h localhost
Then only I get that error.

so localhost resolved to an IPv6 - address ...

but when I pass ip or hostname of the local server then I don't get
such error message
1. psql -d postgres -U postgres -p 5432 -h <ip of local server>
2. psql -d postgres -U postgres -p 5432 -h <hostname of local server>

resolves to an IPv4 - address. you can see the difference?

localhost != iv4-address != hostname with ipv4 address

Andreas

I don;t get that error while using the above two commands.

Regards.

On Wed, Nov 22, 2023 at 10:45 PM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 11/22/23 09:03, Atul Kumar wrote:

The entries that I changed were to replace the md5 with

scram-sha-256

and remove unnecessary remote IPs.

FYI from:

https://www.postgresql.org/docs/current/auth-password.html

md5

The method md5 uses a custom less secure challenge-response
mechanism. It prevents password sniffing and avoids storing
passwords on
the server in plain text but provides no protection if an attacker
manages to steal the password hash from the server. Also, the MD5
hash
algorithm is nowadays no longer considered secure against determined
attacks.

The md5 method cannot be used with the db_user_namespace

feature.

To ease transition from the md5 method to the newer SCRAM
method,
if md5 is specified as a method in pg_hba.conf but the user's
password
on the server is encrypted for SCRAM (see below), then SCRAM-based
authentication will automatically be chosen instead.

But it has nothing to do with connecting the server locally with

"psql

-d postgres -U postgres -h localhost"

The error:

no pg_hba.conf entry for host "::1", user "postgres", database
"postgres

says it does and the error is correct as you do not have an IPv6
entry
for localhost in pg_hba.conf. At least in the snippet you showed us.

But when I try to connect it locally I get this error. So it is

related

When you say connect locally do you mean to localhost or to
local(socket)?

to local connections only and when I pass the hostname or ip of the
server it works fine without any issue.

Regards.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com

On Wed, Nov 22, 2023 at 11:25 PM Andreas Kretschmer
<andreas@a-kretschmer.de> wrote:

Am 22.11.23 um 18:44 schrieb Atul Kumar:

I am giving this command
psql -d postgres -U postgres -p 5432 -h localhost
Then only I get that error.

so localhost resolved to an IPv6 - address ...

but when I  pass ip or hostname of the local server then I don't

get

such error message
1. psql -d postgres -U postgres -p 5432 -h <ip of local server>
2. psql -d postgres -U postgres -p 5432 -h <hostname of local

server>

resolves to an IPv4 - address. you can see the difference?

localhost != iv4-address != hostname with ipv4 address

Andreas

I don;t get that error while using the above two commands.

Regards.

On Wed, Nov 22, 2023 at 10:45 PM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

     On 11/22/23 09:03, Atul Kumar wrote:
     > The entries that I changed were to replace the md5 with
     scram-sha-256
     > and remove unnecessary remote IPs.

     FYI from:

https://www.postgresql.org/docs/current/auth-password.html

     md5

          The method md5 uses a custom less secure challenge-response
     mechanism. It prevents password sniffing and avoids storing
     passwords on
     the server in plain text but provides no protection if an

attacker

     manages to steal the password hash from the server. Also,

the MD5

     hash
     algorithm is nowadays no longer considered secure against

determined

     attacks.

          The md5 method cannot be used with the

db_user_namespace feature.

          To ease transition from the md5 method to the newer SCRAM
     method,
     if md5 is specified as a method in pg_hba.conf but the user's
     password
     on the server is encrypted for SCRAM (see below), then

SCRAM-based

     authentication will automatically be chosen instead.

     >
     > But it has nothing to do with connecting the server

locally with

     "psql
     > -d postgres -U postgres -h localhost"

     The error:

     no pg_hba.conf entry for host "::1", user "postgres", database
     "postgres

     says it does and the error is correct as you do not have an IPv6
     entry
     for localhost in pg_hba.conf. At least in the snippet you

showed us.

     >
     > But when I try to connect it locally I get this error. So

it is

     related

     When you say connect locally do you mean to localhost or to
     local(socket)?

     > to local connections only and when I pass the hostname or

ip of the

     > server it works fine without any issue.
     >
     >
     > Regards.
     >

     --
     Adrian Klaver
adrian.klaver@aklaver.com

--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com <http://www.enterprisedb.com&gt;