Postgres: Role postgres is missing

[olafura]

This is what I get when the docker image spins up:
FATAL: role "postgres" does not exist

[wglambert]

Can you give all the commands you ran and any relevant contextual information or files for reproducing the issue

I don't see anything unusual for a basic start up

$ docker run --rm -d -e MYSQL_ROOT_PASSWORD=root --name postgres postgres
$ docker exec -it postgres bash
root@ee08447db7b7:/# psql -U postgres
psql (10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.

postgres=#

logs

$ docker logs postgres
The files belonging to this database system will be owned by user "postgres".                                                                                 
This user must also own the server process.                                                                                                                   

The database cluster will be initialized with locale "en_US.utf8".                                                                                            
The default database encoding has accordingly been set to "UTF8".                                                                                             
The default text search configuration will be set to "english".                                                                                               

Data page checksums are disabled.                                                                                                                             

fixing permissions on existing directory /var/lib/postgresql/data ... ok                                                                                      
creating subdirectories ... ok                                                                                                                                
selecting default max_connections ... 100                                                                                                                     
selecting default shared_buffers ... 128MB                                                                                                                    
selecting dynamic shared memory implementation ... posix                                                                                                      
creating configuration files ... ok                                                                                                                           
running bootstrap script ... ok                                                                                                                               
performing post-bootstrap initialization ... ok                                                                                                               
syncing data to disk ... ok                                                                                                                                   

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

****************************************************
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".
****************************************************
waiting for server to start....2018-09-11 18:26:56.933 UTC [51] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-09-11 18:26:56.989 UTC [52] LOG:  database system was shut down at 2018-09-11 18:26:55 UTC
2018-09-11 18:26:57.016 UTC [51] LOG:  database system is ready to accept connections
 done
server started
ALTER ROLE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down...2018-09-11 18:26:57.824 UTC [51] LOG:  received fast shutdown request
.2018-09-11 18:26:57.830 UTC [51] LOG:  aborting any active transactions
2018-09-11 18:26:57.835 UTC [51] LOG:  worker process: logical replication launcher (PID 58) exited with exit code 1
2018-09-11 18:26:57.837 UTC [53] LOG:  shutting down
2018-09-11 18:26:57.905 UTC [51] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2018-09-11 18:26:57.963 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-09-11 18:26:57.963 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2018-09-11 18:26:57.970 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-09-11 18:26:58.018 UTC [69] LOG:  database system was shut down at 2018-09-11 18:26:57 UTC
2018-09-11 18:26:58.047 UTC [1] LOG:  database system is ready to accept connections

[olafura]

This version works: circleci/postgres:9.6@sha256:c6c7d60ca0f31904b1d3da5baf5352a42e32b0d6e460a9f265ac4011c7f94432

Here is what happens when I do this on the lastest circleci/postgres:9.6 image:
psql -U postgres
psql: FATAL: role "postgres" does not exist

The sha for the latest image is:
sha256:a244eb59b5c91b6c99e985646ffe651e8d5d12ab4e1df0d640b970e3c9a4d321

[olafura]

Sorry I think I posted this to the wrong repo ;)

[wglambert]

https://hub.docker.com/r/circleci/postgres/

The following is the customizations CircleCI applies in a Dockerfile:

FROM postgres:latest
ENV POSTGRES_USER=root \
POSTGRES_DB=circle_test

[olafura]

@wglambert Thanks I know but it's also creating the postgres user, it just seems like it's broken now. And even when I assign the right env for the POSTGRES_USER and POSTGRES_PASSWORD it's still broken.

[tianon]

The postrges user still existing while using POSTGRES_USER was technically a side-effect that's been fixed in https://github.com/docker-library/postgres/pull/493.

[olafura]

So what you are telling me that you are are not creating the role on purpose even if you set the POSTGRES_USER to postgres. Why and is this the code that is in circleci/postgres.

[olafura]

@tianon thanks it does seem to be working now with setting POSTGRES_USER ;)

[ronkorving]

This killed our CI because we pull in Docker images, and it took me quite a while to figure out what was going on. I'm not sure how these breaking changes (even if it fixed unintended behavior, or as you call it, a bug) can be better communicated, but it probably should be. Or did I miss something blatantly obvious?

(In the mean time, I'm now gonna try the env. variable, fingers crossed :))

[tianon]

What's the use case for the postgres role when you've explicitly asked for
a different one? (Trying to understand beyond just that this broke a few
things.)

[kamlekar]

ok, I fixed by clearing all docker containers and images. Also ran docker prune command to clear the cache. Later when I ran docker-compose up, it actually worked!

docker rm -vf $(docker ps -a -q)
docker rmi -f $(docker images -a -q)
docker system prune --all

Note: It seems as I am nuking on a small thief with the above commands but it was fine in my case.