Search Postgresql Archives

Re: How to debug authentication issues in Postgres

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx> wrote:
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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux