public inbox for [email protected]help / color / mirror / Atom feed
postgres user password reset problem 3+ messages / 2 participants [nested] [flat]
* postgres user password reset problem @ 2020-06-12 04:19 Mike Dewhirst <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Mike Dewhirst @ 2020-06-12 04:19 UTC (permalink / raw) To: [email protected] I know this is a common problem because the web is full of advice. Sadly none of it has worked for me. I re-installed PostgreSQL 10 on Ubuntu 18.04 after removing it with --purge and now I can't get back into it. 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 ... the log now says ... 2020-06-12 14:09:12.846 AEST [22462] LOG: database system is ready to accept connections 2020-06-12 14:09:13.440 AEST [22470] [unknown]@[unknown] LOG: incomplete startup packet 2020-06-12 14:09:59.424 AEST [22498] postgres@postgres FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL on 2020-06-12 14:09:59.427 AEST [22499] postgres@postgres FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off I am fairly lost here. My objective is to get the postgres user re-established, get my own user re-established, re-load the dump files and get back into production. Any advice appreciated Thanks Mike ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: postgres user password reset problem @ 2020-06-12 04:50 Tom Lane <[email protected]> parent: Mike Dewhirst <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2020-06-12 04:50 UTC (permalink / raw) To: Mike Dewhirst <[email protected]>; +Cc: [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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: postgres user password reset problem @ 2020-06-12 06:42 Mike Dewhirst <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Mike Dewhirst @ 2020-06-12 06:42 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] On 12/06/2020 2:50 pm, Tom Lane wrote: > 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. 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-06-12 06:42 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-06-12 04:19 postgres user password reset problem Mike Dewhirst <[email protected]> 2020-06-12 04:50 ` Tom Lane <[email protected]> 2020-06-12 06:42 ` Mike Dewhirst <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox