Обсуждение: strange behavior of pg_hba.conf file
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.
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.
Regards,
Atul
Am 22.11.23 um 17:21 schrieb Atul Kumar: > > > Since then I have started getting the below error: > > no pg_hba.conf entry for host "::1", user "postgres", database "postgres > > > > > What I am missing here, please suggest. > > that's sounds like an issue with IPv6. Do you use it? Disable it or add an entry for it. Regards, Andreas -- Andreas Kretschmer - currently still (garden leave) Technical Account Manager (TAM) www.enterprisedb.com
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.
On 11/22/23 08:21, Atul Kumar 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. > > Since then I have started getting the below error: > > no pg_hba.conf entry for host "::1", user "postgres", database "postgres The host is ::1 which IPv6 and your pg_hba.conf entry below is for IPv4. You need to add IPv6 line. > > > > > 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 <http://127.0.0.1/32> scram-sha-256 > > > > What I am missing here, please suggest. > > > > > Regards, > > Atul > -- Adrian Klaver adrian.klaver@aklaver.com
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 10:31 PM Atul Kumar <akumar14871@gmail.com> wrote:
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 error message is EXPLICIT, and DOES NOT LIE. Either someone removed the ::1 entry, or you're now using IPv6.
On Wed, Nov 22, 2023 at 12:03 PM Atul Kumar <akumar14871@gmail.com> wrote:
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
I am giving this command
psql -d postgres -U postgres -p 5432 -h localhost
Then only I get that error.
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>
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
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 11/22/23 9:55 AM, Andreas Kretschmer 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 ...
Yeah, you should take a look at:
/etc/hosts
In meantime include a line for IPv6 in pg_hba.conf. where the address would be:
::1/128
Please can you share any command for due diligence whether ip is resolved to ipv6 ?.
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
On 11/22/23 10:03 AM, Atul Kumar wrote:
Please can you share any command for due diligence whether ip is resolved to ipv6 ?.
This:
psql -d postgres -U postgres -p 5432 -h localhost
where pretty sure
/etc/hosts
is resolving localhost --> ::1
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
On 11/22/23 10:01 AM, Adrian Klaver wrote:
On 11/22/23 9:55 AM, Andreas Kretschmer 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 ...
Yeah, you should take a look at:
/etc/hosts
In meantime include a line for IPv6 in pg_hba.conf. where the address would be:
::1/128
Or you could change
host all postgres 127.0.0.1/32 scram-sha-256
to
host all postgres localhost scram-sha-256
On Wed, 2023-11-22 at 23:33 +0530, Atul Kumar wrote: > Please can you share any command for due diligence whether ip is resolved to ipv6 ?. Not a lot of diligence is due to figure out that you can use ping localhost Yours, Laurenz Albe