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(dot)albe(at)cybertec(dot)at>
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
>
>