I was scratching my head for quite sometime about why I was able to do

; psql -U neo -h localhost -d postgres -p 5432
psql (14.13 (Homebrew))
Type "help" for help.

postgres=#

without any password but the following kept failing

; psql -U neo -h 127.0.0.1 -d postgres -p 5432
Password for user neo:
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "neo"

I was trying to setup a local webapp (confluence) and it kept asking me db credentials and failing on both localhost and 127.0.0.1.

It was driving me bonkers!

As any sane person, I tried to look at my postgres config file

/opt/homebrew/var/postgresql@14/pg_hba.conf

# 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

Postgres should always allow empty password locally. Why is it even asking for a password ?

I tried resetting password multiple times connecting to psql -h localhost and then reconnecting with the same password at psql -h 127.0.0.1.

Failed.

Anyway, since confluence is rejecting empty password, I tried setting up a password by connecting to psql -h localhost and used the same connection string (localhost) and password in confluence.

Failed.

Makes you wonder, is confluence looking at the same postgres instance I’m looking at ?
Who all are listening at 5432 ?

; lsof -i :5432
COMMAND    PID USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
ssh      39802  neo   42u  IPv4 0xa989e41f6513c271      0t0  TCP localhost:postgresql (LISTEN)
postgres 52660  neo    7u  IPv6 0xa989e42433f89de1      0t0  TCP localhost:postgresql (LISTEN)

Two processes !?

; ps -e | grep 39802
39802 ??         0:01.44 ssh: /Users/neo/Library/Application Support/rancher-desktop/lima/0/ssh.sock [mux]

A docker container ! Let’s verify,

; docker container list | less -S
CONTAINER ID   IMAGE                                COMMAND                  CREATED        STATUS                    PORTS                              >
f52bdadf5549   postgres:16                          "docker-entrypoint.s…"   2 weeks ago    Up 3 days (healthy)   127.0.0.1:5432->5432/tcp           >

This was from one of my project’s docker compose file, running for quite some time. Both docker and my host postgresql was listening at 5432. How is this possible ?

Since docker was running for quite sometime and I just ran postgres, the juice lies in postgres logs

/opt/homebrew/var/log/[email protected]

2025-02-10 16:40:02.716 IST [55542] LOG:  starting PostgreSQL 14.13 (Homebrew) on aarch64-apple-darwin23.4.0, compiled by Apple clang version 15.0.0 (clang-1500.3.9.4), 64-bit
2025-02-10 16:40:02.723 IST [55542] LOG:  listening on IPv6 address "::1", port 5432
-------------------------------------------------------------------------------------------------------------------------------------------
| 2025-02-10 16:40:02.723 IST [55542] LOG:  could not bind IPv4 address "127.0.0.1": Address already in use                               |
| 2025-02-10 16:40:02.723 IST [55542] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.    |
-------------------------------------------------------------------------------------------------------------------------------------------
2025-02-10 16:40:02.724 IST [55542] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-02-10 16:40:02.792 IST [55546] LOG:  database system was shut down at 2025-02-10 16:39:21 IST
2025-02-10 16:40:02.810 IST [55542] LOG:  database system is ready to accept connections

Postgres tried to bind to 127.0.0.1:5432 and correctly failed since docker had already claimed that BUT silently continued as if everything is all ok. Had it errored here, I’d have saved some headache.

This made the following bindings possible

docker   -> 127.0.0.1:5432
postgres -> [::1]:5432

psql resolved localhost to [::1] and sucessfully connected to my host postgres instance but with 127.0.0.1 it pointed to docker’s instance whose password I neither set nor know.

Why was confluence failing ?

For some reason, localhost in jdbc resolved to 127.0.0.1. This also happened in dbeaver.

I stopped the docker container AND restarted postgres, everything was working as expected

; docker container stop f52bdadf5549
f52bdadf5549

; brew services restart postgresql@14
Stopping `postgresql@14`... (might take a while)
==> Successfully stopped `postgresql@14` (label: homebrew.mxcl.postgresql@14)
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)

; psql -U neo -h 127.0.0.1 -d postgres -p 5432
psql (14.13 (Homebrew))
Type "help" for help.

postgres=#
\q

; psql -U neo -h localhost -d postgres -p 5432
psql (14.13 (Homebrew))
Type "help" for help.

postgres=#
\q