0

I was configuring postgresql and pgAdmin4 in my system, for a project i have been working on. Followed the process as directed in the official documentation, installed and started postgresql using these commands

sudo apt update
sudo apt install postgresql postgresql-contrib

sudo service postgresql start

Then, setup a password for 'postgres' using sudo passwd postgres. Then, went on to download the pgAdmin4 adminstration platform for postgresql using the following commands,

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

sudo apt install pgadmin4

Faced the real problem while trying to connect with server.

I was trying to configure postgresql and pgadmin4 for my system, but while connecting with the server, i got this error

unable to connect to server: connection failed: fatal: password authentication failed for user "postgres" connection to server at "localhost" (127.0.0.1). port 5432 failed: fatal: password authentication failed for user "postgres"

error

I went to look for pg_hba.conf pg_hba.conf file configuration, as suggested by some people, but it looks fine, i don't know. My postgresql.conf configuration is

listen_addresses = 'localhost'

port = 5432

password_encryption = scram-sha-256

As for my operating system i.e, Ubuntu 22.04 LTS, it seems to support pgAdmin4 version i installed (7.3) as there's no upperlimit specified for it.supported versions

Adi Akhilesh Singh 5-Year IDD
  • 1
  • 1
  • "as directed in the official documentation" The official documentation is over 3000 pages. Not counting all the other stuff that people call the official documentation even though it is not. Please tell us what you actually did. – jjanes Jun 21 '23 at 21:48
  • 1
    "sudo passwd postgres" That is not how you set the password for the postgres *database* user. That sets it for the OS user, which is something different (and is of no relevance to the current situation.) – jjanes Jun 21 '23 at 21:49
  • Also, it is good that you know that `pg_hba.conf` is fine. If you understand that much about PostgreSQL, you don't need our help, and you can easily solve the problem yourself. – Laurenz Albe Jun 22 '23 at 05:48
  • @LaurenzAlbe Sir, i apologize for it. Actually, this was my first time trying to raise an issue :) Didn't have much clue abt the way i should put it. I shared the pg_hba.conf file's config in the img url, please help me with that if there's something i can fix. – Adi Akhilesh Singh 5-Year IDD Jun 22 '23 at 08:13
  • @jjanes Sir, i have shared all the commands i used. And for the user password, that's what i learned from a tutorial, didn't know it doesn't set for the database user. Can you please guide me through that? – Adi Akhilesh Singh 5-Year IDD Jun 22 '23 at 08:24
  • You need `postgres` in `pb_hba.conf`, e.g. `local all postgres scram-sha-256` and `host all postgres 127.0.0.1/32 scram-sha-256` (and entries for any IPv4 and IPv6 addresses, including IPv6 loopback) and you need `local` and `host` entries for `replication` for `postgres` as well. That is your problem. Start with [Server Start](https://www.postgresql.org/docs/15/server-start.html) and then to [Chap 21 - Client Authentication](https://www.postgresql.org/docs/15/client-authentication.html). – David C. Rankin Jun 22 '23 at 09:01
  • @DavidC.Rankin i changed that, and set the postgres database user password now. Then, it was showing, "Peer authentication failed" , so i changed peer to scram-sha-256 for the "Database administrative login by Unix domain socket" in the pg_hba.conf now it works! – Adi Akhilesh Singh 5-Year IDD Jun 22 '23 at 11:55
  • Thanks everyone! And extremely sorry for assuming things, and infact passing decision about stuff i am new to. – Adi Akhilesh Singh 5-Year IDD Jun 22 '23 at 11:58
  • Glad you got it working. The setup isn't that difficult, but like anything new, it takes time to make friends with where all the pieces of configuration are hidden and what values you need for your setup. This is a perfect teaching moment. No matter what you are faced with in the future, the key is to *Slow Down*, step back and make sure you have all the information you need to solve the problem. (applies to any topic). Setup by guesswork never ends well ... `:)` – David C. Rankin Jun 22 '23 at 21:36
  • @DavidC.Rankin Completely agreed sir! And yeah, It's also a perfect learning moment for me as well. – Adi Akhilesh Singh 5-Year IDD Jun 23 '23 at 18:59

1 Answers1

0

There are two possible explanations:

  1. You used the wrong password (this is the most likely one)

  2. you don't have a SCRAM-hashed password for that user (look at pg_authid.rolpassword for that user)

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263