'postgres with docker compose gives FATAL: role "root" does not exist error

I'm trying to create a simple demo with postgres on a local windows machine with docker desktop.
This is my yaml docker compose file named img.yaml:

version: '3.6'

services:

    postgres-demo:
      image: postgres:11.5-alpine
      container_name: postgres-demo
      environment:
        - POSTGRES_USER=postgres
        - POSTGRES_PASSWORD=Welcome
        - POSTGRES_DB=conference_app
      healthcheck:
        test: ["CMD-SHELL", "pg_isready -U postgres"]
        interval: 10s
        timeout: 5s
        retries: 5
      ports:
        - 5432:5432
      volumes:
        - .:/var/lib/my_data
      restart: always

I'm running it using the command: docker-compose -f img.yaml up And get the following output:

Starting postgres-demo ... done
Attaching to postgres-demo
postgres-demo    | 2020-02-12 17:07:46.487 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres-demo    | 2020-02-12 17:07:46.487 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres-demo    | 2020-02-12 17:07:46.508 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres-demo    | 2020-02-12 17:07:46.543 UTC [18] LOG:  database system was shut down at 2020-02-12 17:07:10 UTC
postgres-demo    | 2020-02-12 17:07:46.556 UTC [1] LOG:  database system is ready to accept connections

And then, opening bash into the container with the command: docker exec -it d47056217a97 bash
I want to watch the databases in container so I run in the bash the command: psql \dt
And get the error: psql: FATAL: role "root" does not exist.
Trying to create the database using the command: psql> create database conference_app; gives the error: psql: FATAL: role "conference_app" does not exist.
I'm puzzled. What am I doing wrong? Is my yaml missing something?

postgresqldocker-compose


Solution 1:[1]

If you don’t specify the PGUSER environment variable, then psql will assume you want to use the current OS user as your database user name. In this case, you are using root as your OS user, and you will attempt to log in as root, but that user doesn’t exist in the database.

You’ll need to either call psql -U postgres, or su - Postgres first

See also the postgresql documentation

Solution 2:[2]

I specified user: postgres for the service in docker-compose file. Then deleted the existing container to re-spin it with the next docker-compose up execution. Container came up with the user "postgres", so you just need psql -l from there onwards(don't need -U flag)

This was my docker-compose file

version: '3.1'
services:
  db:
    image: postgres:12.6-alpine
    restart: always
    container_name: postgres12_6
    user: postgres
    environment:
      - "POSTGRES_PASSWORD=postgres"
      - "ES_JAVA_OPTS=-Xms1024m -Xmx3072m"
    networks:
      - esnet
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

networks:
  esnet:

Solution 3:[3]

The container assumes that you are trying to connect to db with root user(current user) since you dont specify the user and database name on docker exec.

Following should work:

docker exec -it <container_id/container_name> psql -U <user_name> <database_name>
docker exec -it d47056217a97 psql -U postgres conference_app

Solution 4:[4]

in my case, I was using windows 10, I tried everything I could but still not works.

finally, I removed all the related docker image, docker container, local host folder, and restart windows.

and everything goes well

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Adi Sivasankaran
Solution 2
Solution 3 ibrahimkesici
Solution 4 Siwei

Related Questions