Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why password authentication failed for user "postgres"?

Why password authentication failed for user "postgres"?

From
BeginnerC
Date:
Hello everyone,
I am a newbie to the postgres,when I use the psql to connect to the postgres,a error message printed:
These command list like this:

psql -U postgres
Password for user postgres:postgres
postgreSQL: password authentication failed for user "postgres"

How to solve this problem?
Thanks in advance!

Re: Why password authentication failed for user "postgres"?

From
Reid Thompson
Date:
On Sat, 2022-06-04 at 06:32 +0800, BeginnerC wrote:
> Hello everyone,
> I am a newbie to the postgres,when I use the psql to connect to the
> postgres,a error message printed:
> These command list like this:
>
> psql -U postgres
> Password for user postgres:postgres
> postgreSQL: password authentication failed for user "postgres"
>
> How to solve this problem?
> Thanks in advance!

this may work...

switch user to postgres.
$ sudo su - postgres
login to postgres
$ psql 
reset the password for postgres
postgres-# \password

Re: Why password authentication failed for user "postgres"?

From
Jeff Janes
Date:
On Fri, Jun 3, 2022 at 6:32 PM BeginnerC <chuxuec@outlook.com> wrote:
Hello everyone,
I am a newbie to the postgres,when I use the psql to connect to the postgres,a error message printed:
These command list like this:

psql -U postgres
Password for user postgres:postgres
postgreSQL: password authentication failed for user "postgres"

How to solve this problem?
Thanks in advance!

The password you type should not be showing up as you type it, which is what you appear to be showing above.  Also, 'postgres' is a horrible password, you might as well configure 'trust' authentication if you are going to use that as a password.

Look in the db server's log file for a (possibly) more detailed error message.

Cheers,

Jeff

Re: Why password authentication failed for user "postgres"?

From
"Frank Finner"
Date:


On 2022-06-04 00:32, BeginnerC wrote:
Hello everyone,
I am a newbie to the postgres,when I use the psql to connect to the postgres,a error message printed:
These command list like this:

psql -U postgres
Password for user postgres:postgres
postgreSQL: password authentication failed for user "postgres"

How to solve this problem?
Thanks in advance!
  • Which OS are you working on?
  • Can you show us your pg_hba.conf?

If you use -U with psql, the connection must not try to use method "peer" (which means "use the system user with this name", but also means "You must be logged in with the system user corresponding to the postgresql user"), but some kind of authorization, like md5, and use it with IP address. This might be difficult for the user postgres, who often has no password set in the database. If you really need to connect with user postgres and some password, you can set one using the method described by Reid Thompson.

Regards,

Frank
Attachment

Re: Why password authentication failed for user "postgres"?

From
"David G. Johnston"
Date:
On Sun, Jun 5, 2022 at 4:06 PM Frank Finner <postgresql@finner.de> wrote:

If you use -U with psql, the connection must not try to use method "peer" (which means "use the system user with this name", but also means "You must be logged in with the system user corresponding to the postgresql user"), but some kind of authorization, like md5, and use it with IP address.

This is simply wrong (though I suppose only in a corner case).  You must connect via socket but peer authentication can still work.  In particular, so long as the value specified for "-U" is your operating system user name the connection will work just the same as if you didn't specify -U at all and instead relied on the psql default behavior of using your operating system user name for the value of user.  In short, the server only knows what value "user" has as part of the connection string - it has no knowledge of how that value became set.  However, it can prove that the socket connection being requested is owned by a particular user.

I think (going from memory at the moment) you can get the main exception to this rule via usage of pg_ident.conf (i.e., be logged in as "osuser" and supply "-U postgres"; peer auth will work so long as osuser is mapped to postgres and you connect via the socket (i.e., local, not host).

David J.