FATAL: role "root" does not exist

[kawaii]

I notice the following error in my logs appearing every 5 minutes or so:

FATAL:  role "root" does not exist

Whilst not harmful, and whilst the healthcheck is indeed working as intended (a docker ps displays a (healthy) status) is there any way to suppress this message? I assume this happens because the official PostgreSQL Docker images do not have a USER postgres declaration at the end of their Dockerfiles?

[peter-evans]

I'm not aware of this issue so I'll investigate and let you know if I find anything.

[kawaii]

Thanks Peter, for reference here is the PostgreSQL snippet from my docker-compose.yml file:

  postgres:
    image: postgres:10.0
    hostname: postgres
    container_name: postgres
    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      interval: 30s
      timeout: 30s
      retries: 3
    env_file:
      - prosody.env
    volumes:
      - ${PWD}/postgres/data/:/var/lib/postgresql/data/
    restart: always

[peter-evans]

I haven't confirmed it, but I think this comment could be the explanation. As you mentioned, if the PostgreSQL Docker image had a USER postgres declaration then on repeated restarts of the container it would use the correct user.

According to the linked issue it can be prevented by deleting the volume with docker-compose down -v and forcing it to recreate the database each time. However, if you need to preserve the state I'm not really sure what the best solution is. You could make your own PostgreSQL Docker image and add in the USER postgres declaration, I suppose.

[bakasmarius]

@kawaii I spent half of yesterday trying to find out the cause of the FATAL: role "root" does not exist issue.
It turns out you need to specify the username you actually use to connect to your database (let's use "admin" for example:

   test: ["CMD-SHELL", "pg_isready -U admin"]