Re: postgres user password reset problem

On 12/06/2020 2:50 pm, Tom Lane wrote:
> 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.

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