Postgres and Docker Compose; password authentication fails and role 'postgres' does not exist. Cannot connect from pgAdmin4

Question

I have a docker-compose that brings up the psql database as below, currently I'm trying to connect to it with pgAdmin4 (not in a docker container) and be able to view it. I've been having trouble authenticating with the DB and I don't understand why.

docker-compose

version: "3"

services:
  # nginx and server also have an override, but not important for this q.
  nginx:
    ports:
      - 1234:80
      - 1235:443
  server:
    build: ./server
    ports:
      - 3001:3001 # app server port
      - 9230:9230 # debugging port
    env_file: .env
    command: yarn dev
    volumes:
      # Mirror local code but not node_modules
      - /server/node_modules/
      - ./server:/server
  
  database:
    container_name: column-db
    image: 'postgres:latest'
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: postgres # The PostgreSQL user (useful to connect to the database)
      POSTGRES_PASSWORD: root # The PostgreSQL password (useful to connect to the database)
      POSTGRES_DB: postgres # The PostgreSQL default database (automatically created at first launch)
    volumes:
      - ./db-data/:/var/lib/postgresql/data/

networks:
  app-network:
    driver: bridge

I do docker-compose up then check the logs, and it says that it is ready for connections. I go to pgAdmin and enter the following:

where password is root. I then get this error:

FATAL:  password authentication failed for user "postgres"

I check the docker logs and I see

DETAIL:  Role "postgres" does not exist.

I'm not sure what I'm doing wrong, according to the docs the super user should be created with those specifications. Am I missing something? Been banging my head against this for an hour now. Any help is appreciated!

I also have tried going into the docker container and checking postgres, but it says I can't run it in root mode — OmegaNalphA
– OmegaNalphA, Commented May 12, 2022 at 21:11
Also keep seeing this error under it as well Connection matched pg_hba.conf line 100: "host all all all scram-sha-256" — OmegaNalphA
– OmegaNalphA, Commented May 12, 2022 at 21:18
Since you are using a mapped volume, the database is only created if that volume is empty. Otherwise it reuses the database it finds there. If that database was created under a different setting of POSTGRES_USER, then it won't have the user you are expecting. — jjanes
– jjanes, Commented May 13, 2022 at 2:39
@jjanes thanks for the reply! I’m afk right now but will try that out when I get back, I think this will help since the volume part is the only difference from the command line docker command. That’s what I get for following docker-compose instructions. — OmegaNalphA
– OmegaNalphA, Commented May 14, 2022 at 3:10

OmegaNalphA · Accepted Answer · 2022-05-15 18:54:22Z

19

@jjanes solved it in a comment, I had used a mapped volume and never properly set up the db. Removed the volume and we're good to go.

answered May 15, 2022 at 18:54

OmegaNalphA

7001 gold badge6 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pooy.H · Accepted Answer · 2023-11-29 09:56:12Z

1

The reason is @jjanes's comment:

Since you are using a mapped volume, the database is only created if that volume is empty. Otherwise, it reuses the database it finds there. If that database was created under a different setting of POSTGRES_USER, then it won't have the user you are expecting.

There are two ways to fix this:

Modify the configuration at the bottom of the pg_hba.conf file:

Change:

# host all all all scram-sha-256
 host all all all md5

Alternatively, when starting the Docker container, modify the configuration by adding:

POSTGRES_INITDB_ARGS="--auth-host=scram-sha-256"

This makes the generated passwords default to using scram-sha-256 encryption for authentication.

answered Nov 29, 2023 at 9:56

Pooy.H

111 bronze badge

Comments

lcompare · Accepted Answer · 2023-05-06 22:25:52Z

0

In my case it was a permissions issue.
The owner and group of the directory was changed by mistake.
I changed it back to my user (lcompare) and group (lcompare) and it worked.

sudo chown lcompare:lcompare db

answered May 6, 2023 at 22:25

lcompare

99713 silver badges11 bronze badges

Comments

Vanya Usalko · Accepted Answer · 2023-10-23 05:57:14Z

0

Sorry for mine five cents. I've struggled with the same issue, on the docker image postgres:14.7-alpine. Although clear a volume didn't help. But I've noticed that comment ports in the docker-compose.yaml for postgres service and clear volume helps me. Perhaps it's issue in postgres database initialization logic.

    # ports:
    #  - 5432:5432

The ports only needs if you try to open port 5432 on the host for internal application connection it's doesn't necessary.

answered Oct 23, 2023 at 5:57

Vanya Usalko

4329 silver badges13 bronze badges

Collectives™ on Stack Overflow

Postgres and Docker Compose; password authentication fails and role 'postgres' does not exist. Cannot connect from pgAdmin4

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related