-1

I was under the impression that Postgres used some local socket magic to allow a local user to access postgres without password, whereas passing the host (-h) option would force it to use TCP and thus prompt for a password. This does not seem to be the case:

$ command psql -h 127.0.0.1 -W -U sim mydb
Password:
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
psql (15.3 (Homebrew))
Type "help" for help.

127.0.0.1 sim@mydb=>

I force a password prompt, pass localhost as the host and still I can provide whatever password I like: I always get in. I am not sure what happens here, but it is not what I expect.

My intent is to test that I got the password right, to debug some application error, so this behavior is not helpful.

Homebrew install on macOS 13

1 Answer 1

1

Look in pg_hba.conf. It probably has something like this:

local   all    all     peer

or this:

local   all    all     trust

As long as there is a ROLE named sim:

  • peer lets a MacOS user named sim connect, and
  • trust lets sim connect no matter what.

Excerpt from docs on pg_hba.conf:

Client authentication is controlled by a configuration file, which traditionally is named pg_hba.conf and is stored in the database cluster's data directory.

Typical locations for pg_hba.conf when using Homebrew:

$ find /opt -name pg_hba.conf -type f
/opt/homebrew/var/postgresql@13/pg_hba.conf
/opt/homebrew/var/postgresql@12/pg_hba.conf
/opt/homebrew/var/postgresql@15/pg_hba.conf
/opt/homebrew/var/postgres/pg_hba.conf
2
  • Indeed you were right. The only thing that is different is that the config has 5 columns, where the missing one relates to the IP+netmask that matches the rule. So this is the one for IPv4: # IPv4 host all all 127.0.0.1/32 trust
    – oligofren
    Jul 3, 2023 at 6:11
  • @oligofren because of -h 127.0.0.1. You don't need that argument, though, since Pg also uses a domain socket.
    – RonJohn
    Jul 3, 2023 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.