Пользователь не прошел проверку подлинности по паролю postgresql windows - Доктор Windows

Answer given is almost correct just missing some pointers which i’ll be taking care of in my solution

First make sure your user have a sudo access if not you can use the below command to add your user as sudo user :-

sudo adduser <username> sudo

The change will take effect the next time the user logs in.

i) Now go to sudo vim /etc/postgresql/<your_postgres_version>/main/pg_hba.conf file and look for line that says :

local   all             postgres                                md5 #peer

and comment that. Just below that line there must be a commented line that says:

local   all             postgres                                peer

or for older versions it’ll be :-

local   all         postgres                          ident

Uncomment that line.

ii) Now restart the postgres by using any of these commands :-

sudo /etc/init.d/postgresql restart

sudo service postgresql restart

iii) Now you can simply log into postgres using the following command :

sudo -u postgres psql

iv) once you’re in you can create any operation you want to in my case i wanted to create a new database you can do the same using below command :

CREATE DATABASE airflow_replica;

Источник

For those of you who got this error and NONE of these answers helped, I may not have StackOverflow fish for you, but I’ll teach you how to fish!

You likely don’t have the correct order of lines in the pg_hba.conf file. If you read this PostgreSQL documentation link below, it says this error can be thrown if «no matching entry is found». However, that is NOT always true! Documentation is written by humans and humans make mistakes.

https://www.postgresql.org/docs/current/client-authentication-problems.html

The truth is that a line further up might take precedence, is qualifying and is forcing you to use a password stored in PostgreSQL rather than delegated authentication or some other method. If you are not specifying a password stored in PostgreSQL, then you do not need the LOGIN role attribute. Put a line at the very top of this list with your specific user, authentication protocol, network details and other criteria. Also, many may think that most computers use IPv4. Try IPv6 and you’ll be surprised. Once you know the very specific criteria of your issue and place a line at the top, then you have established the ONLY RELIABLE WAY to troubleshoot these pg_hba.conf issues without source code debugging!

Another helpful trick is to create a crapload of Server entries in pg_admin (SQL IDE for PostgreSQL) with all of your users and authentication protocols for testing. When you test different scenarios, you’ll instantly know which ones fail.

Also, whenever you change this file, restart the PostgreSQL service, before testing the user.

You’re welcome my friend.

Источник

For those of you who got this error and NONE of these answers helped, I may not have StackOverflow fish for you, but I’ll teach you how to fish!

https://www.postgresql.org/docs/current/client-authentication-problems.html

Also, whenever you change this file, restart the PostgreSQL service, before testing the user.

You’re welcome my friend.

Источник

Authentication failures and related problems generally manifest themselves through error messages like the following:

FATAL:  no pg_hba.conf entry for host "123.123.123.123", user "andym", database "testdb"

This is what you are most likely to get if you succeed in contacting the server, but it does not want to talk to you. As the message suggests, the server refused the connection request because it found no matching entry in its pg_hba.conf configuration file.

FATAL:  password authentication failed for user "andym"

Messages like this indicate that you contacted the server, and it is willing to talk to you, but not until you pass the authorization method specified in the pg_hba.conf file. Check the password you are providing, or check your Kerberos or ident software if the complaint mentions one of those authentication types.

FATAL:  user "andym" does not exist

The indicated database user name was not found.

FATAL:  database "testdb" does not exist

The database you are trying to connect to does not exist. Note that if you do not specify a database name, it defaults to the database user name, which might or might not be the right thing.

Tip

The server log might contain more information about an authentication failure than is reported to the client. If you are confused about the reason for a failure, check the server log.

Источник

Помогите разобраться народ !!!
Установлен 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 «PAROLnPAROLn» | 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] СООБЩЕНИЕ:  система БД выключена

Источник

Solution of psql: FATAL: Peer authentication failed for user “postgres” (or any user)

The connection failed because by default psql connects over UNIX sockets using peer authentication, that requires the current UNIX user to have the same user name as psql. So you will have to create the UNIX user postgres and then login as postgres or use sudo -u postgres psql database-name for accessing the database (and psql should not ask for a password).

If you cannot or do not want to create the UNIX user, like if you just want to connect to your database for ad hoc queries, forcing a socket connection using psql --host=localhost --dbname=database-name --username=postgres (as pointed out by @meyerson answer) will solve your immediate problem.

But if you intend to force password authentication over Unix sockets instead of the peer method, try changing the following pg_hba.conf* line:

from

# TYPE DATABASE USER ADDRESS METHOD
local  all      all          peer

# TYPE DATABASE USER ADDRESS METHOD
local  all      all          md5

peer means it will trust the identity (authenticity) of UNIX user. 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.

You can, of course, also create more specific rules for a specific database or user, with some users having peer and others requiring passwords.

After changing pg_hba.conf you’ll need to restart PostgreSQL if it’s running. E.g. sudo service postgresql restart

Steps to change/create default `postgres` user’s password:

trust connection by adding in pg_hba.conf file

local all postgres trust

Restart postgresql service

sudo service postgresql restart

psql -U postgres
At the postgres=# prompt, change the user name postgres password:

ALTER USER postgres with password ‘new-password’;

Revert the changes in pg_hba.conf file from trust to md5 and restart postgresql.

pg_hba.conf file location

The file pg_hba.conf will most likely be at /etc/postgresql/9.x/main/pg_hba.conf
To check location of pg_hba.conf connect to postgres db using psql then type SHOW hba_file; command.

After change pg_hba.conf file, you can execute SELECT pg_reload_conf(); or pg_ctl reload with superuser instead of restart postgresql service.

^{* Source}

Источник

Tip

Steps to change/create default postgres user’s password:

pg_hba.conf file location

Steps to change/create default `postgres` user’s password: