Vitaly Karasik, при инсталяции он просит придумать пароль, я и пробовал вводить этот самый пароль при создании БД, но не принимает. Вы мне скинули ссылку, попробовал по инструкции, но выяснилось, что у меня урезанная версия виндоус и нет функции просмотра учетных записей других программ.
Кстати в pgadmin с этим паролем я могу зайти, там попробовал изменить пароль базы postgres, создать еще одного суперюзера, но теперь отчет об ошибке ведет себя по другому:
Раньше в сообщении об ошибке было написано, что не удалось подключиться к базе postgres, сейчас пишет, что не удалось подключиться к базе template1… и дальше все также (пользователь Albert не прошел проверку подлинности (по паролю).
Завтра поставлю себе убунту и буду работать уже на ней. Надеюсь там не будет возникать таких ошибок. Еще отчет об ошибке отправил на офф. сайт postgreSQL.
Table of Contents
- Introduction
- Solutions to Fix the Error
- 1. Verify the Password
- 2. Reset the ‘postgres’ User Password
- 3. Editing the pg_hba.conf File
- Conclusion
Introduction
Encountering an authentication error with PostgreSQL can be a stress-inducing event, but there are several methodologies to troubleshoot and solve this issue. The error “Password authentication failed for user ‘postgres’” specifically means that the password provided for the ‘postgres’ user is not matching the one stored in the database. This discrepancy might be due to various reasons, such as an incorrect password, misconfigured PostgreSQL files or insufficient user rights.
Solutions to Fix the Error
1. Verify the Password
This solution starts with the simplest step of verifying and ensuring that the correct password is being used while attempting to connect to the PostgreSQL database.
- Double-check the password provided for being accurate.
- Try logging in via the psql command-line interface to validate the password:
psql -U postgres -W
If the above-mentioned password works, the problem might lie with the application configuration using PostgreSQL.
Advantages: Simple and quick to check.
Limitations: If the password is lost, this method will not resolve the error directly.
2. Reset the ‘postgres’ User Password
If the password for the ‘postgres’ user has been forgotten or needs to be reset, this can be accomplished by accessing the PostgreSQL interactive terminal as a different, privileged user and setting a new password.
1. Sudo to the PostgreSQL user:
sudo su - postgres2. Open the PostgreSQL interactive terminal:
psql3. Run the command to set a new password for ‘postgres’ user:
ALTER USER postgres WITH PASSWORD 'newpassword';Advantages: Direct method to reset a forgotten password.
Limitations: Requires provisional access to the PostgreSQL shell as a privileged user.
3. Editing the pg_hba.conf File
Improper configuration in PostgreSQL’s pg_hba.conf file can result in authentication issues. Modifying this file to use a different authentication method such as ‘trust’ can temporarily bypass the password for troubleshooting purposes.
- Access the PostgreSQL data directory, which typically contains the pg_hba.conf file. It is usually located in ‘/etc/postgresql/<version>/main/’ on Ubuntu or ‘/var/lib/pgsql/data/’ on Red Hat-based systems.
- Edit the pg_hba.conf file, changing the method to ‘trust’ for localhost or specific IP addresses.
- Reload the PostgreSQL server configuration:
sudo systemctl reload postgresql
Although changing the file and reloading the configuration is fast, using the ‘trust’ authentication method should be temporary, as it is not secure.
Advantages: Useful for diagnosing authentication issues.
Limitations: Must ensure the security of the system during troubleshooting, as ‘trust’ method allows connections without a password.
Conclusion
If none of the solutions work, there may be a deeper configuration issue or possible data corruption. To handle such scenarios, additional diagnostics may be required, possibly leading to recovery from backups or consulting with a database specialist. Nonetheless, the above solutions cover the majority of the scenarios encountered with the stated error in PostgreSQL.
Помогите разобраться народ !!!
Установлен PostgreSQL 9.6
Это моя настройка в pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD LOCAL ALL postgres md5 # "local" is for Unix domain socket connections only LOCAL ALL ALL md5 # IPv4 local connections: host ALL ALL 127.0.0.1/32 md5
Задан пароль пользователю «postgres» в Ubuntu
sudo echo -e «PAROL\nPAROL\n» | passwd postgres;
Пароль пользователю «postgres» в базе был задан:
sudo -U postgres psql -c «ALTER USER postgres WITH ENCRYPTED PASSWORD ‘PAROL’;»
Перезапускаю service postgresql restart
В результате, в логах:
2017-03-09 14:55:43 MSK [11185-1] СООБЩЕНИЕ: система БД была выключена: 2017-03-09 14:55:42 MSK 2017-03-09 14:55:43 MSK [11185-2] СООБЩЕНИЕ: Защита от наложения мультитранзакций сейчас включена 2017-03-09 14:55:43 MSK [11184-1] СООБЩЕНИЕ: система БД готова принимать подключения 2017-03-09 14:55:43 MSK [11189-1] СООБЩЕНИЕ: процесс запуска автоочистки создан 2017-03-09 14:55:44 MSK [11191-1] [н/д]@[н/д] СООБЩЕНИЕ: неполный стартовый пакет 2017-03-09 14:55:44 MSK [11194-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:44 MSK [11194-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:45 MSK [11197-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:45 MSK [11197-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:45 MSK [11200-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:45 MSK [11200-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:46 MSK [11203-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:46 MSK [11203-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:46 MSK [11206-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:46 MSK [11206-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:47 MSK [11209-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:47 MSK [11209-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:47 MSK [11212-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:47 MSK [11212-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:48 MSK [11215-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:48 MSK [11215-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:48 MSK [11218-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:48 MSK [11218-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:49 MSK [11221-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:49 MSK [11221-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:49 MSK [11224-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:49 MSK [11224-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:49 MSK [11184-2] СООБЩЕНИЕ: получен запрос на "вежливое" выключение 2017-03-09 14:55:49 MSK [11189-2] СООБЩЕНИЕ: процесс запуска автоочистки завершается 2017-03-09 14:55:49 MSK [11186-1] СООБЩЕНИЕ: выключение 2017-03-09 14:55:49 MSK [11184-3] СООБЩЕНИЕ: система БД выключена
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.
PostgreSQL is an open-source database management system that allows the user to create relational databases using different CLI or GUI applications. The user will need to set the credentials for the servers which will be required while accessing the PostgreSQL server at any moment. Additionally, it enables the user to alter the passwords and credentials if the user has lost them.
This guide will explain how to solve the password authentication error for the user in PostgreSQL.
How to Solve the FATAL: Password Authentication Failed for User Postgres Error?
This error occurs in the PostgreSQL client application when the user provides the wrong password while connecting to the server:
To rectify the stated error, first, open the Notepad from the computer by clicking on the “Run as administrator” button:
Expand the “File” menu from the notepad and click on the “Open” button or press Ctrl+O from the keyboard:
Head into the “data” folder from the “PostgreSQL” directory to select the “pg_hba.conf” file and click on the “Open” button:
Scroll down to the bottom of the file to copy the last section of the file and store it on the local system:
After that, change the “scram-sha-256” with the “trust” keyword in the “METHOD” column, and click on the “Save” button from the “File” menu:
Open the “Run” dialog box from the local system:
Type the “services.msc” and click on the “OK” button:
Locate the “PostgreSQL” file from the list of all the services to click on it and then click on the “restart” button:
Restart the PostgreSQL client and it will allow the user to access the Postgres database to open the “Query Tool”:
Run the following query to change the password of the “postgres” user and then access the server using the newly provided password:
ALTER USER postgres WITH PASSWORD 'postgres';
Head back to the “pg_hba.conf” file from the “PostgreSQL” directory to select and change the “trust” with the “scram-sha-256” keyword:
Open the “Run” application one more time from the system:
Head into the “services.msc” page by clicking on the “OK” button:
Select the PostgreSQL file and click on the “Restart” button:
Open the PostgreSQL client to use the password and click on the “OK” button:
That’s all about solving the password authentication error while connecting to the server.
Conclusion
To solve the Fatal: password authentication failed for the user “postgres” error while connecting to the server, simply open the “pg_hba.conf” file. Change the values in the method column from the last section and then restart the PostgreSQL application from the “services.msc” application. Open the PostgreSQL client and change the password to log in to the server. This guide has explained the process of solving the password authentication failed error while connecting to the PostgreSQL server.