How to debug authentication issues in Postgres

Started by Hemil Ruparelover 5 years ago28 messagesgeneral

hemilruparel2002@gmail.com

over 5 years ago

I have a remote database which I can connect to using psql command line
tool as well as PgAdmin4. But I would really like to use DataGrip. But
whenever I try to connect, it gives me fatal: password authentication
failed and prompts me for another password. I raised an issue in DataGrip
and I was told there is an issue in my database configuration.

Here is my pg_hba.conf:
```
# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32
scram-sha-256
# IPv4 connections from internet
host database user 0.0.0.0/0 scram-sha-256
host database user 0.0.0.0/0 md5
host database user 0.0.0.0/0 password
# IPv6 local connections:
host all all ::1/128
scram-sha-256
# IPv6 connections from internet:
host database user ::0/0 scram-sha-256
host database user ::0/0 md5
host database user ::0/0 password
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all
```

Since I know a Java and I know Idea uses java, so I wrote this small
snippet to try to connect to my server using JDBC:
```java
public class Test {
public static void main(String[] args) throws SQLException {
Connection connection = DriverManager.getConnection(
"jdbc:postgresql://url/database",
"user",
"password"
);

try (connection) {
Statement statement = connection.createStatement();
statement.execute("select version()");
}
}
}
```
And it failed with the same error

Laurenz Albe

laurenz.albe@cybertec.at

over 5 years ago

In reply to: Hemil Ruparel (#1)

Re: How to debug authentication issues in Postgres

On Fri, 2020-11-27 at 12:44 +0530, Hemil Ruparel wrote:

I have a remote database which I can connect to using psql command line tool as well as PgAdmin4. But I would really like to use DataGrip. But whenever I try to connect, it gives me fatal: password
authentication failed and prompts me for another password. I raised an issue in DataGrip and I was told there is an issue in my database configuration.

Here is my pg_hba.conf:
```
# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv4 connections from internet
host database user 0.0.0.0/0 scram-sha-256
host database user 0.0.0.0/0 md5
host database user 0.0.0.0/0 password
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# IPv6 connections from internet:
host database user ::0/0 scram-sha-256
host database user ::0/0 md5
host database user ::0/0 password
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all
```

Since I know a Java and I know Idea uses java, so I wrote this small snippet to try to connect to my server using JDBC:
```java
public class Test {
public static void main(String[] args) throws SQLException {
Connection connection = DriverManager.getConnection(
"jdbc:postgresql://url/database",
"user",
"password"
);

try (connection) {
Statement statement = connection.createStatement();
statement.execute("select version()");
}
}
}
```
And it failed with the same error

You should consult the PostgreSQL log file.

For one, the last line "local replication all" is syntactically wrong, which
would lead to an error message in the log and cause the file not to take effect.
It will also prevent PostgreSQL from starting if you restart it.

The second reason to look into the log file (once you have fixed pg_hba.conf) is
that it will give you more details to error message. The client gets less information,
because such information could be useful to an attacker.
I'd expect that you get at least the line in pg_hba.conf that was used, which will
ease debugging for you.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Laurenz Albe (#2)

Re: How to debug authentication issues in Postgres

I have restarted postgres quite a few times to try making configuration
changes and it is always back up. I don't know how. Feels weird to me. I
didn't add the line "local replication all". It was there by default

On Fri, Nov 27, 2020 at 1:24 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

Show quoted text

On Fri, 2020-11-27 at 12:44 +0530, Hemil Ruparel wrote:

I have a remote database which I can connect to using psql command line

tool as well as PgAdmin4. But I would really like to use DataGrip. But
whenever I try to connect, it gives me fatal: password

authentication failed and prompts me for another password. I raised an

issue in DataGrip and I was told there is an issue in my database
configuration.

Here is my pg_hba.conf:
```
# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32

scram-sha-256

# IPv4 connections from internet
host database user 0.0.0.0/0 scram-sha-256
host database user 0.0.0.0/0 md5
host database user 0.0.0.0/0 password
# IPv6 local connections:
host all all ::1/128

scram-sha-256

# IPv6 connections from internet:
host database user ::0/0 scram-sha-256
host database user ::0/0 md5
host database user ::0/0 password
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all
```

Since I know a Java and I know Idea uses java, so I wrote this small

snippet to try to connect to my server using JDBC:

```java
public class Test {
public static void main(String[] args) throws SQLException {
Connection connection = DriverManager.getConnection(
"jdbc:postgresql://url/database",
"user",
"password"
);

try (connection) {
Statement statement = connection.createStatement();
statement.execute("select version()");
}
}
}
```
And it failed with the same error

You should consult the PostgreSQL log file.

For one, the last line "local replication all" is syntactically
wrong, which
would lead to an error message in the log and cause the file not to take
effect.
It will also prevent PostgreSQL from starting if you restart it.

The second reason to look into the log file (once you have fixed
pg_hba.conf) is
that it will give you more details to error message. The client gets less
information,
because such information could be useful to an attacker.
I'd expect that you get at least the line in pg_hba.conf that was used,
which will
ease debugging for you.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Laurenz Albe

laurenz.albe@cybertec.at

over 5 years ago

In reply to: Hemil Ruparel (#3)

Re: How to debug authentication issues in Postgres

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making configuration changes and it
is always back up. I don't know how. Feels weird to me. I didn't add the line
"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Laurenz Albe (#4)

Re: How to debug authentication issues in Postgres

Sorry. This was the replication section:
local replication all peer
host replication all 127.0.0.1/32
scram-sha-256
host replication all ::1/128
scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

Show quoted text

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making configuration

changes and it

is always back up. I don't know how. Feels weird to me. I didn't add

the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Hemil Ruparel (#5)

Re: How to debug authentication issues in Postgres

The log says:

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 scram-sha-256"

I can't understand where is the problem as both psql and pgadmin connect
without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel <hemilruparel2002@gmail.com>
wrote:

Show quoted text

Sorry. This was the replication section:
local replication all peer
host replication all 127.0.0.1/32
scram-sha-256
host replication all ::1/128
scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making configuration

changes and it

is always back up. I don't know how. Feels weird to me. I didn't add

the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Zwettler Markus (OIZ)

Markus.Zwettler@zuerich.ch

over 5 years ago

In reply to: Hemil Ruparel (#6)

AW: How to debug authentication issues in Postgres

Did you correctly upgrade your whole environment to scram-sha-256?

<quote>
To upgrade an existing installation from md5 to scram-sha-256, after having ensured that all client libraries in use are new enough to support SCRAM, set password_encryption = 'scram-sha-256' in postgresql.conf, make all users set new passwords, and change the authentication method specifications in pg_hba.conf to scram-sha-256.
</quote>

-Markus

Von: Hemil Ruparel <hemilruparel2002@gmail.com>
Gesendet: Freitag, 27. November 2020 09:38
An: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Betreff: Re: How to debug authentication issues in Postgres

The log says:

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user password 0.0.0.0/0<http://0.0.0.0/0> scram-sha-256"

I can't understand where is the problem as both psql and pgadmin connect without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel <hemilruparel2002@gmail.com<mailto:hemilruparel2002@gmail.com>> wrote:
Sorry. This was the replication section:
local replication all peer
host replication all 127.0.0.1/32<http://127.0.0.1/32> scram-sha-256
host replication all ::1/128 scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe <laurenz.albe@cybertec.at<mailto:laurenz.albe@cybertec.at>> wrote:
On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making configuration changes and it
is always back up. I don't know how. Feels weird to me. I didn't add the line
"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32<http://127.0.0.1/32> trust
host replication all ::1/128 trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Zwettler Markus (OIZ) (#7)

Re: How to debug authentication issues in Postgres

I don't quite get what you mean by upgrading to scram-sha256. I installed
postgres 13. I haven't upgraded anything yet.

On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ) <
Markus.Zwettler@zuerich.ch> wrote:

Show quoted text

Did you correctly upgrade your whole environment to scram-sha-256?

<quote>
To upgrade an existing installation from md5 to scram-sha-256, after
having ensured that all client libraries in use are new enough to support
SCRAM, set password_encryption = 'scram-sha-256' in postgresql.conf, make
all users set new passwords, and change the authentication method
specifications in pg_hba.conf to scram-sha-256.

</quote>

-Markus

*Von:* Hemil Ruparel <hemilruparel2002@gmail.com>
*Gesendet:* Freitag, 27. November 2020 09:38
*An:* Laurenz Albe <laurenz.albe@cybertec.at>
*Cc:* pgsql-generallists.postgresql.org <
pgsql-general@lists.postgresql.org>
*Betreff:* Re: How to debug authentication issues in Postgres

The log says:

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 scram-sha-256"

I can't understand where is the problem as both psql and pgadmin connect
without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel <hemilruparel2002@gmail.com>
wrote:

Sorry. This was the replication section:

local replication all peer
host replication all 127.0.0.1/32
scram-sha-256
host replication all ::1/128
scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making configuration

changes and it

is always back up. I don't know how. Feels weird to me. I didn't add

the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Adrian Klaver

adrian.klaver@aklaver.com

over 5 years ago

In reply to: Hemil Ruparel (#8)

Re: How to debug authentication issues in Postgres

On 11/27/20 7:01 AM, Hemil Ruparel wrote:

I don't quite get what you mean by upgrading to scram-sha256. I
installed postgres 13. I haven't upgraded anything yet.

In postgresql.conf see what password_encryption has been set to. If it
is 'scram-sha-256` then it has been upgraded.

On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ)
<Markus.Zwettler@zuerich.ch <mailto:Markus.Zwettler@zuerich.ch>> wrote:

Did you correctly upgrade your whole environment to scram-sha-256?____

__ __

__ __

<quote>
To upgrade an existing installation from |md5|to |scram-sha-256|,
after having ensured that all client libraries in use are new enough
to support SCRAM, set |password_encryption = 'scram-sha-256'|in
|postgresql.conf|, make all users set new passwords, and change the
authentication method specifications in |pg_hba.conf|to
|scram-sha-256|.____

</quote>____

__ __

__ __

-Markus____

__ __

__ __

__ __

*Von:*Hemil Ruparel <hemilruparel2002@gmail.com
<mailto:hemilruparel2002@gmail.com>>
*Gesendet:* Freitag, 27. November 2020 09:38
*An:* Laurenz Albe <laurenz.albe@cybertec.at
<mailto:laurenz.albe@cybertec.at>>
*Cc:* pgsql-generallists.postgresql.org
<http://pgsql-generallists.postgresql.org>
<pgsql-general@lists.postgresql.org
<mailto:pgsql-general@lists.postgresql.org>>
*Betreff:* Re: How to debug authentication issues in Postgres____

__ __

The log says:____

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 <http://0.0.0.0/0>
scram-sha-256"____

__ __

I can't understand where is the problem as both psql and pgadmin
connect without problems using the same password____

__ __

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
<hemilruparel2002@gmail.com <mailto:hemilruparel2002@gmail.com>>
wrote:____

Sorry. This was the replication section:____

local replication all peer
host replication all 127.0.0.1/32 <http://127.0.0.1/32>
scram-sha-256
host replication all ::1/128
scram-sha-256____

__ __

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
<laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>>
wrote:____

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making

configuration changes and it

is always back up. I don't know how. Feels weird to me.

I didn't add the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user
with the
# replication privilege.
local replication all
trust
host replication all 127.0.0.1/32
<http://127.0.0.1/32> trust
host replication all ::1/128
trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
<https://www.cybertec-postgresql.com>____

--
Adrian Klaver
adrian.klaver@aklaver.com

#10

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Adrian Klaver (#9)

Re: How to debug authentication issues in Postgres

The database has been upgraded

On Fri, Nov 27, 2020 at 8:41 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Show quoted text

On 11/27/20 7:01 AM, Hemil Ruparel wrote:

I don't quite get what you mean by upgrading to scram-sha256. I
installed postgres 13. I haven't upgraded anything yet.

In postgresql.conf see what password_encryption has been set to. If it
is 'scram-sha-256` then it has been upgraded.

On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ)
<Markus.Zwettler@zuerich.ch <mailto:Markus.Zwettler@zuerich.ch>> wrote:

Did you correctly upgrade your whole environment to

scram-sha-256?____

__ __

__ __

<quote>
To upgrade an existing installation from |md5|to |scram-sha-256|,
after having ensured that all client libraries in use are new enough
to support SCRAM, set |password_encryption = 'scram-sha-256'|in
|postgresql.conf|, make all users set new passwords, and change the
authentication method specifications in |pg_hba.conf|to
|scram-sha-256|.____

</quote>____

__ __

__ __

-Markus____

__ __

__ __

__ __

*Von:*Hemil Ruparel <hemilruparel2002@gmail.com
<mailto:hemilruparel2002@gmail.com>>
*Gesendet:* Freitag, 27. November 2020 09:38
*An:* Laurenz Albe <laurenz.albe@cybertec.at
<mailto:laurenz.albe@cybertec.at>>
*Cc:* pgsql-generallists.postgresql.org
<http://pgsql-generallists.postgresql.org>
<pgsql-general@lists.postgresql.org
<mailto:pgsql-general@lists.postgresql.org>>
*Betreff:* Re: How to debug authentication issues in Postgres____

__ __

The log says:____

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 <http://0.0.0.0/0>
scram-sha-256"____

__ __

I can't understand where is the problem as both psql and pgadmin
connect without problems using the same password____

__ __

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
<hemilruparel2002@gmail.com <mailto:hemilruparel2002@gmail.com>>
wrote:____

Sorry. This was the replication section:____

local replication all

peer

host replication all 127.0.0.1/32 <http://127.0.0.1/32>
scram-sha-256
host replication all ::1/128
scram-sha-256____

__ __

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
<laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>>
wrote:____

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making

configuration changes and it

is always back up. I don't know how. Feels weird to me.

I didn't add the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user
with the
# replication privilege.
local replication all
trust
host replication all 127.0.0.1/32
<http://127.0.0.1/32> trust
host replication all ::1/128
trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
<https://www.cybertec-postgresql.com>____

--
Adrian Klaver
adrian.klaver@aklaver.com

#11

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Hemil Ruparel (#10)

Re: How to debug authentication issues in Postgres

When I try to connect to the database, the log says:

FATAL: password authentication failed for user "user"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 scram-sha-256"

So I think the client is using scram-sha-256

On Fri, Nov 27, 2020 at 8:45 PM Hemil Ruparel <hemilruparel2002@gmail.com>
wrote:

Show quoted text

The database has been upgraded

On Fri, Nov 27, 2020 at 8:41 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 11/27/20 7:01 AM, Hemil Ruparel wrote:

I don't quite get what you mean by upgrading to scram-sha256. I
installed postgres 13. I haven't upgraded anything yet.

In postgresql.conf see what password_encryption has been set to. If it
is 'scram-sha-256` then it has been upgraded.

On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ)
<Markus.Zwettler@zuerich.ch <mailto:Markus.Zwettler@zuerich.ch>> wrote:

Did you correctly upgrade your whole environment to

scram-sha-256?____

__ __

__ __

<quote>
To upgrade an existing installation from |md5|to |scram-sha-256|,
after having ensured that all client libraries in use are new enough
to support SCRAM, set |password_encryption = 'scram-sha-256'|in
|postgresql.conf|, make all users set new passwords, and change the
authentication method specifications in |pg_hba.conf|to
|scram-sha-256|.____

</quote>____

__ __

__ __

-Markus____

__ __

__ __

__ __

*Von:*Hemil Ruparel <hemilruparel2002@gmail.com
<mailto:hemilruparel2002@gmail.com>>
*Gesendet:* Freitag, 27. November 2020 09:38
*An:* Laurenz Albe <laurenz.albe@cybertec.at
<mailto:laurenz.albe@cybertec.at>>
*Cc:* pgsql-generallists.postgresql.org
<http://pgsql-generallists.postgresql.org>
<pgsql-general@lists.postgresql.org
<mailto:pgsql-general@lists.postgresql.org>>
*Betreff:* Re: How to debug authentication issues in Postgres____

__ __

The log says:____

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 <http://0.0.0.0/0>
scram-sha-256"____

__ __

I can't understand where is the problem as both psql and pgadmin
connect without problems using the same password____

__ __

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
<hemilruparel2002@gmail.com <mailto:hemilruparel2002@gmail.com>>
wrote:____

Sorry. This was the replication section:____

local replication all

peer

host replication all 127.0.0.1/32 <http://127.0.0.1/32>

scram-sha-256
host replication all ::1/128
scram-sha-256____

__ __

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
<laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>>
wrote:____

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making

configuration changes and it

is always back up. I don't know how. Feels weird to me.

I didn't add the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user
with the
# replication privilege.
local replication all
trust
host replication all 127.0.0.1/32
<http://127.0.0.1/32> trust
host replication all ::1/128
trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
<https://www.cybertec-postgresql.com>____

--
Adrian Klaver
adrian.klaver@aklaver.com

#12

Tom Lane

tgl@sss.pgh.pa.us

over 5 years ago

In reply to: Hemil Ruparel (#11)

Re: How to debug authentication issues in Postgres

Hemil Ruparel <hemilruparel2002@gmail.com> writes:

When I try to connect to the database, the log says:

FATAL: password authentication failed for user "user"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 scram-sha-256"

So I think the client is using scram-sha-256

No, what that says is that the server is going to insist on scram-sha-256.
If the client can't handle SCRAM, then a failure would be expected.

regards, tom lane

#13

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Tom Lane (#12)

Re: How to debug authentication issues in Postgres

Thanks for the clarification. According to this page,
https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0,
scram support was added in JDBC driver 42.2.0. I am on 42.2.18. And using
the java code mentioned above, I still get the same error.

On Fri, Nov 27, 2020 at 9:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Hemil Ruparel <hemilruparel2002@gmail.com> writes:

When I try to connect to the database, the log says:

FATAL: password authentication failed for user "user"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 scram-sha-256"

So I think the client is using scram-sha-256

No, what that says is that the server is going to insist on scram-sha-256.
If the client can't handle SCRAM, then a failure would be expected.

regards, tom lane

#14

Tom Lane

tgl@sss.pgh.pa.us

over 5 years ago

In reply to: Hemil Ruparel (#13)

Re: How to debug authentication issues in Postgres

Hemil Ruparel <hemilruparel2002@gmail.com> writes:

Thanks for the clarification. According to this page,
https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0,
scram support was added in JDBC driver 42.2.0. I am on 42.2.18. And using
the java code mentioned above, I still get the same error.

If you back off the pg_hba setting to md5, does it work?

regards, tom lane

#15

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Tom Lane (#14)

Re: How to debug authentication issues in Postgres

I will try that. I do not have access to the computer right now

On Fri 27 Nov, 2020, 9:25 PM Tom Lane, <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Hemil Ruparel <hemilruparel2002@gmail.com> writes:

Thanks for the clarification. According to this page,
https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0,
scram support was added in JDBC driver 42.2.0. I am on 42.2.18. And using
the java code mentioned above, I still get the same error.

If you back off the pg_hba setting to md5, does it work?

regards, tom lane

#16

Adrian Klaver

adrian.klaver@aklaver.com

over 5 years ago

In reply to: Hemil Ruparel (#10)

Re: How to debug authentication issues in Postgres

On 11/27/20 7:15 AM, Hemil Ruparel wrote:

The database has been upgraded

Just to be clear the postgresql.conf file has:

password_encryption = scram-sha-256

set correct?

On Fri, Nov 27, 2020 at 8:41 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 11/27/20 7:01 AM, Hemil Ruparel wrote:

I don't quite get what you mean by upgrading to scram-sha256. I
installed postgres 13. I haven't upgraded anything yet.

In postgresql.conf see what password_encryption has been set to. If it
is 'scram-sha-256` then it has been upgraded.

On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ)
<Markus.Zwettler@zuerich.ch <mailto:Markus.Zwettler@zuerich.ch>

<mailto:Markus.Zwettler@zuerich.ch
<mailto:Markus.Zwettler@zuerich.ch>>> wrote:

Did you correctly upgrade your whole environment to

scram-sha-256?____

__ __

__ __

<quote>
To upgrade an existing installation from |md5|to |scram-sha-256|,
after having ensured that all client libraries in use are new

enough

to support SCRAM, set |password_encryption = 'scram-sha-256'|in
|postgresql.conf|, make all users set new passwords, and

change the

authentication method specifications in |pg_hba.conf|to
|scram-sha-256|.____

</quote>____

__ __

__ __

-Markus____

__ __

__ __

__ __

*Von:*Hemil Ruparel <hemilruparel2002@gmail.com

<mailto:hemilruparel2002@gmail.com>

<mailto:hemilruparel2002@gmail.com

<mailto:hemilruparel2002@gmail.com>>>

*Gesendet:* Freitag, 27. November 2020 09:38
*An:* Laurenz Albe <laurenz.albe@cybertec.at

<mailto:laurenz.albe@cybertec.at>

<mailto:laurenz.albe@cybertec.at

<mailto:laurenz.albe@cybertec.at>>>

*Cc:* pgsql-generallists.postgresql.org

<http://pgsql-generallists.postgresql.org>

<http://pgsql-generallists.postgresql.org

<http://pgsql-generallists.postgresql.org>>

<pgsql-general@lists.postgresql.org

<mailto:pgsql-general@lists.postgresql.org>

<mailto:pgsql-general@lists.postgresql.org

<mailto:pgsql-general@lists.postgresql.org>>>

*Betreff:* Re: How to debug authentication issues in Postgres____

__ __

The log says:____

> FATAL: password authentication failed for user "centos"
> DETAIL: Connection matched pg_hba.conf line 88: "host

user

password 0.0.0.0/0 <http://0.0.0.0/0> <http://0.0.0.0/0

<http://0.0.0.0/0>>

scram-sha-256"____

__ __

I can't understand where is the problem as both psql and pgadmin
connect without problems using the same password____

__ __

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
<hemilruparel2002@gmail.com

<mailto:hemilruparel2002@gmail.com>
<mailto:hemilruparel2002@gmail.com <mailto:hemilruparel2002@gmail.com>>>

wrote:____

Sorry. This was the replication section:____

local replication all

peer

host replication all 127.0.0.1/32

<http://127.0.0.1/32> <http://127.0.0.1/32 <http://127.0.0.1/32>>

scram-sha-256
host replication all ::1/128
scram-sha-256____

__ __

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
<laurenz.albe@cybertec.at

<mailto:laurenz.albe@cybertec.at> <mailto:laurenz.albe@cybertec.at
<mailto:laurenz.albe@cybertec.at>>>

wrote:____

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
> I have restarted postgres quite a few times to try

making

configuration changes and it
> is always back up. I don't know how. Feels weird

to me.

I didn't add the line
> "local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user
with the
# replication privilege.
local replication all
trust
host replication all 127.0.0.1/32

<http://127.0.0.1/32>

<http://127.0.0.1/32 <http://127.0.0.1/32>>

trust

host replication all ::1/128
trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

<https://www.cybertec-postgresql.com>

<https://www.cybertec-postgresql.com

<https://www.cybertec-postgresql.com>>____

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

#17

Adrian Klaver

adrian.klaver@aklaver.com

over 5 years ago

In reply to: Hemil Ruparel (#6)

Re: How to debug authentication issues in Postgres

On 11/27/20 12:37 AM, Hemil Ruparel wrote:

The log says:

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 <http://0.0.0.0/0> scram-sha-256"

To me that looks like a strange line for pg_hba.conf and I don't see it
in the pg_hba.conf file you sent earlier.

What is line 88 in your pg_hba.conf?

I can't understand where is the problem as both psql and pgadmin connect
without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
<hemilruparel2002@gmail.com <mailto:hemilruparel2002@gmail.com>> wrote:

Sorry. This was the replication section:
local replication all peer
host replication all 127.0.0.1/32 <http://127.0.0.1/32>
scram-sha-256
host replication all ::1/128
scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
<laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>> wrote:

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making

configuration changes and it

is always back up. I don't know how. Feels weird to me. I

didn't add the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all
trust
host replication all 127.0.0.1/32 <http://127.0.0.1/32>
trust
host replication all ::1/128
trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
<https://www.cybertec-postgresql.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

#18

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Adrian Klaver (#17)

Re: How to debug authentication issues in Postgres

Yes. Password encryption is set to scram-sha-256.

On Fri, Nov 27, 2020 at 10:36 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Show quoted text

On 11/27/20 12:37 AM, Hemil Ruparel wrote:

The log says:

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 <http://0.0.0.0/0> scram-sha-256"

To me that looks like a strange line for pg_hba.conf and I don't see it
in the pg_hba.conf file you sent earlier.

What is line 88 in your pg_hba.conf?

I can't understand where is the problem as both psql and pgadmin connect
without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
<hemilruparel2002@gmail.com <mailto:hemilruparel2002@gmail.com>> wrote:

Sorry. This was the replication section:
local replication all peer
host replication all 127.0.0.1/32 <http://127.0.0.1/32>
scram-sha-256
host replication all ::1/128
scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
<laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>> wrote:

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making

configuration changes and it

is always back up. I don't know how. Feels weird to me. I

didn't add the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with

the

# replication privilege.
local replication all
trust
host replication all 127.0.0.1/32 <http://127.0.0.1/32>
trust
host replication all ::1/128
trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
<https://www.cybertec-postgresql.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

#19

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Hemil Ruparel (#18)

Re: How to debug authentication issues in Postgres

I commented out scram-sha-256 lines for IPv4 and IPv6. I still got
authentication failure. The log output now says:
FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 89: "host database
user 0.0.0.0/0 md5"

On Sat, Nov 28, 2020 at 7:34 PM Hemil Ruparel <hemilruparel2002@gmail.com>
wrote:

Show quoted text

Yes. Password encryption is set to scram-sha-256.

On Fri, Nov 27, 2020 at 10:36 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 11/27/20 12:37 AM, Hemil Ruparel wrote:

The log says:

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 <http://0.0.0.0/0> scram-sha-256"

To me that looks like a strange line for pg_hba.conf and I don't see it
in the pg_hba.conf file you sent earlier.

What is line 88 in your pg_hba.conf?

I can't understand where is the problem as both psql and pgadmin

connect

without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
<hemilruparel2002@gmail.com <mailto:hemilruparel2002@gmail.com>> wrote:

Sorry. This was the replication section:
local replication all peer
host replication all 127.0.0.1/32 <http://127.0.0.1/32>

scram-sha-256
host replication all ::1/128
scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
<laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>> wrote:

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making

configuration changes and it

is always back up. I don't know how. Feels weird to me. I

didn't add the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with

the

# replication privilege.
local replication all
trust
host replication all 127.0.0.1/32 <http://127.0.0.1/32>
trust
host replication all ::1/128
trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
<https://www.cybertec-postgresql.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

#20

Hemil Ruparel

hemilruparel2002@gmail.com

over 5 years ago

In reply to: Hemil Ruparel (#19)

Re: How to debug authentication issues in Postgres

Line 88 is this line: host database user 0.0.0.0/0
scram-sha-256.

I might have forgotten to change one of the names in the earlier mails.

On Sat, Nov 28, 2020 at 7:38 PM Hemil Ruparel <hemilruparel2002@gmail.com>
wrote:

Show quoted text

I commented out scram-sha-256 lines for IPv4 and IPv6. I still got
authentication failure. The log output now says:
FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 89: "host database
user 0.0.0.0/0 md5"

On Sat, Nov 28, 2020 at 7:34 PM Hemil Ruparel <hemilruparel2002@gmail.com>
wrote:

Yes. Password encryption is set to scram-sha-256.

On Fri, Nov 27, 2020 at 10:36 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 11/27/20 12:37 AM, Hemil Ruparel wrote:

The log says:

FATAL: password authentication failed for user "centos"
DETAIL: Connection matched pg_hba.conf line 88: "host user

password 0.0.0.0/0 <http://0.0.0.0/0> scram-sha-256"

To me that looks like a strange line for pg_hba.conf and I don't see it
in the pg_hba.conf file you sent earlier.

What is line 88 in your pg_hba.conf?

I can't understand where is the problem as both psql and pgadmin

connect

without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
<hemilruparel2002@gmail.com <mailto:hemilruparel2002@gmail.com>>

wrote:

Sorry. This was the replication section:
local replication all

peer

host replication all 127.0.0.1/32 <http://127.0.0.1/32>

scram-sha-256
host replication all ::1/128
scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
<laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>>

wrote:

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:

I have restarted postgres quite a few times to try making

configuration changes and it

is always back up. I don't know how. Feels weird to me. I

didn't add the line

"local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with

the

# replication privilege.
local replication all
trust
host replication all 127.0.0.1/32 <http://127.0.0.1/32>

trust
host replication all ::1/128
trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
<https://www.cybertec-postgresql.com>

--
Adrian Klaver
adrian.klaver@aklaver.com