Pgadmin4 With Nginx On Ubuntu

This post outlines some simple steps to deploy pgadmin with nginx on ubuntu.

Motivation

pgadmin4 provides a web interface for basic monitoring, administration, and querying tasks against postgresql clusters.

The move to a web interface has some nice features, not the least of which are the classic benefits of web over native applications. For me, this means not having to maintain a fleet of pgadmin installs for analysts with an aversion to command line tools, e.g. psql.

Installation

The following steps should roughly work for any relatively recent ubuntu version, e.g. 16.04 to 17.04

First install docker and nginx.

sudo apt install docker.io nginx
sudo usermod -aG docker $USER

In my experience a reboot is necessary here, as the docker daemon was not available regardless of attempting to restart the service.

Next we install a docker image for pgadmin4. The chorss/docker-pgadmin4 repository seems to be well maintained. Thanks chorss!

docker pull chorss/docker-pgadmin4

Now we can run pg admin on our host machine with the following command.

docker run -e SERVER_MODE=true -e PGADMIN_SETUP_EMAIL=berrydigital@gmail.com \
  -e PGADMIN_SETUP_PASSWORD=password -d -p 5050:5050 \
  -v /home/robert/data:/data chorss/docker-pgadmin4

Nginx Configuration

Since we're considering deploying this to be useful for analysts, you probably want to get an ssl certificate. The following example assumes you have received certs from let's encrypt.

Create a host entry in /etc/nginx/sites-available/pgadmin similar to the following.

server {
  listen  80;
  server_name pgadmin.awesome.com;
  return 301 https://$server_name$request_uri;
}

server {
  listen  443;
  server_name pgadmin.awesome.com;

  ssl on;
  ssl_certificate /etc/letsencrypt/live/pgadmin.awesome.com/fullchain.pem;
  ssl_certificate_key /etc/letsencrypt/live/pgadmin.awesome.com/privkey.pem;
  location / {
    proxy_pass http://localhost:5050;
  }
}
Here we've set up a simple server that redirects to ssl, and acts as a reverse proxy, passing requests to the pgadmin process running in docker. You may consider allowing only specific ips with an allow / deny directive pairing.

Final Thoughts

pgadmin's adoption of a web interface brings a lot of benefits. But it can be a little more difficult to setup than a traditional installation wizard.

Some of the complexity can be sidestepped by leveraging docker and a minimal nginx configuration.