Question

87

Postgres password authentication fails

rated 0 times [ 87] [ 0] / answers: 1 / hits: 291590 / 2 Years ago, thu, september 1, 2022, 9:53:48

I've installed PostgreSQL 9.1 and pgadmin3 on Ubuntu Server 13.10.

I configured postgresql.conf with: listen_addresses = '*'

also I configured ph_hba.conf by changed peer connections to md5

Plus I reset the password of postgres by: sudo password postgres

then restarted the service with sudo /etc/init.d/postgresql restart

after that I tried to connect to the default PostgreSQL template database:

sudo -u postgres psql template1

but login failed with this error message:

psql: FATAL:  password authentication failed for user "postgres"

then I tried to login from the pgadmin, which gave me the same error.

I've read here that it might be a password expiry dates bug
PostgreSQL user can not connect to server after changing password

but I couldn't solve it coz I cannot login with psql. Does anyone now how to resolve this issue?

EDIT

ph_hba file:

here

Screenshot:

enter image description here

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

rillrage

Add To Favorites

Follow

Total Points: 122

Total Questions: 120

Total Answers: 103

Location: Tokelau

Member since Thu, Aug 26, 2021

3 Years ago

rillrage questions

1 Is there any way to control ls output format

Wed, Apr 6, 22, 17:39, 2 Years ago

1 Added a Samsung NVMe SSD, but I can't see it in File Explorer

Sat, May 7, 22, 07:10, 2 Years ago

1 Accidentally terminating installing restricted extras and vlc during installation

Fri, Apr 28, 23, 18:11, 1 Year ago

1 How to fix the ugly font rendering of "Tex Gyre Pagella" in Firefox?

Wed, Aug 3, 22, 21:02, 2 Years ago

1 Changing screenshot directory in Ubuntu 21.10

Sat, Apr 30, 22, 13:11, 2 Years ago

View All

answered 2 Years ago percol · Accepted Answer

You are confusing the password for the Unix user "postgres" with the database password for the database user "postgres". These are not the same.

You've locked yourself out, because you enabled md5 authentication for database user postgres without setting a password for the database user postgres.

Add a new line to the top of pg_hba.conf:

local    postgres     postgres     peer

Then restart/reload PostgreSQL:

/etc/init.d/postgresql reload

and run:

sudo -u postgres psql

From the resulting prompt:

ALTER USER postgres PASSWORD 'my_postgres_password';

remove the line you added to pg_hba.conf and restart Pg again. You can now use the password you set above to connect to PostgreSQL as the postgres user.

To learn more, read the "client authentication" chapter of the user manual and the docs on pg_hba.conf.