pgAdmin in Docker: Provisioning connections and passwords

pgAdmin is a popular open source and feature rich administration and development platform for PostgreSQL. When provisioning Postgres database environments using containers, it is not unusual to also provision a pgAdmin container.

The pgAdmin image provided on Docker Hub does not contain any server connection details. When your pgAdmin container changes regularly (think about changes to database connection details and keeping pgAdmin up to date), you might not want to enter the connections and passwords manually every time. This is especially true if you use a single pgAdmin instance to connect to many databases. A manual step also prevents a fully automated build process for the pgAdmin container.

You can export/import connection information, but you cannot export passwords. It is a bother, especially in development environments where the security aspect is less important, to lookup passwords every time you need them. How to fix this and make your life a little bit easier?

In this blog I’ll show how to create a simple script to automate creating connections and supply password information so the pgAdmin instance is ready for use when you login to the console for the first time! This consists of provisioning the connections and provisioning the password files. You can find the files here.

Getting started

In order to test creating connections, I need both a Postgres database and an pgAdmin instance. Docker-compose files are quite suitable to do this. I used the following  docker-compose.yml:

version: '3.5'

services:
  postgres:
    container_name: postgres_container
    image: postgres:12.1
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-Welcome01}
      PGDATA: /data/postgres
    volumes:
       - postgres:/data/postgres
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped
  
  pgadmin:
    container_name: pgadmin_container
    image: dpage/pgadmin4:4.16
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}
    volumes:
       - pgadmin:/root/.pgadmin
    ports:
      - "${PGADMIN_PORT:-5050}:80"
    networks:
      - postgres
    restart: unless-stopped

networks:
  postgres:
    driver: bridge

volumes:
    postgres:
    pgadmin:

After you’ve done docker-compose up, you’ll see two containers:

[maarten@localhost postgres]$ docker ps
CONTAINER ID        IMAGE                 COMMAND                  CREATED             STATUS              PORTS                           NAMES
331f18838528        dpage/pgadmin4:4.16   "/entrypoint.sh"         17 hours ago        Up 54 minutes       443/tcp, 0.0.0.0:5050->80/tcp   pgadmin_container
7e7d3f3a51d2        postgres:12.1         "docker-entrypoint.s…"   17 hours ago        Up 54 minutes       0.0.0.0:5432->5432/tcp          postgres_container

Provisioning connections

In order to provision connections, you can use a JSON file and load that. The JSON file I’ve used is listed below:

servers.json

{
    "Servers": {
        "1": {
            "Name": "pgadmin4@pgadmin.org",
            "Group": "Servers",
            "Host": "postgres",
            "Port": 5432,
            "MaintenanceDB": "postgres",
            "Username": "postgres",
            "SSLMode": "prefer",
            "PassFile": "/pgpassfile"
        }
    }
}

In this case I’m creating a single connection to a database with hostname postgres. This is equal to the service name in the docker-compose.yml file above. Also important to note is that I’m referring to a password file: PassFile /pgpassfile.

If you have a running container already which has connections, you can export the connections as follows

docker exec -it pgadmin_container python /pgadmin4/setup.py --dump-servers /tmp/servers.json
docker cp pgadmin_container:/tmp/servers.json .

If you have a running container and want to import connections, you can do the following:

docker cp servers.json pgadmin_container:/tmp/servers.json
docker exec -it pgadmin_container python /pgadmin4/setup.py --load-servers /tmp/servers.json

You can import multiple server JSON files after each other (importing a servers.json file adds the servers) and even add servers with the same name. This is not recommended though.

Provisioning passwords

You now have connections. Passwords however cannot be exported in such a way (see here, ‘Password fields cannot be imported or exported’). You have specified a password file however when creating the connection (the PassFile parameter). So how does this file need to look and where does it need to go inside the container?

In order to find out I created a folder using the pgAdmin web interface and searched for the folder name.

[maarten@localhost postgres]$ docker exec -it pgadmin_container find / -name 'New Folder'
/var/lib/pgadmin/storage/pgadmin4_pgadmin.org/New Folder

This location (/var/lib/pgadmin/storage/pgadmin4_pgadmin.org) is a per user location and not per connection. Per connection, a file in that directory can be specified with / being /var/lib/pgadmin/storage/pgadmin4_pgadmin.org/. 

The format of the file is specified here. In my example, the postgres database and user are postgres and the password is Welcome01. Thus my password file (pgpassfile) is:

postgres:5432:postgres:postgres:Welcome01

When an instance is created from the image for the first time, the directory /var/lib/pgadmin/storage/pgadmin4_pgadmin.org does not exist yet. It should be created with permission mask 700 owned by user and group pgadmin if we want to provision it beforehand.

docker exec -u pgadmin:pgadmin -it pgadmin_container mkdir -m 700 /var/lib/pgadmin/storage/pgadmin4_pgadmin.org

When using docker cp to copy files into a container, the root user is used. It is not possible to specify a user/group to use within the container with this command. The directory created above is not writable by root. Thus in order to place the password file in the correct location we first copy it to /tmp, set the correct user/group and then from within the container move it to the correct location. 

docker cp pgpassfile pgadmin_container:/tmp/pgpassfile
docker exec -it -u root pgadmin_container chown pgadmin:pgadmin /tmp/pgpassfile
docker exec -it pgadmin_container mv /tmp/pgpassfile /var/lib/pgadmin/storage/pgadmin4_pgadmin.org

The file can only be used when it has umask 600 (only readable, writable by pgadmin) so after we have moved the file, we need to set the correct permissions. 

docker exec -it pgadmin_container chmod 600 /var/lib/pgadmin/storage/pgadmin4_pgadmin.org/pgpassfile

It would have been nice if the docker cp command had supplied functionality to set the target user, group and permissions in order to avoid such a workaround.

Running the example

In order to run this complete example and see it in action you can do the following:

git clone https://github.com/MaartenSmeets/db_perftest.git
cd db_perftest/pg_provision
bash ./create.sh

Now you can go to: localhost:5050 and login with user pgadmin4@pgadmin.org password admin and when you open the Servers entry on the left and the connection you have created, you don’t need to enter a password.

If you want to remove the environment to start over again, you can do:

bash ./remove.sh

Finally

Podman and Docker

Podman is an alternative to docker and the default container engine in recent versions of Fedora and Red Hat. Podman supports rootless containers without the need for a socket connection/daemon and uses systemd instead.

I first tried this with podman instead of docker on Fedora 31. I decided to go back to docker for several reasons:

• I couldn’t get podman to start containers. I kept getting the following error, even after having tried several podman configuration settings and kernel parameters: [conmon:d]: failed to write to /proc/self/oom_score_adj: Permission denied. 
• This probably wasn’t going to be the last challenge I would encounter using podman. Many people appear to be having issues (see for example a recent post here). 
• I did not want to waste time on rewriting the docker-compose file to podman commands.

In order to get docker to work on Fedora 31 (Fedora switched to CgroupsV2 so Docker does not work out of the box anymore):

sudo yum install docker-ce
sudo systemctl enable docker
sudo grubby --update-kernel=ALL --args="systemd.unified_cgroup_hierarchy=0"
sudo reboot

After this, docker worked without difficulties. I’ll take a look at podman again in the future when using it is less challenging.