public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Mike Dewhirst <[email protected]>
Cc: [email protected]
Subject: Re: postgres user password reset problem
Date: Fri, 12 Jun 2020 00:50:25 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Mike Dewhirst <[email protected]> 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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: postgres user password reset problem
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox