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