Re: postgres user password reset problem

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.

Using trust worked.

All is now working and restored from dumps. Two of the databases are in production and the other four are for staging sites. Fortunately low traffic.

Many thanks for your support

Cheers

Mike

		regards, tom lane