\n
\nwhere password is root
. I then get this error:
FATAL: password authentication failed for user "postgres"\n
\nI check the docker logs and I see
\nDETAIL: Role "postgres" does not exist.\n
\nI'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!
\n","author":{"@type":"Person","name":"OmegaNalphA"},"upvoteCount":12,"answerCount":4,"acceptedAnswer":{"@type":"Answer","text":"@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.
\n","author":{"@type":"Person","name":"OmegaNalphA"},"upvoteCount":19}}}Reputation: 710
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!
Upvotes: 12
Views: 20260
Reputation: 11
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.
Upvotes: 1
Reputation: 432
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.
Upvotes: 0
Reputation: 997
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
Upvotes: 0
Reputation: 710
@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.
Upvotes: 19