0

I'm not able to login to PostgreSQL because it's not letting me in, saying password authentication failed.

Info

Using PostgreSQL on an M1 Mac. Installed Postgres through brew.
pg_hba.conf(not including the comments on top of the file):

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

Error

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "adithraghav"

Description

I've been getting this error ever since I restarted my M1 MacBook Air. Before restarting, it worked fine. Because of this error, I reinstalled Postgres, but still I get this error, even though I never created this user. I thought it might be something PostgreSQL created on its own based on my computer's login details, and I entered my computer's login details, but still no luck.

I can start the server with brew services start postgres, but I can't get into the PostgreSQL prompt.

I think I've forgotten the password or it has set the password on its own

What should I do to solve this problem? Should I reinstall PostgreSQL, this time making note to delete some extra information? Should I reset the password in some way? Is there some sort of forgot password option? What can I do to access the prompt?

Robo
  • 660
  • 5
  • 22
  • did you try this? https://stackoverflow.com/a/70351870/1683626 – y0j0 Jan 10 '22 at 12:58
  • No, but it didn't work for me. Maybe it's because I'm on Big Sur and not Monterey – Robo Jan 10 '22 at 13:06
  • 1
    What is the full `psql` command you are using? – Adrian Klaver Jan 10 '22 at 16:33
  • I'm not able to access the DB with just `psql` command – Robo Jan 10 '22 at 17:14
  • What happens if you do `psql postgres`? – Adrian Klaver Jan 10 '22 at 17:18
  • 1
    If a user does not exist, then password authentication must fail for it. Try logging on as a user which does exist. – jjanes Jan 10 '22 at 19:08
  • @AdrianKlaver It still asks for a password... – Robo Jan 11 '22 at 00:34
  • @jjanes, I can't create a new user because I can't log in... – Robo Jan 11 '22 at 00:34
  • Then you are going to need to find the file `pg_hba.conf` and add its contents as update to your question. – Adrian Klaver Jan 11 '22 at 00:45
  • Sure, I've edited my question – Robo Jan 11 '22 at 01:00
  • This may not be related at all but I've seen some weird problems with the M1, between docker / kafka even postgresql there is even a question on apple dev support with no answer as of yet https://developer.apple.com/forums/thread/673694 – Jorge Campos Jan 11 '22 at 01:54
  • Do you know any way to fix this? – Robo Jan 11 '22 at 02:00
  • @Bergi, no see [Homebrew](https://wiki.postgresql.org/wiki/Homebrew). The setup should allow a no password connection on `local(socket)` to the `postgres` database. This is confirmed by the contents of `pg_hba.conf`. My suspicion is that there is another instance of Postgres running. – Adrian Klaver Jan 11 '22 at 05:19
  • @Bergi If the user did not exist you would get something like `psql -d test -U dog psql: error: FATAL: role "dog" does not exist`. The error the OP gets: `FATAL: password authentication failed for user "adithraghav` is due to a password failure. What makes it suspicious is that the `pg_hba.conf` has no line with `auth` METHOD requiring a password, they are all set to `trust`. There should be no password authentication needed. – Adrian Klaver Jan 11 '22 at 17:28
  • @Bergi, I should have been clearer. If the OP is indeed pointing at the Postgres instance with the given `pg_hba.conf`(auth set to `trust`) then they would have gotten `FATAL: role does not exist` error. The fact they got `FATAL: password authentication ...` indicates they are trying to connect at an instance that is looking for a password. – Adrian Klaver Jan 11 '22 at 17:45
  • @AdrianKlaver You're right. I trusted jjanes "*If a user does not exist, then password authentication must fail for it.*", assuming that the error message would be the same. Going to delete my comments – Bergi Jan 11 '22 at 18:01
  • Thank you @AdrianKlaver, @Bergi, @jjanes, @JozefChechovsky and @JorgeCampos for trying to help me. Your inputs led me to think that there had been a cache or there was something wrong with the installation itself. I uninstalled the PostgreSQL I had installed with `brew` and installed via EDB. It works now. – Robo Jan 12 '22 at 00:45
  • Running `\du` confirms your theory, @AdrianKlaver, there is no user `adithraghav`. – Robo Jan 12 '22 at 00:53
  • The point is not whether there was a user `adithraghav` or not but the fact that the error was a password one and the `pg_hba.conf` you showed does not have a line requiring a password authentication method. Therefore you should not have gotten that error. To me that means there was more then one instance of Postgres running on the machine and the one you where trying to connect to had a different `pg_hba.conf` . If I where to put money on it, I would say that you had a previous install of EDB in parallel with the `brew` install. – Adrian Klaver Jan 12 '22 at 01:00
  • I did have an EDB install before, but I had uninstalled it when I was running with `brew`. – Robo Jan 12 '22 at 01:18
  • Or... I uninstalled only pgadmin4. Thank you. – Robo Jan 12 '22 at 01:18
  • @bergi if there is a pg_hba line for 'all' users which calls for password auth, then a non existent user name can match to that line. At that point, a password is requested, but must fail. The client is not told why it failed. If the non existent user matches to some other hba method, then it will get some other error. – jjanes Jan 12 '22 at 09:45

1 Answers1

0

After about half a week of endless brew and PostgreSQL uninstallations and reinstallations, endless hours of scouting StackOverflow from top to bottom about anything about PostgreSQL errors, endless elevenses and suppers and dinner teas pondering the solution, endless surfing documentations, I finally came to an answer.

And it's quite simple.

I had to uninstall the brew installation and install with EDB. That's all. sudo -u postgres psql now opens up a prompt which I see the beauty in for the first time.

I can't believe I never tried that, now I think about it. But who cares? It works, and that's all that matters.

Robo
  • 660
  • 5
  • 22