Can't connect to container using docker exec -it

[BigBoulard]

What is the bug or the crash?

> docker exec -i 71530cf446fd3b0e7752fd14d2290ee1fa939e19760a0ed47f79ab2f0846f759 psql -U postgres password
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

Steps to reproduce the issue

postgres:
    image: kartoza/postgis@sha256:b0a7e256fca6c9aceca6eacac531dfb38c982390e40ebfafb510b0e7fbc8e67c
    hostname: postgres
    ports:
      - 5432:5432
    networks:
      - postgres-net
    environment:
      - POSTGRES_USER=${PGUSER}
      - POSTGRES_PASS=${PGPASSWORD}
    volumes:
      - postgres-vol:/var/lib/postgresql/data

Versions

kartoza/postgis@sha256:b0a7e256fca6c9aceca6eacac531dfb38c982390e40ebfafb510b0e7fbc8e67c

Additional context

I need to restore a dump from a previous install of a PostGIS DB postgis/postgis:latest but I changed my laptop to an M2 so I need an ARM64 build to avoid performance issues and potential bugs. So I just did a dump like this:

docker exec -t CONTAINER_ID pg_dumpall -c -U postgres | gzip > dump_`date +%Y-%m-%d"_"%H:%M:%S`.sql.gz

and then tried to restore it like this

gunzip dump_xxxxxx.sql.gz
cat dump_xxxxxx.sql | docker exec -i CONTAINER_ID psql -U postgres

Then I realized I couldn't connect with the docker exec -it. I also have a pgadmin container connected to this postgis container ... so I don't get it

[NyakudyaA]

Why don't you try connecting or restoring using the PGUSER and password as opposed to postgres user.

Alternatively I would also log into the container and check if exec is working

[BigBoulard]

Why don't you try connecting or restoring using the PGUSER and password as opposed to postgres user.

Alternatively I would also log into the container and check if exec is working

Sorry for this late reply. Actually, I'm passing postgres and password as PGUSER and PGPASSWORD vars in the docker compose up command. Then, I tried to connect: docker exec -i 4458734..54354 psql -U postgres password without success.

The weird thing is that I can connect the postgres user through a pgadmin docker instance...I connect to pgadmin with another dedicated user, then I register the server using the instance name and the postgres user with password.

In the postgres instance logs, I can see:

LOG: provided user name (postgres) and authenticated user name (root) do not match
FATAL: Peer authentication failed for user "postgres"
DETAIL: Connection matched pg_hba.conf line 90: "local       all     postgres    peer"

[NyakudyaA]

Even though you start the container with the password, when you run the psql command you will need to export the password in the context of the shell.

docker exec -i postgis-db-1 psql -U docker -h localhost -p 5432

This postgres user is it the default super user that comes by default in postgres.

[BigBoulard]

Thank you so much!

So to connect to the container from the terminal.

docker exec -i -e PGPASSWORD=password CONTAINER_NAME_OR_ID psql -U postgres -h localhost -p 5432

and to restore the dump:

cat dump_20230831.sql | docker exec -i -e PGPASSWORD=password CONTAINER_NAME_OR_ID psql -U postgres -h localhost -p 5432