Windows postgresql password authentication failed for user - Доктор Windows

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.

Источник

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;

Источник

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:

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.

Источник

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.

Already on GitHub?
Sign in
to your account

Assignees

Comments

$ sudo docker run --name postgresql -itd --restart always   --env 'PG_PASSWORD=postgres'   --env 'PG_TRUST_LOCALNET=true'   --publish 5432:5432   --volume /srv/docker/postgresql:/var/lib/postgresql   sameersbn/postgresql:9.6-2
59edeb353dec8c503a7f8d86396decfcdb31b98a3b463c3e9c990083fb4dc59e

$ psql -Upostgres -hlocalhost
Password for user postgres: 
psql: FATAL:  password authentication failed for user "postgres"

mauricedoepke, afourmy, Barbery, MrSwitch, mileslucas, iNLyze, zalper, dveselov, GopherJ, wsz87, and 30 more reacted with thumbs up emoji
Moathal reacted with heart emoji

v10, still reproduced. Solution is:

You need to define a password for user postgres

Get container
docker ps
Enter inside of the container
docker exec -it <hash> bash
Start query console
psql
Define password
ALTER ROLE postgres WITH PASSWORD 'your_password';

Then you may grant other users as superuser and etc. (Make your volume persist)

Container Mgmnt:

Up:
docker-compose up —remove-orphans —force-recreate —build PostgreSQL

Down:
docker-compose down -v

AhmedBHameed, mcyleung, eduardorangell, kemicofa, birhanuh, Joostluijben, AnjaneyuluBatta505, ybuasen, AliA74, adrianchavez123, and 39 more reacted with thumbs up emoji
SergeyMMedvedev, dingobar, willduarte, Robertmw, songtianyi, and deadman2000 reacted with thumbs down emoji
arogyakoirala, NightQnEarth, sterswift, daniel-covelli, and Tanja-4732 reacted with hooray emoji
Moathal and Mitjaaa reacted with heart emoji

@zalper how should i save your steps in the container!!
I did your steps and commit the container but still now saved when running my image again !!
BTW i’m NB to docker.

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:

Option 1: Stop the PostgreSQL service on Windows
Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)
Option 3: Change the port of the docker container

yondchang, bluwy, VShkaberda, TJBelcher, p3t3rix, mafrax, o-andres-u, nikluwfy, andersonmoura, Mallikanand, and 146 more reacted with thumbs up emoji
mjohnston-vtx, sqrnut-1, omneimneh, UUPRETI, IlyaBritkov, Dreammaker001, zcybupt, blackstorm0514, grad44, WeiWeiCheng123, and 21 more reacted with hooray emoji
oLadeira, theding0x, DALLESANAA, ml-vines, CMofjeld, lalit-g-deepr, faragos, downtest, milijanadj, Hellin1, and 21 more reacted with heart emoji
nikluwfy, andersonmoura, khalilahmad0, danfmaia, exaucae, rbarthel, mjohnston-vtx, viloil, sqrnut-1, omneimneh, and 26 more reacted with rocket emoji

@eddex Just wanted to chime in to say your Option 1 above fixed this for me after I had been scratching my head for a couple of hours. That solution does not appear anywhere else online that I have been able to find and was extremely helpful. Thanks!

eddex, yondchang, exaucae, jtonikeetup, mjohnston-vtx, Lyakhova, omneimneh, lucasfpds, Quiarom, liekeceton, and 7 more reacted with hooray emoji

@eddex Thanks! I also benefited from you recalling your problem and solution. I’d not seen it mentioned anywhere else in 2 days of troubleshooting. I had noticed double use of the port in some netstat results, but reasoned it away as being 2 references to the same process since I didn’t know postgres 12 was running on my PC. Bad assumption on my part

@eddex Thanks a lot. I was having the same issue with my local Postgres installation and the container I was running by using docker. I just stopped the service on Windows and started working.

@eddex also saved the day for me here.

@eddex — after a couple of hours of struggle, this answer helped me.
Didnt realise my Postgres locally starts automatically upon login.
As I didnt need the local version anymore, uninstalled it.

/remind me about this on saturday

I still got the issue and really don’t know what changed.
I was doing some work on my home machine and everything was set up properly and was working. Then I moved working on my working machine, set it up there and it worked as well (and it still works there).

After moving to my working machine I removed the containers and images for postgres:12 from my home machine. Now I want to set the things up again but for some reason it refuses to log me. Haven’t changed any docker-compose settings.
I am afraid that if I now remove everything from my working machine and try to set it up again, it’ll stop working there too.

This is my docker-compose.yml file.

version: '3.1'

services:

    postgres:
        image: postgres:12
        container_name: "postgres-v12"
        ports:
            - 5432:5432
        environment:
            POSTGRES_PASSWORD: admin
        volumes:
            - db_vol:/var/lib/postgresql/data

    pgadmin:
        image: dpage/pgadmin4
        container_name: pgadmin4
        ports:
            - 3333:80
        environment:
            PGADMIN_DEFAULT_EMAIL: admin
            PGADMIN_DEFAULT_PASSWORD: admin


volumes:
    db_vol:
        external:
            name: "postgres-v12"

I don’t have postgres installed directly on my machine, tried to change ports, passwords, adding networks, re-pulling images, restarting docker etc. and it just won’t work.

Any ideas anyone?

Today, I have decided to try and re-create the volume. So I deleted this postgres-v12 and created a new one and just in case I named it postgres12. I updated docker-compose.yml and voila everything started working again. I have never done any manual user updates, so somehow something messed up the postgres user in that volume.

Thanks @eddex ! Solution 1 fixed my issue.

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:

Option 1: Stop the PostgreSQL service on Windows

Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)

Option 3: Change the port of the docker container

Do you maybe also know a solution for Mac OSX? I already spent hours on this bad connection error for this docker issue…

In my case (dockerizing a rails app) I had to delete tmp/db folder and rerun docker-compose up for me to solve the issue.

almost went crazy because I have tried even reinstalling docker to solve this issue, turns out it was because I forgot I installed a local postgresql. 🤪 thanks @eddex

Today, I have decided to try and re-create the volume. So I deleted this postgres-v12 and created a new one and just in case I named it postgres12. I updated docker-compose.yml and voila everything started working again. I have never done any manual user updates, so somehow something messed up the postgres user in that volume.

It worked for me as well

I had the same issue with just running
docker run --name pg -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres and I eventually realised that the command was using the postgres docker image that I had locally, tagged as latest (rather than pulling the latest one from remote).
Run d
docker image ls

and see what images you currently have. Removing the postgres:latest image I had locally and re-running the command ended up with the latest image being pulled and the login working!

this worked for me… i just forward and expose 5435 on my machine. leave pg on 5432 inside the container. Then it’s easy to config 5435 or any other port for dev use.

services:
  database:
    image: "postgres" # use latest official postgres version
    env_file:
      - database.env # configure postgres
    volumes:
      - database-data:/var/lib/postgresql/data/ # persist data even if container shuts down
    ports:
      - 5435:5432
    expose:
      - "5435"

volumes:
  database-data: # named volumes can be managed easier using docker-compose

my database.env for reference

  PGUSER=postgres
  PGPASSWORD=postgres
  PGDATABASE=postgres
  POSTGRES_USER=postgres
  POSTGRES_PASSWORD=postgres
  POSTGRES_DB=postgres

yes it’s been redacted. i am not actually forcing the default values. just wanted to clarify the file structure and proper envs as noted here: https://www.postgresql.org/docs/9.3/libpq-envars.html

in addition to the others the docker container uses.

there seems to be no reason for 2 different sets of envs, but i haven’t thought through every use case.

so perhaps that’s a possible fix here is using the core PG envs and then update container docs to match.

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:

Option 1: Stop the PostgreSQL service on Windows

Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)

Option 3: Change the port of the docker container

You are so beautiful, THANKSS

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:
* Option 1: Stop the PostgreSQL service on Windows

* Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (`sudo apt install postgresql-client-common postgresql-client libpq-dev`)

* Option 3: Change the port of the docker container

You saved me tons of painful 72hours of battle with innocent Intellij IDE and plugins

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:

Option 1: Stop the PostgreSQL service on Windows

Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)

Option 3: Change the port of the docker container

Faced the same issue. Solved it by changing port mapping on docker-compose

services:
  db:
    image: postgres
    container_name: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: ***
      POSTGRES_USER: ***
      POSTGRES_DB: ***
    ports:
      - 5435:5432    ==> changed 5432 to 5435

@gustavorps I think the solution is an easy one to work around. Using @JustifydWarrior’s fix above:

    ports:
      - 5435:5432
    expose:
      - "5435"

5433 and 5434 are also reserved for more common uses, however 5435 is not a very common protocol by definition & is most often used as a second postgres port.

@bibhuticoder came up with the same solution.

I think someone should submit a PR to fix this, even if it is a second docker compose file, simply called by using the filename flag.

Disabling the default service, is the simplest solution, but that shouldn’t be the preferred workaround, many of us need both running.

I had the same problem . My solution for windows :

Stop docker container
win + r and type services.msc
Find postgressql and stop
Start your container again

I solved my problem with this way .

Envs are used by postgres initdb to create your postgresql instance. If you changed yours envs after first execution, you need to delete the postgres container to force a recreation using new envs. If you have defined a volume, you will also need to remove it.

My english is not very well, but i try help you.

In your project run

if exist a folder called database-data (name of the your volume of postgres of the docker-compose.yml), remove this folder and run again

  docker-compose up -d --force-recreate

The my work this way.

Or
if it didn’t work, try
Remove all volumes outside project with

  docker volume rm $(docker volume ls -q)

and run again
«`console`
docker-compose up -d —force-recreate
«

Below is my postgres:14 in the docker-compose.yml

I am writing Go Program in the microservice manner.
One day before POSTGRES_PASSWORD: password
was the password. Next day I changed POSTGRES_PASSWORD: secret
Now the said error came.

The folder /db-data/postgres/ which I mentioned below has content populated by docker.
Deleted that content and again run docker. Now ok.

postgres:
image: ‘postgres:14.0’
ports:
— «5432:5432»
restart: always
deploy:
mode: replicated
replicas: 1
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: secret
POSTGRES_DB: users
volumes:
— ./db-data/postgres/:/var/lib/postgresql/data/

@eddex Thanks a lot. I was having the same issue with my local Postgres installation and the container I was running by using docker. I just stopped the service on Windows and started working.

THanks it works for me

A uppercase character in the username will also trigger this error message…

Источник

Содержание

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:
Postgresql role is blocked due to fail authentication attempts
Submit correction
Postgresql: ошибка аутентификации пароля для пользователя » postgres»
12 ответов:
Postgresql: password authentication failed for user “postgres”
25 Answers 25
FATAL: password authentication / DETAIL: Role “” does not exist. #773
Comments
ebolasudan commented Oct 16, 2020 •
wglambert commented Oct 16, 2020
ebolasudan commented Oct 16, 2020
wglambert commented Oct 16, 2020
ebolasudan commented Oct 16, 2020 •

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:

Verifying authentication configuration

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

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;

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:

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

where option can be:

Particularly these keywords are described as:

$ 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

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:

Источник

Postgresql role is blocked due to fail authentication attempts

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

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.

The indicated database user name was not found.

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.

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.

Prev	Up	Next
21.14. BSD Authentication	Home	Chapter 22. Database Roles

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Источник

Postgresql: ошибка аутентификации пароля для пользователя » postgres»

Я установил PostgreSQL 8.4, Postgres client и Pgadmin 3. Не удалось выполнить проверку подлинности для пользователя «postgres» как для консольного клиента, так и для Pgadmin. Я ввел пользователя как » postgres «и пароль» postgres», потому что он работал раньше. Но теперь аутентификация не удалась. Я делал это раньше пару раз без этой проблемы. Что же мне делать? И что же происходит?

12 ответов:

если я правильно помню нет DB пароль установлен на Ubuntu по умолчанию. Это значит, что вы можете войти в эту учетную запись только с помощью postgres пользователей ОС

ответ персонала правильный, но если вы хотите дополнительно автоматизировать может сделать:

$ sudo -u postgres psql -c «ALTER USER postgres PASSWORD ‘postgres’;»

готово! Вы сохранили User = postgres и password = postgres.

Если у вас нет пароля для пользователя postgres ubuntu do:

$ sudo passwd postgres

Это было неприятно, большинство из приведенных выше ответов верны, но они не упоминают, что вы должны перезапустить службу базы данных перед изменениями в pg_hba.файл conf вступит в силу.

Так что если вы делаете что-то вроде выше

затем перезагрузка как root ( на centos это что-то вроде сервиса Service postgresql-9.2 restart ) сейчас вы должны иметь доступ к БД как пользователь postgres

$psql psql (9.2.4) Введите «справка» для справки.

надеюсь, что это добавляет информацию для новых пользователей postgres

редактировать pg_hba.файл conf, например, с sudo emacs /etc/postgresql/9.3/main/pg_hba.conf

измените все методы аутентификации на trust . Измените пароль Unix для пользователя «postgres». Перезагрузите Сервер. Войдите с помощью psql -h localhost -U postgres и использовать только что установленный пароль Unix. Если это работает, вы можете повторно установить файл pg_hba.файл conf по умолчанию.

Если вы пытаетесь войти в оболочку postgres как пользователь postgres, то вы можете использовать следующие команды.

переключиться на пользователя postgres

надеюсь, что это поможет

старайтесь не использовать параметр-W и оставьте пароль пустым. Иногда пользователь создается без пароля.

если это не работает, сбросьте пароль. Есть несколько способов сделать это, но это работает на многих системах:

Как правило: ВЫ НИКОГДА НЕ ДОЛЖНЫ УСТАНАВЛИВАТЬ ПАРОЛЬ ДЛЯ ПОЛЬЗОВАТЕЛЯ POSTGRES.

Если вам нужен доступ суперпользователя от pgAdmin, сделайте другого суперпользователя. Таким образом, если учетные данные для этого суперпользователя скомпрометированы, вы всегда можете ssh в фактический хост базы данных и вручную удалить суперпользователя с помощью

Я просто хотел добавить, что вы также должны проверить, если ваш пароль истек.

вот некоторые комбинации, которые я пытался войти:

Источник

Postgresql: password authentication failed for user “postgres”

I have installed PostgreSQL 8.4, Postgres client and Pgadmin 3. Authentication failed for user «postgres» for both console client and Pgadmin. I have typed user as «postgres» and password «postgres», because it worked before. But now authentication is failed. I did it before a couple of times without this problem. What should I do? And what happens?

25 Answers 25

If I remember correctly the user postgres has no DB password set on Ubuntu by default. That means, that you can login 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. 🙂

Источник

FATAL: password authentication / DETAIL: Role “” does not exist. #773

I have been looking for a workaround for 3 days now and it seems to be a fairly common issue that I can’t seem to fix for myself due to my lack of knowledge with docker/Postgres. I believe it has something to do with the volume not being able to write.

the errors I’ve been able to see are ;

FATAL: password authentication failed for user «app»
DETAIL: Role “app” does not exist. Connection matched pg_hba.conf line 95: “host all all all md5”

So I can see that the user is not being created for some reason. nor is the DB and at this point, I don’t even know how to change this pg_hba.conf file nor why it should be needed to be changed in the first place on an official docker image. I would assume that it would work right off the bat. I am constantly deleting the images and rebuilding them trying to make changes with no hope so far.

What am I missing and how can I fix this so I can actually do something with Postgres?

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

If your app-db volume has files in it then the entrypoint script won’t initialize the database, what’s the log output of that container on startup?

If your app-db volume has files in it then the entrypoint script won’t initialize the database, what’s the log output of that container on startup?

well, I’m not too sure, but I have been completely deleting all the images using docker system prune -a and trying to rebuild only to get the same errors, the container will show what’s below, otherwise, is there a way to get better logs?

`PostgreSQL Database directory appears to contain a database; Skipping initialization

2020-10-16 14:40:32.119 UTC [1] LOG: starting PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

2020-10-16 14:40:32.119 UTC [1] LOG: listening on IPv4 address «0.0.0.0», port 5432

2020-10-16 14:40:32.119 UTC [1] LOG: listening on IPv6 address «::», port 5432

2020-10-16 14:40:32.156 UTC [1] LOG: listening on Unix socket «/var/run/postgresql/.s.PGSQL.5432»

2020-10-16 14:40:32.174 UTC [26] LOG: database system was shut down at 2020-10-16 14:08:16 UTC

2020-10-16 14:40:32.221 UTC [1] LOG: database system is ready to accept connections

2020-10-16 14:40:36.071 UTC [35] FATAL: password authentication failed for user «app»

2020-10-16 14:40:36.071 UTC [35] DETAIL: Role «app» does not exist.