Password authentication fails when publishing to a port other than 5432

[icleary5]

I am trying to make a docker-compose.yml that will publish port "5433:5432" so I can access it with psql on port 5433 on localhost. I set the POSTGRES_PASSWORD, but password authentication fails. Here is my journey:

Note: Between each test, I am stopping and removing all containers using docker compose down or docker ps -qa | xargs docker stop | xargs docker rm, depending on the situation.

I started with this compose file:

  pg:
    image: postgres
    ports:
      - '5433:5432'
    environment:
      POSTGRES_PASSWORD: pwd

I tried to connect with psql:

Password for user postgres:
psql: error: connection to server at "localhost" (::1), port 5433 failed: FATAL:  password authentication failed for user "postgres"

Hmm... I wonder if it works with the default port:

  pg:
    image: postgres
    ports:
      - '5432:5432'
    environment:
      POSTGRES_PASSWORD: pwd

I tried connecting to it:

Password for user postgres:
psql (15.2, server 15.3 (Debian 15.3-1.pgdg110+1))
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

Ok that worked! So the only difference is the published port number. What if I do this comparison outside of a compose file? $ docker run -d -e POSTGRES_PASSWORD=pwd -p 5432:5432 postgres

As expected, connecting to 5432 works just fine. No surprises here:

$ psql -p 5432 -U postgres
Password for user postgres:
psql (15.2, server 15.3 (Debian 15.3-1.pgdg110+1))
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

Now what happens if I change the published port to 5433? $ docker run -d -e POSTGRES_PASSWORD=pwd -p 5433:5432 postgres

Oh look! It fails here too:

Password for user postgres:
psql: error: connection to server at "localhost" (::1), port 5433 failed: FATAL:  password authentication failed for user "postgres"

So by this point I've established that in both contexts - docker compose and docker run - the failure depends only on the published port number. I thought I'd experiment with Docker's confusingly documented "expose" concept, which isn't supposed to do anything in this context, but I thought I'd give it a try. $ docker run -d -e POSTGRES_PASSWORD=pwd -p 5433:5432 --expose 5433 postgres

And sure enough, it still doesn't work:

Password for user postgres:
psql: error: connection to server at "localhost" (::1), port 5433 failed: FATAL:  password authentication failed for user "postgres"

I dunno, maybe it will do something in compose?

  pg:
    image: postgres
    ports:
      - '5432:5432'
    environment:
      POSTGRES_PASSWORD: pwd
    expose:
      - '5433'

Nope!

Password for user postgres:
psql: error: connection to server at "localhost" (::1), port 5433 failed: FATAL:  password authentication failed for user "postgres"

My understanding is that this publishing a postgres container's port to something other than the default is supposed to work. It's clear that the postgres container is listening on the published port. It's just rejecting the password if and only if the published port varies from the default.

Note: I am running Windows 11 Pro on an HP EliteBook 840 G6. I'm using the latest version of Docker Desktop and the latest version of the postgres image (15.3-1.pgdg110+1). I run my docker commands on Git Bash on Debian. Here is the system info for my Docker installation:

Client:
 Version:    24.0.2
 Context:    default
 Debug Mode: false
 Plugins:
  buildx: Docker Buildx (Docker Inc.)
    Version:  v0.10.5
    Path:     C:\Program Files\Docker\cli-plugins\docker-buildx.exe
  compose: Docker Compose (Docker Inc.)
    Version:  v2.18.1
    Path:     C:\Program Files\Docker\cli-plugins\docker-compose.exe
  dev: Docker Dev Environments (Docker Inc.)
    Version:  v0.1.0
    Path:     C:\Program Files\Docker\cli-plugins\docker-dev.exe
  extension: Manages Docker extensions (Docker Inc.)
    Version:  v0.2.19
    Path:     C:\Program Files\Docker\cli-plugins\docker-extension.exe
  init: Creates Docker-related starter files for your project (Docker Inc.)
    Version:  v0.1.0-beta.4
    Path:     C:\Program Files\Docker\cli-plugins\docker-init.exe
  sbom: View the packaged-based Software Bill Of Materials (SBOM) for an image (Anchore Inc.)
    Version:  0.6.0
    Path:     C:\Program Files\Docker\cli-plugins\docker-sbom.exe
  scan: Docker Scan (Docker Inc.)
    Version:  v0.26.0
    Path:     C:\Program Files\Docker\cli-plugins\docker-scan.exe
  scout: Command line tool for Docker Scout (Docker Inc.)
    Version:  v0.12.0
    Path:     C:\Program Files\Docker\cli-plugins\docker-scout.exe

Server:
 Containers: 1
  Running: 1
  Paused: 0
  Stopped: 0
 Images: 12
 Server Version: 24.0.2
 Storage Driver: overlay2
  Backing Filesystem: extfs
  Supports d_type: true
  Using metacopy: false
  Native Overlay Diff: true
  userxattr: false
 Logging Driver: json-file
 Cgroup Driver: cgroupfs
 Cgroup Version: 1
 Plugins:
  Volume: local
  Network: bridge host ipvlan macvlan null overlay
  Log: awslogs fluentd gcplogs gelf journald json-file local logentries splunk syslog
 Swarm: inactive
 Runtimes: io.containerd.runc.v2 runc
 Default Runtime: runc
 Init Binary: docker-init
 containerd version: 3dce8eb055cbb6872793272b4f20ed16117344f8
 runc version: v1.1.7-0-g860f061
 init version: de40ad0
 Security Options:
  seccomp
   Profile: builtin
 Kernel Version: 5.15.90.1-microsoft-standard-WSL2
 Operating System: Docker Desktop
 OSType: linux
 Architecture: x86_64
 CPUs: 8
 Total Memory: 3.732GiB
 Name: docker-desktop
 ID: 20e1f4a9-e438-43d9-a584-a23d2d13a3ac
 Docker Root Dir: /var/lib/docker
 Debug Mode: false
 HTTP Proxy: http.docker.internal:3128
 HTTPS Proxy: http.docker.internal:3128
 No Proxy: hubproxy.docker.internal
 Experimental: false
 Insecure Registries:
  hubproxy.docker.internal:5555
  127.0.0.0/8
 Live Restore Enabled: false

WARNING: No blkio throttle.read_bps_device support
WARNING: No blkio throttle.write_bps_device support
WARNING: No blkio throttle.read_iops_device support
WARNING: No blkio throttle.write_iops_device support

[tianon]

Hmm, something weird is happening here, but I'm not sure where! I tried reproducing with a --network=host container (so I don't need psql on my host, which is something I do not have :sweat_smile:), and I ran into it defaulting to the unix socket (somewhat expected) but adding -h made it work fine:

$ docker pull postgres
Using default tag: latest
latest: Pulling from library/postgres
Digest: sha256:31c9342603866f29206a06b77c8fed48b3c3f70d710a4be4e8216b134f92d0df
Status: Image is up to date for postgres:latest
docker.io/library/postgres:latest
$ docker run -d -e POSTGRES_PASSWORD=pwd -p 5433:5432 postgres
4ec13f8afff9df1d1f76ca5db5451061d97edb270f4a9f50a5d1fe86b7b430a2
$ docker logs --tail=2 4ec13f8afff9df1d1f76ca5db5451061d97edb270f4a9f50a5d1fe86b7b430a2
2023-06-05 18:44:09.269 UTC [62] LOG:  database system was shut down at 2023-06-05 18:44:09 UTC
2023-06-05 18:44:09.274 UTC [1] LOG:  database system is ready to accept connections
$ docker run -it --rm --network host --env PGPASSWORD=pwd postgres psql -p 5433 -U postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5433" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?
$ docker run -it --rm --network host --env PGPASSWORD=pwd postgres psql -h 127.0.0.1 -p 5433 -U postgres
psql (15.3 (Debian 15.3-1.pgdg110+1))
Type "help" for help.

postgres=# 

[icleary5]

Thanks for trying this @tianon, but I don't think you have reproduced the issue.

[tianon]

Yep, that's exactly what I'm saying -- I've reproduced your test case, but not the issue. :sweat_smile:

(At this point, my best guess is something related to Windows or Docker Desktop for Windows, but it's hard to say for sure.)

[yosifkit]

I am also unable to reproduce. 😞 This is using psql from a WSL2 Debian instance with the container running in Docker Desktop:

$ docker pull postgres
Using default tag: latest
latest: Pulling from library/postgres
...
Digest: sha256:31c9342603866f29206a06b77c8fed48b3c3f70d710a4be4e8216b134f92d0df
Status: Downloaded newer image for postgres:latest
docker.io/library/postgres:latest
$ docker run -d -e POSTGRES_PASSWORD=pwd -p 5433:5432 postgres
a02b8eeb5dac394ae336bacb6a275c7b53b41860dc41b1d1e32607a95aef17f0
$ docker logs --tail=2 a02b8eeb5dac394ae336bacb6a275c7b53b41860dc41b1d1e32607a95aef17f0
2023-06-05 20:55:45.029 UTC [62] LOG:  database system was shut down at 2023-06-05 20:55:44 UTC
2023-06-05 20:55:45.032 UTC [1] LOG:  database system is ready to accept connections
$ PGPASSWORD=pwd psql -h 127.0.0.1 -p5433 -U postgres
psql (13.11 (Debian 13.11-0+deb11u1), server 15.3 (Debian 15.3-1.pgdg110+1))
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
Type "help" for help.

postgres=#
\q

I also installed the client binaries in Windows and those work both from the "SQL shell (pqsl)" and via running the bin\psql binary in the installation directory from powershell:

# "SQL Shell (psql)
Server [localhost]:
Database [postgres]:
Port [5432]: 5433
Username [postgres]:
Password for user postgres:
psql (15.3)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

PS C:\Program Files\PostgreSQL\15> .\bin\psql.exe -p 5433 -U postgres
Password for user postgres:
psql (15.3)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

😕 Have you tried adding -h 127.0.0.1 to your psql line? Is there a conflicting PostgreSQL running in Windows that is being connected to instead?

[Gruummy]

Have you checked your hba.conf ?

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

i do not know what is the default contend of it .. but probably you have uncommon network setup / network range and simply this basic protection layer blocks your request to connect ?