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 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 it using it is less challenging.
Hi! Thanks for the helpful post. One suggestion; instead of tracking down the storage directory within the container it may be preferable to map /var/lib/pgadmin to a directory on persistent storage. That way not only will the password file be available to any instance of the container, but so will any saved SQL queries etc.
ReplyDeleteSee https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html#mapped-files-and-directories
Thanks for this useful post It helped me figure out how to do the same thing without any shell scripts just doing docker-compose up is all you need. https://github.com/asaikali/docker-compose-postgres has sample.
ReplyDelete