pg_database issue when admin db is missing

[jwillmer]

As described in this issue comment the execution of the following command returns an exception if no database called admin exists.

bash-5.0# export PGPASSWORD=$DB_PASS
bash-5.0# psql -h $DB_HOST -U $DB_USER -c 'COPY (SELECT datname FROM pg_database WHERE datistemplate = false) TO STDOUT;' 
psql: error: FATAL:  database "admin" does not exist

Using the following docker-compose file will create me a new postgres container with missing admin database. I think an admin db should be added automatically to make the above command work.

postgres:
    image: postgres:12.1-alpine
    hostname: postgres
    container_name: postgres
    restart: always
    environment:
       POSTGRES_USER: ${POSTGRESUSER}
       POSTGRES_PASSWORD: ${POSTGRESPW}
       POSTGRES_DB: ${POSTGRESDB}
    volumes:
      - postgres-data:/var/lib/postgresql/data

[tianon]

In order to reproduce the issue, I think we'll need a bit more data? I'm not aware of any functionality inside stock PostgreSQL that makes "admin" any kind of special case, and I'm not able to reproduce using only the information provided: 😕

$ docker pull postgres:12-alpine
12-alpine: Pulling from library/postgres
Digest: sha256:002161f3f4472b37e6014e7d5136a81076ebec5add084a2cd433cf84a416bb11
Status: Image is up to date for postgres:12-alpine
docker.io/library/postgres:12-alpine

$ docker run -dit --name postgres --env POSTGRES_USER=example --env POSTGRES_PASSWORD=example --env POSTGRES_DB=example --volume /var/lib/postgresql/data postgres:12-alpine
6b17b81001e0f531419053451cc453eb1cf5d9dbb82064ccdaa5525c83c6f982

$ docker logs --tail=1 postgres
2021-06-07 19:20:16.105 UTC [1] LOG:  database system is ready to accept connections

$ docker exec -it postgres bash
bash-5.1# PGPASSWORD=example psql -h 127.0.0.1 -U example -c 'COPY (SELECT datname FROM pg_database WHERE datistemplate = false) TO STDOUT;'
postgres
example
bash-5.1# 

[jwillmer]

I tested it again with a new and minimal docker-compose definition. Note that there is no volume attached so that it is a clean deployment:

services:
  pgdb:
    image: postgres:12.1-alpine
    environment:
       POSTGRES_USER: admin
       POSTGRES_PASSWORD: Example01
       POSTGRES_DB: local

And this was the output when entering the container via docker exec -it central_pgdb_1 bash:

bash-5.0# export PGPASSWORD=Example01
bash-5.0# psql -h 127.0.0.1 -U admin -c 'COPY (SELECT datname FROM pg_database WHERE datistemplate = false) TO STDOUT;'
psql: error: could not connect to server: FATAL:  database "admin" does not exist

[wglambert]

Because you're specifying POSTGRES_DB: local you need to include that when connecting

$ docker-compose up -d
Creating network "root_default" with the default driver
Creating root_pgdb_1 ... done

$ export PGPASSWORD=Example01

$ psql -h 127.0.0.1 -U admin -d local
psql (12.7, server 12.1)
Type "help" for help.

local=# \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 local     | admin | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | admin | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | admin | UTF8     | en_US.utf8 | en_US.utf8 | =c/admin         +
           |       |          |            |            | admin=CTc/admin
 template1 | admin | UTF8     | en_US.utf8 | en_US.utf8 | =c/admin         +
           |       |          |            |            | admin=CTc/admin
(4 rows)

local=# \c
psql (12.7, server 12.1)
You are now connected to database "local" as user "admin".

When not specifying POSTGRES_DB then then the value of POSTGRES_USER will be used.

$ docker run -d --rm --name postgres -p 5432:5432 -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=Example01 postgres:12.1-alpine
0d4be9f97c5b580ba8c7e25fbda261b4ec5fd5185f6cf2d48337dbdc0d47bdb3

$ psql -h 127.0.0.1 -U admin
psql (12.7, server 12.1)
Type "help" for help.

admin=# \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 admin     | admin | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | admin | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | admin | UTF8     | en_US.utf8 | en_US.utf8 | =c/admin         +
           |       |          |            |            | admin=CTc/admin
 template1 | admin | UTF8     | en_US.utf8 | en_US.utf8 | =c/admin         +
           |       |          |            |            | admin=CTc/admin
(4 rows)

admin=# \c
psql (12.7, server 12.1)
You are now connected to database "admin" as user "admin".

[jwillmer]

Thank you for the solution. I already proposed a bug fix for it in a related project.