Re: postgres user password reset problem

Mike Dewhirst <miked(at)dewhirst(dot)com(dot)au> writes:
> I have assigned a Linux password to the postgres user and I can sudo or
> su but psql is demanding its own password for its postgres user. The log
> says ...

> 2020-06-12 14:03:00.019 AEST [22214] postgres(at)postgres FATAL: password
> authentication failed for user "postgres" 2020-06-12 14:03:00.019 AEST
> [22214] postgres(at)postgres DETAIL: User "postgres" has no password
> assigned. Connection matched pg_hba.conf line 92: "host all all
> 127.0.0.1/32 md5"

> No password assigned. Which I knew. So I removed that "host all"  line
> from pg_hba leaving only the "local all" lines and failed again ...

Yeah. So, if the user doesn't have any password assigned in pg_authid,
you cannot use a password-based auth method. And you can't just not
have any auth method, which is why removing the pg_hba.conf line
altogether does not work. You have to specify some other auth method
than "md5".

If this is a single-user machine, you could just skip all the BS and set
the auth method to "trust", figuring that nobody but you can reach the
localhost port anyway.

A safer choice is "peer", but (at least on most platforms) that only
works with unix-socket connections not TCP --- that is, you'd need
to put it on a "local" pg_hba entry not a "host" entry. And those
entries are not applicable in your usage, it seems. I wonder why your
psql is trying a localhost TCP connection in the first place, though.
Are you writing "psql -h localhost", and if so why?

In short, my recommendation would be to put a "local all all peer"
line in pg_hba, drop "-h localhost" if you're using that, and be
sure to run psql as the Linux postgres user so that "peer" will
let you in. If that doesn't work, "local all all trust" is a
less secure fallback, and "host all all 127.0.0.1/32 trust" is
another route if you really don't want to use unix-socket for
some reason.

regards, tom lane