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

Question

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?

richyen · Answer 1 · 2022-10-03T17:06:59.090

103

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

UPDATE: Someone suggested changing PGUSER to POSTGRES_USER -- this is actually incorrect. Postgres looks for PGUSER in the environment, but if you're using Docker, you'll tell Docker the correct user by using POSTGRES_USER, which gets assigned to PGUSER -- see the entrypoint source code

edited Oct 03 '22 at 17:06

answered Feb 12 '20 at 17:45

richyen

8,114
4
13
28

1

I managed to do it not from the image bash but with the command: docker exec -it postgres-demo psql -U postgres -d conference_app -f /var/lib/my_data/insert_data.sql – Yaron Feb 13 '20 at 09:50
@Yaron Great! Same idea :) – richyen Feb 14 '20 at 01:39
4

I used it like this: `environment: PGUSER: postgres` and it worked for me. – thiagoBarbosa48 Dec 21 '22 at 20:55

Raj · Answer 2 · 2021-06-07T06:18:13.517

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:

This solution seems to be the most simple to understand . Thanks! — Ananta K Roy, Jul 27 '21 at 12:23

score 14 · Answer 3 · answered Dec 22 '20 at 22:24

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

score 5 · Answer 4 · answered Jul 12 '23 at 09:53

If you also specify a database other than the default you should also include this in the pg_ready cmd eg:

    healthcheck:
      test: ["CMD-SHELL", "pg_isready -d db-name -U db-user"]

In the above snippet my db name is db-name and using a user db-user. I needed to specify this despite also setting the POSTGRES_DB & POSTGRES_USER env vars.

score 1 · Answer 5 · answered Apr 27 '22 at 04:55

1

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

answered Apr 27 '22 at 04:55

Siwei

19,858
7
75
95

Christopher Peisert · Answer 6 · 2023-07-24T20:28:57.140

Update for docker-compose v2+

Error 1 - FATAL: role "root" does not exist

As stated in richyen's answer, set the PGUSER environment variable.

NOTE: POSTGRES_USER by itself will not work.

Error 2 - FATAL: database "X" does not exist

In this case, X is usually the value of the PGUSER environment variable.

After setting the PGUSER environment variable, it fixes the original error, but introduces a new error:

FATAL: database "X" does not exist

Solution

The fix is to define the database host as its own Docker network IP address as in:

services:
  server:
    depends_on:
      db:
        condition: service_healthy
  db:
    image: postgres:latest
    healthcheck:
      test: "pg_isready -h db"
      interval: 3s
      timeout: 5s
      retries: 5

Here the -h db refers to the db service's own Docker IP address. With this configuration, there is often no need to pass additional flags to pg_isready.

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

6 Answers6

Update for docker-compose v2+

Error 1 - FATAL: role "root" does not exist

Error 2 - FATAL: database "X" does not exist

Solution

Linked