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