"connection to server on socket ... failed" when using image with Docker Compose

[christianblueskylabs]

When using the image with Docker Compose and accessing it with docker exec -it <container name> bash and psql, you will get this error message:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

I have tried to isolate the problem with the following:

1. Using docker run to test and see if I can run psql just fine
2. Using the image with a docker-compose.yml of mine and psql sports the error.
3. Using the image as the only container in the docker-compose.yml and psql still sports the error
4. Using the postgres [13] image and install postgis myself.

For #1, I ran the following command:

docker run --name some-postgis --net=aow-trips_sail -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_USER=woopwoop -e PGPASSWORD=powpow -d postgis/postgis:13-master

Running psql just works fine.

I won't go into detail with #2 as #3 might be more interesting. For #3, I had the following docker-compose.yml:

version: '3'
services:
    pt-pgsql:
        image: postgis/postgis:14-master
        ports:
            - '5432:5432'
        environment:
            PGPASSWORD: 'kylie'
            POSTGRES_DB: 'test_db'
            POSTGRES_USER: 'notroot'
            POSTGRES_PASSWORD: 'kylie'
        volumes:
            - 'pgtest-pgsql:/var/lib/postgresql/data'
        networks:
            - pgtest
        healthcheck:
            test: ["CMD", "pg_isready", "-q", "-d", "test_db", "-U", "notroot"]
            retries: 3
            timeout: 5s
networks:
    pgtest:
        driver: bridge
volumes:
    pgtest-pgsql:
        driver: local

#4 works completely fine, but I won't go into detail into it as there are heaps of files involved. If any of you would like to have it, feel free to ask.

I have tested this with both 14-master and 13-master.

Being able to access psql from the container is very important as you will need this capability to export and import [very large] databases.

[ImreSamu]

Hi @christianblueskylabs ,

Thanks for opening this issue!

role "root" does not exist
... Being able to access psql from the container is very important

your issue is probably related to:

• https://stackoverflow.com/questions/60193781/postgres-with-docker-compose-gives-fatal-role-root-does-not-exist-error

or you can find similar issues in the upstream docker Postgres repo:

• https://github.com/docker-library/postgres/search?q=role+does+not+exist&type=issues

---

IMHO: You have to add the database connection parameters;

For example: based on your docker-compose example:

• docker exec -it c68e3e8e27bd bash
  • psql -d test_db -U notroot <----- it is working for me.

if this is not working for you,
probably you have already initialized the PostgreSQL database with the previous parameters (different user, ...)

• so you have to remove the pgtest-pgsql volume - before re-testing your settings with any change!

and please check the PostgreSQL logs !

I have tested this with both 14-master and 13-master.

if you are first time user .. my recommendation is 14-3.2 or 13-3.2

• The 14-master is the PostGIS development version ( so not released ! only for testing strange errors )
• The 14-3.2 uses Debian based PostgreSQL 14 + latest released PostGIS 3.2 <-- this is the recommended !

important: This repo is only the extension of the official Postgres docker image;
You can find the docker PostgreSQL documentation here ( https://github.com/docker-library/docs/blob/master/postgres/README.md )
and everything should work with the postgis/postgis:14-3.2 image

[christianblueskylabs]

HI @ImreSamu ,

Thanks for chiming in. Prior to your comment, I have tried everything of those you have already outlined, some not really 100% relevant except for pinning the version. I have to say for some reason that worked when I paid very close attention to the user/group ownership of the socket. I must have indeed just missed a few things. Thanks, closing issue now.