0

I just installed postgresql and pgadmin from the repos on Fedora 36. I set up the initial postgres role and database, and logged in with the postgres user

sudo su - postgres
psql

From there, I set up a new user with an encrypted password: CREATE ROLE john WITH ENCRYPTED PASSWORD '123';

I also added this line to my pg_hba.conf:

host    all             all             127.0.0.1/32            scram-sha-256

Now, from unix, as the root user, if I try: psql -U john -W -h 127.0.0.1, I get this error:

psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  Ident authentication failed for user "john"

pgadmin also fails to login to the database with a similar error message.

But if I go into pg_hba.conf and disable this line:

host    all             all             127.0.0.1/32            ident

All of a sudden, I can login from both pgadmin and with the psql -U john -W -h 127.0.0.1 command from the root user.

Am I doing something wrong? Why do I need to disable ident auth entirely for this to work? I don't see any way to force password auth, rather than ident auth from the psql client, or in pgadmin.

Edit - this is my pg_hba.conf:

local   all             all                                     peer
#host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host    all             all             127.0.0.1/32            scram-sha-256

This is basically the default that came with postgres from the repos, but I:

  • Commented out the one ident line
  • Added the last line
Improve this question
4
  • My 10 year old memory on how this works is that pg_hba.conf gives rules where the first to match is the one that's used. So if ident fails then it will not try the next matching rules. In any case the pg_hba.conf manual page will be the most illuminating here. Commented Oct 27, 2022 at 17:33
  • To debug pg_hba we need to see the entire file. Please, paste it into the question (ommting comments and blank lines). Also we could need user name and database name, since it is possible to craft pg_hba to act differently for different users and databases. Commented Oct 27, 2022 at 17:59
  • @PhilipCouling this occurred to me, so I tried moving my line to the top. It didn't help Commented Oct 27, 2022 at 19:15
  • @NikitaKipriyanov as far as I can tell, my pg_hba.conf has nothing user-specific or database-specific in it Commented Oct 27, 2022 at 19:16

1 Answer 1

Reset to default
1

The existing ident rule any ipv4 local connections (from 127.0.0.1)

If the rule matches any connection attempt then that's the authentication type it uses and rules lower down the file are not used even if authentication fails.

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered.

In other words you need to disable ident for the client connections that you want to use something else.

What you've done is not necessary wrong, but you might want to leave it enabled for root user only.

You might also want to create a matching IPv6 rule for ::1 in case some software uses it.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.