Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Version postgres:9.4 doesn't create role/password #41

Closed
pandeiro opened this issue Dec 18, 2014 · 42 comments
Closed

Version postgres:9.4 doesn't create role/password #41

pandeiro opened this issue Dec 18, 2014 · 42 comments

Comments

@pandeiro
Copy link

Setting POSTGRES_USER and POSTGRES_PASSWORD on the 9.4 tag doesn't create the role and password for me. (Previous versions work.)

@yosifkit
Copy link
Member

That is a bit strange, I'm not having any problems here and I just did a docker pull -a to be sure. I would say docker version or storage driver could be a problem, but you say the other versions work fine and the files we use in each are almost identical (the entrypoint setting the user/password is identical).

@pandeiro
Copy link
Author

I'm still having the problem. Just to clarify, the expected behavior is that setting those variables will create the role and password and restrict login to those credentials. When I pass the variables and then try to login to the container, Postgres says the role doesn't exist.

Edit: It's hard to put a minimal case because a client like psql must be present on the host machine to test. But if all else fails to detect this, I can try something.

In case this helps, docker version:

Docker version 1.4.0, build 4595d4f

and docker info:

Containers: 63
Images: 566
Storage Driver: devicemapper
 Pool Name: docker-8:3-9965829-pool
 Pool Blocksize: 65.54 kB
 Data file: /home/user/data/docker/devicemapper/devicemapper/data
 Metadata file: /home/user/data/docker/devicemapper/devicemapper/metadata
 Data Space Used: 13.72 GB
 Data Space Total: 107.4 GB
 Metadata Space Used: 25.68 MB
 Metadata Space Total: 2.147 GB
 Library Version: 1.02.92 (2014-11-28)
Execution Driver: native-0.2
Kernel Version: 3.17.6-1-ARCH
Operating System: Arch Linux
CPUs: 4
Total Memory: 7.716 GiB
Name: userhost
ID: CWFN:OTRR:L5GE:VFDJ:DJ26:XQSD:KQSP:ZC6V:LHJU:CDFS:B2ZL:YZU6

@yosifkit
Copy link
Member

Yes it creates the user specified as a superuser with the password and creates a database for them. Here is how I'm testing it:

$ docker run -it --rm --name pg1 -e POSTGRES_PASSWORD=12345 -e POSTGRES_USER=bob postgres:9.4

$ docker run -it --link pg1:postgres --rm postgres:9.4 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U bob'

@pandeiro
Copy link
Author

I just did that and get psql: FATAL: role "bob" does not exist.

Edit: I tried on another machine and it does work. Clearly something local here -- sorry. Feel free to close this.

@ferrouswheel
Copy link

I get the same issue

$ docker version
Client version: 1.3.0
Client API version: 1.15
Go version (client): go1.3.3
Git commit (client): c78088f
OS/Arch (client): darwin/amd64
Server version: 1.3.0
Server API version: 1.15
Go version (server): go1.3.3
Git commit (server): c78088f

Not sure how the logic of changing role/password could be version dependent. But happy to help to try and debug why some of us get this issue.

I've tried setting POSTGRES_USER/POSTGRES_PASSWORD on a container with an existing db volume, and with a blank data volume in case the user/password is only set up the first time things get initialised (the documentation is unclear if setting user/password will be respected on a container with an existing data volume).

@ferrouswheel
Copy link

Tracked this down to the docker-entrypoint.sh in my pulled image being different to the one in this repo:

#!/bin/bash
set -e

if [ "$1" = 'postgres' ]; then
    chown -R postgres "$PGDATA"

    if [ -z "$(ls -A "$PGDATA")" ]; then
        gosu postgres initdb

        sed -ri "s/^#(listen_addresses\s*=\s*)\S+/\1'*'/" "$PGDATA"/postgresql.conf

        { echo; echo 'host all all 0.0.0.0/0 trust'; } >> "$PGDATA"/pg_hba.conf

        if [ -d /docker-entrypoint-initdb.d ]; then
            for f in /docker-entrypoint-initdb.d/*.sh; do
                [ -f "$f" ] && . "$f"
            done
        fi
    fi

    exec gosu postgres "$@"
fi

exec "$@"

And turns out I was using the version tagged 9.3 in my config.

@liamdawson
Copy link

I had this issue, then realised I had another postgres working on my machine. Oops. Make sure you check that!

@Alexis-benoist
Copy link

I'm having the same issue.

@liamdawson: for you the problem was that postgres was running on your machine or installed?

How did you solve this issue?

@liamdawson
Copy link

@Alexis-benoist I had postgres actually running on my host, so it took precedence over the version run in Docker.

@stabenfeldt
Copy link

This is how you start the docker postgres image and access:

$ docker run -it --rm --name pg1 -p 5432:5432 -e POSTGRES_PASSWORD=12345 -e POSTGRES_USER=bob postgres:9.4

$ psql -h dockerhost  -U bob

The incoming ports was not defined in @yosifkit's example. :-)
-p 5432:5432

@tibo
Copy link

tibo commented Dec 28, 2015

I've got the same issue using docker-compose
the POSTGRES_USER environment variable doesn't seems to create the user properly?

@yosifkit
Copy link
Member

@tibo, with docker-compose, it tries to preserve volumes between runs; since the user is only created on a new database, you probably need to docker-compose rm -v database to delete the container and associated volume.

@tibo
Copy link

tibo commented Dec 28, 2015

@yosifkit good call. setting POSTGRES_USER at the creation of the container works.
Thanks!

@servomac
Copy link

I have the same problem with docker-compose and an specified POSTGRES_USER:

 postgres:
     image: postgres:9.4.1
     environment:
         - POSTGRES_PASSWORD=supersecret
         - POSTGRES_USER=username
         - POSTGRES_DB=dbname

Results in

$ psql -U username
psql: FATAL:  role "username" does not exist

It works with the 9.5 tag.

@jmerkow
Copy link

jmerkow commented Jul 31, 2016

I have this problem with 9.5 tag. I am running it from OS X (docker-machine with virtual box drivers). The same files do no have issues running from a ubuntu 14.04 machine.
Perhaps, this has something to do with the host os for docker?

@yosifkit
Copy link
Member

yosifkit commented Aug 1, 2016

@servomac or @jmerkow, are you using docker-compose as well? Any environment variable to setup the user, password, and database are only done on the first initialization of the database. If you already ran it once and are just restarting to apply new variables then compose will start a new container that reuses the already initialized volume. See my comment above.

@villelahdenvuo
Copy link

@yosifkit I also ran into this problem. But the thing is that I tried deleting and recreating the volumes/container/images and starting from scratch, but still I can't get it to create the user with compose. Before it has been working fine and I haven't touched my docker-compose.yml or Dockerfiles.

A quick manual fix though: docker-compose exec db psql -h localhost -U postgres -c "CREATE USER user WITH PASSWORD 'password';"

@villelahdenvuo
Copy link

I figured out the problem, by reading the logs... I had set the PG* env variables to make psql login automatically and of course that broke the init script that uses psql. That's why the problem occurred now when I reinitialized the image.

@genomics-geek
Copy link

Also another issue might be that you have hanging volumes. I had this same issue and I couldn't figure out why. I then figured out it had something to do with volumes and the problem was resolved after I ran this command: docker volume rm $(docker volume ls -qf dangling=true)

@joaoalf
Copy link

joaoalf commented Oct 24, 2016

I have the same issue using docker-compose. I tried in 2 different machines (MacOS + parallels docker machine and Fedora24). If I create the postgres container manually it works.

@yosifkit
Copy link
Member

Related comment: #203 (comment).

@chespinoza
Copy link

@yosifkit now to delete volumes: docker-compose down -v

@CharlesDerek
Copy link

Watch this video on youTube,
It's lot different following steps from a video.
Worked easy for me.

@TigerC10
Copy link

I encountered the issue due to a mismatch between the$POSTGRES_DB and $POSTGRES_USER variables. By default, psql will attempt to set the database to the same name as the user logging in, so when there is a mismatch between the variables it fails with an error along the lines of psql: FATAL: database "root" does not exist

I had to edit the init script that I was writing to include the -d "$POSTGRES_DB" option like so:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$POSTGRES_DB" <<-EOSQL
	CREATE USER docker;
	CREATE DATABASE docker;
	GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

@davetapley
Copy link

To close the loop I also provided @yosifkit 's 'delete the volume' approach as an answer on Stackoverflow (although at the time I answered I didn't know about this issue).

@mwthink
Copy link

mwthink commented Mar 7, 2018

I'm still experiencing this issue on postgres:9.6.8-alpine. I'm running the container via Kubernetes and have env values set as POSTGRES_USER=myuser, POSTGRES_PASSWORD=mypass, and POSTGRES_DB=myuser

Supposedly the POSTGRES_DB value should inherit from POSTGRES_USER, but I set it to the same value to be extra safe. Still experiencing the same issues as everyone else. Running commands inside the container work when using the postgres user.

@yosifkit
Copy link
Member

yosifkit commented Mar 7, 2018

@mwthink, that would be because any connection via localhost is trusted without a password (as long as the username matches):

# pg_hba.conf (when setting pass via env vars)
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host all all all md5

Here is the code that is used to setup the initial user and pg_hba.conf (note that if you specify another user with POSTGRES_USER, then the default postgres user is not deleted or changed):

if [ "$POSTGRES_PASSWORD" ]; then
pass="PASSWORD '$POSTGRES_PASSWORD'"
authMethod=md5
else
# The - option suppresses leading tabs but *not* spaces. :)
cat >&2 <<-'EOWARN'
****************************************************
WARNING: No password has been set for the database.
This will allow anyone with access to the
Postgres port to access your database. In
Docker's default configuration, this is
effectively any other container on the same
system.
Use "-e POSTGRES_PASSWORD=password" to set
it in "docker run".
****************************************************
EOWARN
pass=
authMethod=trust
fi
{
echo
echo "host all all all $authMethod"
} >> "$PGDATA/pg_hba.conf"
# internal start of server in order to allow set-up using psql-client
# does not listen on external TCP/IP and waits until start finishes
PGUSER="${PGUSER:-postgres}" \
pg_ctl -D "$PGDATA" \
-o "-c listen_addresses='localhost'" \
-w start
file_env 'POSTGRES_USER' 'postgres'
file_env 'POSTGRES_DB' "$POSTGRES_USER"
psql=( psql -v ON_ERROR_STOP=1 )
if [ "$POSTGRES_DB" != 'postgres' ]; then
"${psql[@]}" --username postgres <<-EOSQL
CREATE DATABASE "$POSTGRES_DB" ;
EOSQL
echo
fi
if [ "$POSTGRES_USER" = 'postgres' ]; then
op='ALTER'
else
op='CREATE'
fi
"${psql[@]}" --username postgres <<-EOSQL
$op USER "$POSTGRES_USER" WITH SUPERUSER $pass ;
EOSQL

@sk33z3r
Copy link

sk33z3r commented Mar 20, 2018

Encountering this issue with the image tagged latest, which at the time of writing is 10.3.

I have tried on two separate Ubuntu servers:

  • Ubuntu 14.04, has a postgreSQL instance running on the system in addition to the container
  • Ubuntu 16.04, has no databases and is a fresh installation with only docker and docker-compose installed.

I build the container like so (I've also tried with docker-compose.yml):

sudo docker run -d \
        --name $sqlnm \
        -h $dbhost \
        --dns-search $dbdomain \
        --net $network \
        --ip $sqlIP \
        -p $sqlPort:5432 \
        --restart always \
        -e POSTGRES_USER=$dbusr \
        -e POSTGRES_PASSWORD=$dbusrpwd \
        -e POSTGRES_DB=$dbname \
        -v /data/$sqlnm/data:/var/lib/postgresql/data:rw \
        -v /data/$sqlnm/mnt:/mnt:rw \
        -v /etc/localtime:/etc/localtime:ro \
        $sqlPull

After the initial run command (with no pre-existing bind directories or even images), I can get into bash for the container, and echo the $POSTGRES_PASSWORD and $POSTGRES_USER variables to see that they are indeed being set by the run command as I would expect with my specified variables. However, when I attempt to connect to the box remotely using my set username and password, I get the notice that the user does not exist. If I attempt to login via the default postgres user, then the container's logs tell me there is no password assigned to the user.

2018-03-20 16:24:17.939 EDT [76] FATAL:  password authentication failed for user "postgres",
2018-03-20 16:24:17.939 EDT [76] DETAIL:  User "postgres" has no password assigned.

I know that there is nothing wrong with the remote connection, as I can see movement in the logs every time I connect, and it tells me when the user doesn't exist in the server's logs as well as on client machine. This appears to purely be an issue with how the database is being initialized, especially since the container has those environment variables set. I'm not sure what else to dive into, as the entry-point.sh appears to be grabbing the variables on lines 65-87, but I am available for some troubleshooting if there are any suggestions.

file_env 'POSTGRES_PASSWORD'
if [ "$POSTGRES_PASSWORD" ]; then
pass="PASSWORD '$POSTGRES_PASSWORD'"
authMethod=md5
else
# The - option suppresses leading tabs but *not* spaces. :)
cat >&2 <<-'EOWARN'
****************************************************
WARNING: No password has been set for the database.
This will allow anyone with access to the
Postgres port to access your database. In
Docker's default configuration, this is
effectively any other container on the same
system.
Use "-e POSTGRES_PASSWORD=password" to set
it in "docker run".
****************************************************
EOWARN
pass=
authMethod=trust
fi

@yosifkit
Copy link
Member

@sk33z3r, your setup looks correct. The postgres user will exist but not have a password (thus only usable via container localhost) since you specified -e POSTGRES_USER=$dbusr. How exactly are you connecting? Does this work to get a psql shell?

$ docker run -it --rm --net $network -e PGPASSWORD=$dbusrpwd postgres psql --host $sqlnm --username $dbusr

Is data/$sqlnm/data empty on first start; see #203 (comment).

@sk33z3r
Copy link

sk33z3r commented Mar 20, 2018

@yosifkit I knew this would happen! I apologize for cluttering this issue up... I was misreading the logs. I'm getting an error that the database with the name of $dbusr doesn't exist, not that the user didn't exist... when you asked how I was connecting it triggered me to look into it a little more deeply.

I was doing (from the client machine) psql -h "$dbhost.$dbdomain" -p $sqlPort -U $dbusr -W, which was trying to access a database named $dbusr because the -W arg is empty, but my database is actually named $dbname.

Now that I've educated myself more, running this command is working: psql -h "$dbhost.$dbdomain" -p $sqlPort -U $dbusr -W $dbname

I've worked primarily with MySQL, and so was not entirely familiar with the psql arguments. I should've researched just a bit more before posting. Sorry again for adding unnecessary info, but hopefully it will help others to avoid this rookie mistake.

@yosifkit
Copy link
Member

@sk33z3r, no problem!

As the original issue also seems to be solved, I'll close the issue.

@tiagocborg
Copy link

if anyone still ends up here looking for the solution: envs need to be ordered POSTGRES_DB > POSTGRES_USER > POSTGRES_PASSWORD.

@mrdulin
Copy link

mrdulin commented Nov 26, 2018

same issue.

Here is the docker-compose logs:

db_1_9392cbf8fcab | waiting for server to start....LOG:  database system was shut down at 2018-11-26 04:11:56 UTC
db_1_9392cbf8fcab | LOG:  MultiXact member wraparound protections are now enabled
db_1_9392cbf8fcab | LOG:  database system is ready to accept connections
db_1_9392cbf8fcab | LOG:  autovacuum launcher started
db_1_9392cbf8fcab |  done
db_1_9392cbf8fcab | server started
db_1_9392cbf8fcab | CREATE DATABASE
db_1_9392cbf8fcab |
db_1_9392cbf8fcab |
db_1_9392cbf8fcab | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
db_1_9392cbf8fcab |
db_1_9392cbf8fcab | LOG:  received fast shutdown request
db_1_9392cbf8fcab | LOG:  aborting any active transactions
db_1_9392cbf8fcab | waiting for server to shut down....LOG:  autovacuum launcher shutting down
db_1_9392cbf8fcab | LOG:  shutting down
db_1_9392cbf8fcab | LOG:  database system is shut down
db_1_9392cbf8fcab |  done
db_1_9392cbf8fcab | server stopped

As you can see, there is no CREATE Role log.

image: postgres:9.6.11

update:

Finally, I found the reason why I always get error: role "sampleadmin" does not exist error.

  1. I use brew install postgres
  2. brew services start postgres
  3. brew uninstall postgres
  4. docker-compose up - start postgres container
  5. local application try to connect postgres db with user and password

And, then I got error: role "sampleadmin" does not exist error.

ps aux | grep postgres

The postgres process which brew started still there. (Don't know why, I already uninstall postgres.) So my application always try to connect this pg(brew services start), not the pg running in docker container.

kill -9 <postgres pid>

Then, everything works fine.

@vishal-vp
Copy link

vishal-vp commented Nov 30, 2018

If someone is having this problem please do check if you are defining postgres service in the docker-compose file again in the end.

@majinghe
Copy link

i am experiencing the same issue on kubernetes
kubectl version: 1.11.2
docker version: 17.03.1-ce
postgres version: v10.6

when i created postgres pods on kubernetes, envs are set correctly.
LANG=en_US.utf8
HOSTNAME=sonar-postgres-766497cdfb-r6w6c
SONAR_POSTGRES_SERVICE_HOST=172.21.127.145
PG_MAJOR=10
KUBERNETES_PORT_443_TCP_PROTO=tcp
KUBERNETES_PORT_443_TCP_ADDR=172.21.0.1
KUBERNETES_PORT=tcp://172.21.0.1:443
SONAR_POSTGRES_PORT_5432_TCP_ADDR=172.21.127.145
PWD=/
HOME=/root
PG_VERSION=10.6-1.pgdg90+1
GOSU_VERSION=1.10
KUBERNETES_SERVICE_PORT_HTTPS=443
PGDATA=/var/lib/postgresql/data
KUBERNETES_PORT_443_TCP_PORT=443
POSTGRES_DB=postgres
SONAR_POSTGRES_PORT_5432_TCP_PORT=5432
SONAR_POSTGRES_PORT=tcp://172.21.127.145:5432
KUBERNETES_PORT_443_TCP=tcp://172.21.0.1:443
TERM=xterm
POSTGRES_PASSWORD=sonar
SONAR_POSTGRES_PORT_5432_TCP=tcp://172.21.127.145:5432
POSTGRES_USER=sonar
SHLVL=1
SONAR_POSTGRES_PORT_5432_TCP_PROTO=tcp
KUBERNETES_SERVICE_PORT=443
SONAR_POSTGRES_SERVICE_PORT=5432
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/postgresql/10/bin
KUBERNETES_SERVICE_HOST=172.21.0.1

but when i exec psql command. It showed
psql: FATAL: role "postgres" does not exist

Anyone know how to fix this issue?

@yosifkit
Copy link
Member

@lhb008, the postgres user wont exist in the database because you have POSTGRES_USER=sonar; you need to use that user to connect.

@kuritka
Copy link

kuritka commented May 5, 2019

ensure that $HOME/docker/volumes/postgres is empty !!

sudo docker run --rm --name pg-docker \
 -e POSTGRES_PASSWORD=password \
 -p 5432:5432 \
 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data \
 -d postgres:latest

To create server connection use pg-docker as Host and 5432 as port postgres as database and user. For password use password

To accessing data you should use PGAdmin - linked to pg-docker

sudo docker run -p 8085:80 \
--link pg-docker:pg-docker \
-e "PGADMIN_DEFAULT_EMAIL=admin@local.com" \
-e "PGADMIN_DEFAULT_PASSWORD=password" \
-d dpage/pgadmin4

To access database run localhost:8085 from your browser and sign in as admin@local.com / password

@vinchauhan
Copy link

if anyone still ends up here looking for the solution: envs need to be ordered POSTGRES_DB > POSTGRES_USER > POSTGRES_PASSWORD.

Best answer...!

@dgreene1
Copy link

Can someone post their docker-compose.yml file? I've tried using the correct order and I've even read through the docker compose logs and I never see a create statement.

@yosifkit
Copy link
Member

@dgreene1: See the docs from Docker Hub; https://github.com/docker-library/docs/tree/3130985bd950650b4cd31cb1ef973d30f9f0c154/postgres#-via-docker-stack-deploy-or-docker-compose (the play-with-docker link will even let you explore that compose file on a fresh setup).

@gustavovalverde
Copy link

if anyone still ends up here looking for the solution: envs need to be ordered POSTGRES_DB > POSTGRES_USER > POSTGRES_PASSWORD.

This worked for me, but why is this a solution?

@yosifkit
Copy link
Member

if anyone still ends up here looking for the solution: envs need to be ordered POSTGRES_DB > POSTGRES_USER > POSTGRES_PASSWORD.

This worked for me, but why is this a solution?

It can''t; order of environment variable definitions in the docker-compose.yml (or docker run) has no bearing in the bash entrypoint that uses them. I would guess it is because the volume was deleted (see also #203 (comment))

Locking since the original issue was solved.

@docker-library docker-library locked as resolved and limited conversation to collaborators Dec 10, 2019
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests