Pooler error password authentication failed

user can still login without LOGIN privilege / validuntil #423

When I remove LOGIN privilege, a user can still login. After restarting pgBouncer service a normal ERROR occurs (Reloading service and RELOAD command has not effect).
When I set LOGIN privilege back a user can now login without restarting pgbouncer service.

Same principe with valid timestamp (pg_catalog.pg_authid.rolvaliduntil).

pgBouncer 1.11.0 — Debian 9 Stretch — PostgreSQL 11.5
auth_user & auth_query = SELECT usename, passwd FROM .

The text was updated successfully, but these errors were encountered:

Please provide more detail: full configuration, exact commands and output.

Here my long story to fully describe the context.
Il will describe in two parts :
Part A : Single user login
Part B : User Group login

Configuration

general

Debian 9.11 — Stretch
PostgreSQL 11.5 (Debian 11.5-3.pgdg90+1)
pgBouncer 1.11.0 (apt)

File : /etc/postgresql/11/main/postgresql.conf

File : /etc/postgresql/11/main/pg_hba.conf

Database (testing)

pgBouncer configuration

File : /etc/pgbouncer/pgbouncer.ini

File : /etc/pgbouncer/userlist.txt

Part A : Single User

Authentication

In this scenario the authenticate function will only have to check the username and NOT super user.

User Access tests login

CASE : postgres=# ALTER ROLE devel NOLOGIN;

=> OK — RAS, ALL CLEAR

CASE : postgres=# ALTER ROLE devel LOGIN;

=> OK (immediately) — ALL CLEAR

CASE : postgres=# ALTER ROLE devel VALID UNTIL ‘2019-01-01T15:29:00+01:00’;

=> BUG, devel should NOT be able to connect any more !

• reload service (C1) : no effect,
• pgbouncer RELOAD (C2) : no effect,
• restart service (C3) : OK

• (C1) # systemctl reload pgbouncer
• (C2) # psql — pgbouncer=# RELOAD;
• (C3) # systemctl restart pgbouncer

Log successfully logged (before reload)
Note : But should NOT be logged !

log system RELOAD
same : should not be logged

log pgbouncer RELOAD

CASE : postgres=# ALTER ROLE devel VALID UNTIL ‘infinity’;

=> BUG, devel should be able to connect !

• Login successfull after a few attemps
• reload service (C1) : no effect,
• pgbouncer RELOAD (C2) : no effect,

log error login

2nd attempt — successfully

service reload — (if) login error
Output :

next attempt (still an error)

next attempt is successfull

Part B : Group management

Users belong to a group. The function checks the authentication group membership for the user.

But it’s getting worse with groups management.

• LOGIN is not longer checked.
• VALID UNTIL either
  without pgbouncer restart 🙁

Источник

How to fix an error: password authentication failed for the user in PostgreSQL

I have two solutions for this Error.

First solution :

open Terminal and write this command in terminal

• you can click Tab , if you didn’t know PostgreSQL version

Then, go to the bottom of the file you just played and edit these lines
— if scroll didn’t work you can use keyboard buttons to scroll down

• you must change peer and md5 to trust to be as this

• peer means it will trust the identity (authenticity) of the UNIX users. So not asking for a password.
• md5 means it will always ask for a password, and validate it after hashing with MD5 .
• trust means it will never ask for a password, and always trust any connection.

Then follow these steps to fix Error:-

1- click Ctrl + s to save your edits.
2- click Ctrl + x to close the file.
3- run this command sudo service postgresql restart

Second solution :

If you remember correctly the user postgres has no DB password set on Ubuntu by default. That means, that you can log in to that account only by using the postgres OS user account.

Assuming, that you have root access on the box you can do:

If that fails with a database «postgres» does not exists error, then you are most likely not on a Ubuntu or Debian server 🙂 In this case simply add template1 to the command:

If any of those commands fail with an error psql: FATAL: password authentication failed for user «postgres» then check the file /etc/postgresql/8.4/main/pg_hba.conf : There must be a line like this as the first non-comment line:

For newer versions of PostgreSQL ident actually might be peer . That’s OK also.

Inside the psql shell you can give the DB user postgres a password:

You can leave the psql shell by typing Ctrl-D or with the command q .

Now you should be able to give pgAdmin a valid password for the DB superuser and it will be happy too. 🙂

Источник

PostgreSQL returns password authentication failed for user although password is correct

Published on February 14th 2020 — Listed in PostgreSQL Database

On a new PostgreSQL server, the following error message was received, when a user tried to use psql :

$ psql -h 127.0.0.1 -U dbuser
Password for user dbuser: *****
psql: FATAL: password authentication failed for user «dbuser»
FATAL: password authentication failed for user «dbuser»

The logs showed the following entries:

[11834] dbuser@dbuser FATAL: password authentication failed for user «dbuser»
[11834] dbuser@dbuser DETAIL: Password does not match for user «dbuser».
Connection matched pg_hba.conf line 92: «host all all 127.0.0.1/32 md5»
[11835] dbuser@dbuser FATAL: password authentication failed for user «dbuser»
[11835] dbuser@dbuser DETAIL: Password does not match for user «dbuser».
Connection matched pg_hba.conf line 92: «host all all 127.0.0.1/32 md5»

Verifying authentication configuration

Obviously the first question in such a situation is: Was the entered password correct? And in the special case of PostgreSQL a second question should always come into mind: Is there a correct configuration in pg_hba.conf for the attempted authentication?

The authentication obviously correctly matched a line in pg_hba.conf, as could also be seen in the log file:

# grep 127.0.0.1 /etc/postgresql/9.6/main/pg_hba.conf
host all all 127.0.0.1/32 md5

After it was verified that the entered password was actually correct, the creation of the SQL user was once more analyzed. This turned out to be a manual user creation using an SQL query (old habits):

postgres=# CREATE USER dbuser WITH PASSWORD ‘mysecretpassword’;

Spotting the difference in user creation

When using the createuser command, a special parameter -e can be used. This will show the actual SQL queries sent to PostgreSQL:

$ createuser -P -E -e dbuser
Enter password for new role: *****
Enter it again: *****
SELECT pg_catalog.set_config(‘search_path’, », false)
CREATE ROLE dbuser ENCRYPTED PASSWORD ‘md5b3e88aa92b0943f1d2eed5cc618255e8’ NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

A significant difference is the usage of «ENCRYPTED PASSWORD» vs. «WITH PASSWORD» from the manual SQL query. Would that mean that the md5 password encryption (as defined in pg_hba.conf) didn’t work because the password from the manual user creation (using SQL query) was understood as plain text?

This can be verified again, now that the user was created using createuser:

$ psql -h 127.0.0.1 -p 5432 -U dbuser
Password for user dbuser: *****
psql (9.6.16)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type «help» for help.

Indeed, the password authentication worked this time and the user is logged in!

Encrypted vs unencrypted passwords

The documentation of the createuser command shows that there are two different options available whether or not to use an encrypted password:

CREATE USER name [ [ WITH ] option [ . ] ]

where option can be:

SYSID uid
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP groupname [, . ]
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password’
| VALID UNTIL ‘abstime’

Particularly these keywords are described as:

These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.

It also says that if neither of these keywords was used, the system default would be applied. To check the current PostgreSQL setting whether or not to encrypt passwords by default, the following SQL query can be run:

$ psql -qAtc «SELECT name, setting FROM pg_settings WHERE name = ‘password_encryption’»
password_encryption|on

It can also be checked in the configuration file, in case the default should be changed:

# grep password_encryption /etc/postgresql/9.6/main/postgresql.conf
#password_encryption = on

TL;DR: Now it makes sense

The manual user creation using an SQL query did not contain either ENCRYPTED nor UNENCRYPTED keywords. This means that PostgreSQL automatically applied the default: ENCRYPTED. The given password («mysecretpassword») was therefore encrypted and stored in the database. Logins with exactly this plain password («mysecretpassword») would of course fail, because it does not match the encrypted stored value.

Although the manual user creation using an SQL query still works, the newer createuser command should be used primarily. This will avoid errors or mistakes from remembered SQL queries from earlier PostgreSQL versions. As mentioned, old habits.

Mert from wrote on Jan 2nd, 2021:

Thank’s for clarification i was trying wrap my mind around this for whole evening. More people should be aware of subtle difference between authentication mechanisms, especially newcomers.

Blog Tags:

© 2008 — 2023 by Claudio Kuenzler. Powered by .

Источник

Обсуждение: Installed. Now what?

Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

[databases]
MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432

[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
; ip address or * which means all ip-s
listen_addr = 127.0.0.1
listen_port = 6543
auth_type = trust
auth_file = /var/lib/pgsql/pgbouncer.txt

admin_users = postgres
stats_users = stats, root
pool_mode = session
server_reset_query = DISCARD ALL

;;; Connection limits
; total number of clients that can connect

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

Dne 20.11.2011 04:21, Phoenix Kiula napsal(a):

The auth_file is used only for connecting to the pgbouncer, it’s not
forwarded to the database server — the pgbouncer opens the connection on
behalf of the users, and you may actually have a completely different
users on the connection pooler.

Tomas

Re: Installed. Now what?

Re: Installed. Now what?

Re: Installed. Now what?

On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula wrote:
> On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant wrote:
>>
>> On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra wrote:
>>>
>>> Dne 20.11.2011 04:21, Phoenix Kiula napsal(a):
>>>
>>> My guess is that you actually require a password when connecting to the
>>> database, but you haven’t specified a password in the pgbouncer.ini
>>> file. You have to specify it in the MYDB line, i.e. something like
>>>
>>> [databases]
>>> MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
>>> port=5432 password=’mypassword’
>>>
>>>
>>> The auth_file is used only for connecting to the pgbouncer, it’s not
>>> forwarded to the database server — the pgbouncer opens the connection on
>>> behalf of the users, and you may actually have a completely different
>>> users on the connection pooler.
>
>
> OK. So I specified the password enclosed in double quotes.
>
> [databases]
> MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
> port=5432 password=»mypassword»
>
>
> Then restarted pgbouncer:
>
> service pgbouncer restart
>
> And this shows up as this:
>
> > lsof -i | grep pgbouncer
> pgbouncer 8558 postgres 7u IPv4 26187618 TCP
> localhost:lds-distrib (LISTEN)
>
> Is this normal? Shouldn’t the port number be somewhere? What’s «lds-distrib»?

I changed the port to the usual 6432 in the pgbouncer.ini. Restarted.
Now I see this:

> lsof -i | grep pgbounc
pgbouncer 10854 postgres 7u IPv4 26257796 TCP localhost:6432 (LISTEN)

So this is live and working. Pgbouncer is working. And yet, this is a problem:

> psql MYDB -E «MYDB_MYDB» -p 6432 -W
Password for user MYDB_MYDB:

psql: ERROR: no working server connection

From the log file:

2011-11-20 01:28:57.775 10854 WARNING server login failed: FATAL

2011-11-20 01:29:46.413 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us

The password I am entering in the terminal is right for sure. I’ve
tried it a few times, checked the caps lock, etc. Also, if the log
carries this «FATAL password authentication failed», why does the
terminal give the vague error «no working server connection»?

Источник

I had a set of services built with Docker Compose. In this set, I had a PostgreSQL service coupled with a PgBouncer instance that pooled requests from the other services of my application to the database. The PostgreSQL was at version 13 and I wanted to upgrade to the next version, at the time of this writing, 14.

The Docker Compose service settings initially looked like this:

The next version tag for the db service was 14-alpine. So I changed the docker-compose.yml respectively:

Once the new configuration is applied and the services are re-built and restarted I get the following error message:

The solution was to remove the database volume pg_data, re-build the service and restore the database backup.

The next problem, I’ve encountered was the incompatibility of the authentication method. The PgBouncer service I was running used md5 and I had these errors from the pgb service:

My solution was to change the authentication method on the PostgreSQL side, in my service db. To do that I needed to set the server setting password_encryption to md5. This can be done by adding the command line to the service settings in the file docker-compose.yaml:

Next, I needed the db service to use a custom file pg_hba.conf. In the project root, I created a copy of the original pg_hba.conf and modified the last line to use trust as METHOD. Originally the line looked like this:

After the modification the same line:

Finally, I mount the custom file as a volume to the service and tell PostgreSQL to use it instead of the default pg_hba.conf file:

The command section is in one line, with no line breaks.

I recreated the db container and the PgBouncer pooling service was now able to connect to the database.

Dne 20.11.2011 04:15, Phoenix Kiula napsal(a):
> I just did some testing.
>
> If the password is wrong, then it shows me the «authentication failed»
> message right in the terminal window, immediately.
>
> If the password is correct (plain text or md5 of that plain text —
> both have similar requests), it shows me the second error «no working
> connection» below.

Because it’s failing at different times.

The first command fails because the pgbouncer verifies the password
against the auth_file, finds out it’s incorrect and kicks you out.

The second command actually connects to pgbouncer (the password is
correct), attempts to open the connection to the database using the
connection string — AFAIK it’s

MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
port=5432

and fails because there’s no password or incorrect password.

You’ve used the same username and password both for the connection
pooler and for database, so it’s rather confusing.

> [host] > psql -h 127.0.0.1 MYDB -E «MYDB_MYDB» -p 6543psql: ERROR:
> password authentication failed for user «MYDB_MYDB»[coco] ~ > [coco] ~
>> pico /var/lib/pgsql/pgbouncer.txt
>
> [host] ~ > psql -h 127.0.0.1 MYDB -E «MYDB_MYDB» -p 6543
> psql: ERROR: no working server connection
>
>
> But in the second case, the error in the pgbouncer log is the same —
> authentication is failing.

No it’s not. When the authentication fails when connecting to pgbouncer,
the message is

Pooler Error: Auth failed

but when the database authentication fails, the message is

Pooler Error: password authentication failed for user «…»

In the first case you have to check the auth_file, in the second you
need to check the connection string in pgbouncer.ini.

> Why this inconsistent and utterly inane behavior from pgbouncer? Why
> can’t we see transparently what the error is?

It’s saying you exactly what’s going on. You’re confused because the
connection pooling is new to you and because you’ve decided to use the
same credentials both for DB and pgbouncer.

> Nowhere in the docs does it clearly specify with an example how the
> auth_file format should be.

Not sure which docs are you talking about, but the «quick start» in
doc/usage.txt shows an example of the file, and doc/config.txt (and the
man pages) state that the format is the same as pg_auth/pg_pwd.

Anyway it’s quite trivial — two strings, first one is username, second
one is the password. It’s either plain or hashed (depending on the
auth_type).

Tomas

On a new PostgreSQL server, the following error message was received, when a user tried to use psql:

$ psql -h 127.0.0.1 -U dbuser
Password for user dbuser: *****
psql: FATAL:  password authentication failed for user «dbuser»
FATAL:  password authentication failed for user «dbuser»

The logs showed the following entries:

[11834] dbuser@dbuser FATAL:  password authentication failed for user «dbuser»
[11834] dbuser@dbuser DETAIL:  Password does not match for user «dbuser».
    Connection matched pg_hba.conf line 92: «host    all             all             127.0.0.1/32            md5»
[11835] dbuser@dbuser FATAL:  password authentication failed for user «dbuser»
[11835] dbuser@dbuser DETAIL:  Password does not match for user «dbuser».
    Connection matched pg_hba.conf line 92: «host    all             all             127.0.0.1/32            md5»

Verifying authentication configuration

Obviously the first question in such a situation is: Was the entered password correct? And in the special case of PostgreSQL a second question should always come into mind: Is there a correct configuration in pg_hba.conf for the attempted authentication?

The authentication obviously correctly matched a line in pg_hba.conf, as could also be seen in the log file:

# grep 127.0.0.1 /etc/postgresql/9.6/main/pg_hba.conf
host    all             all             127.0.0.1/32            md5

After it was verified that the entered password was actually correct, the creation of the SQL user was once more analyzed. This turned out to be a manual user creation using an SQL query (old habits):

postgres=# CREATE USER dbuser WITH PASSWORD ‘mysecretpassword’;

Spotting the difference in user creation

When using the createuser command, a special parameter -e can be used. This will show the actual SQL queries sent to PostgreSQL:

postgres@pgserver:~$ createuser -P -E -e dbuser
Enter password for new role: *****
Enter it again: *****
SELECT pg_catalog.set_config(‘search_path’, », false)
CREATE ROLE dbuser ENCRYPTED PASSWORD ‘md5b3e88aa92b0943f1d2eed5cc618255e8’ NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

A significant difference is the usage of «ENCRYPTED PASSWORD» vs. «WITH PASSWORD» from the manual SQL query. Would that mean that the md5 password encryption (as defined in pg_hba.conf) didn’t work because the password from the manual user creation (using SQL query) was understood as plain text?

This can be verified again, now that the user was created using createuser:

Indeed, the password authentication worked this time and the user is logged in!

Encrypted vs unencrypted passwords

The documentation of the createuser command shows that there are two different options available whether or not to use an encrypted password:

Particularly these keywords are described as:

It also says that if neither of these keywords was used, the system default would be applied. To check the current PostgreSQL setting whether or not to encrypt passwords by default, the following SQL query can be run:

postgres@pgserver:~$ psql -qAtc «SELECT name, setting FROM pg_settings WHERE name = ‘password_encryption'»
password_encryption|on

It can also be checked in the configuration file, in case the default should be changed:

root@pgserver:~# grep password_encryption /etc/postgresql/9.6/main/postgresql.conf
#password_encryption = on

TL;DR: Now it makes sense

The manual user creation using an SQL query did not contain either ENCRYPTED nor UNENCRYPTED keywords. This means that PostgreSQL automatically applied the default: ENCRYPTED. The given password («mysecretpassword») was therefore encrypted and stored in the database. Logins with exactly this plain password («mysecretpassword») would of course fail, because it does not match the encrypted stored value.

Although the manual user creation using an SQL query still works, the newer createuser command should be used primarily. This will avoid errors or mistakes from remembered SQL queries from earlier PostgreSQL versions. As mentioned, old habits.

Add a comment

Show form to leave a comment

Comments (newest first)

Mert from wrote on Jan 2nd, 2021:

Thank’s for clarification i was trying wrap my mind around this for whole evening. More people should be aware of subtle difference between authentication mechanisms, especially newcomers.