Thread: postgres user password reset problem

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

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

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