Mike Dewhirst <miked@dewhirst.com.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@postgres FATAL: password
> authentication failed for user "postgres" 2020-06-12 14:03:00.019 AEST
> [22214] postgres@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