Содержание
- user can still login without LOGIN privilege / validuntil #423
- Comments
- Configuration
- general
- 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
- User Access tests login
- CASE : postgres=# ALTER ROLE devel NOLOGIN;
- CASE : postgres=# ALTER ROLE devel LOGIN;
- CASE : postgres=# ALTER ROLE devel VALID UNTIL ‘2019-01-01T15:29:00+01:00’;
- CASE : postgres=# ALTER ROLE devel VALID UNTIL ‘infinity’;
- Part B : Group management
- How to fix an error: password authentication failed for the user in PostgreSQL
- First solution :
- open Terminal and write this command in terminal
- 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.
- PostgreSQL returns password authentication failed for user although password is correct
- Verifying authentication configuration
- Spotting the difference in user creation
- Encrypted vs unencrypted passwords
- TL;DR: Now it makes sense
- Add a comment
- Comments (newest first)
- Blog Tags:
- Обсуждение: 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?
- 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?
- Re: Installed. Now what?
- Re: Installed. Now what?
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
db: image: postgres:13—alpine volumes: — pg_data:/var/lib/postgresql/data/ env_file: — ./.env networks: — app—network pgb: image: edoburu/pgbouncer:1.15.0 depends_on: — db networks: — app—network env_file: — ./.env ... |
The next version tag for the db
service was 14-alpine
. So I changed the docker-compose.yml
respectively:
db: image: postgres:14—alpine volumes: — pg_data:/var/lib/postgresql/data/ env_file: — ./.env networks: — app—network ... |
Once the new configuration is applied and the services are re-built and restarted I get the following error message:
FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14 |
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:
ERROR: srv/username@181.55.0.2:5432 cannot do SCRAM authentication: wrong password type LOG: srv/username@181.55.0.1:53604 closing because: server login failed: wrong password type (age=0s) WARNING: srv/username@181.55.0.1:53604 pooler error: server login failed: wrong password type |
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
:
db: image: postgres:14—alpine volumes: — pg_data:/var/lib/postgresql/data/ env_file: — ./.env command: postgres —c password_encryption=md5 |
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:
... #line to modify host all all all md5 |
After the modification the same line:
#line to modify host all all all trust |
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:
db: image: postgres:14—alpine volumes: — pg_data:/var/lib/postgresql/data/ — ./compose/local/db/:/var/lib/postgresql/custom/ env_file: — ./.env command: postgres —c password_encryption=md5 —c hba_file=/var/lib/postgresql/custom/pg_hba.conf |
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:
$ 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.
dbuser=>
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:
ENCRYPTED | UNENCRYPTED
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:
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.