Wednesday 10 July 2019

Reverse Proxying to pgAdmin

Reverse proxying requests to a pgAdmin server is becoming more and more popular if posts to the mailing lists are to be taken as an indicative measure; more often than not when using pgAdmin in a container (of which there have now been over 10 million pulls)! Typically users will deploy a reverse proxy for a couple of reasons; to host multiple applications in different subdirectories under the same domain, or to add SSL/TLS support independently of the application.

Because of the number of questions asked, I spent a little time over the last couple of days doing some testing and updating the documentation with some examples. Here's a blog-ified version of that work.


Nginx

Nginx is winning the battle of the web servers these days, beating out Lighttpd (which is still a fine piece of software) and the ageing and arguably bloated Apache HTTPD. All of these servers support reverse proxying, and whilst I've looked at Nginx, the examples shown below can easily be translated to the other servers if you prefer to run them instead.

In the following examples, we have pgAdmin running in a Docker container (in which it's hosted under Gunicorn). For simplicity, the examples have Nginx running on the host machine, but it can also be easily run in another container, sharing a Docker network with pgAdmin. In such a configuration there is no need to map the pgAdmin container port to the host.

The container is launched as shown below. See the documentation for information on other useful environment variables you can set and paths you can map.

The commands below will pull the latest version of the container from the repository, and run it with port 5050 on the host mapped to port 80 on the container. It will set the default username and password to user@domain.com and SuperSecret respectively.

docker pull dpage/pgadmin4
docker run -p 5050:80 \
    -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \
    -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \
    -d dpage/pgadmin4

A simple configuration to reverse proxy with Nginx to pgAdmin at the root directory looks like this:

server {
    listen 80;
    server_name _;

    location / {
        proxy_set_header Host $host;
        proxy_pass http://localhost:5050/;
        proxy_redirect off;
    }
}

Here we tell Nginx to listen on port 80, and respond to any server name (sent by the client in the Host header). We then specify that all requests under the root directory are proxied back to port 5050 on the local host, and that the Host header is passed along as well. The proxy_redirect option tells the server not to rewrite the Location header.

But what if we want to host pgAdmin under a subdirectory, say /pgadmin4? In this case we need to change the path at the top of the location block and add the X-Script-Name header to the requests made to the pgAdmin container to tell it what subdirectory it's hosted under. This is shown below:

server {
    listen 80;
    server_name _;

    location /pgadmin4/ {
        proxy_set_header X-Script-Name /pgadmin4;
        proxy_set_header Host $host;
        proxy_pass http://localhost:5050/;
        proxy_redirect off;
    }
}

OK, so that's cool but we're talking about super top secret database stuffs here. It needs to be encrypted! Adding SSL/TLS support to the configuration is largely unrelated to pgAdmin itself, except that as with the subdirectory, we need to tell it the URL scheme (http or https) to use. We do this by setting the X-Scheme header. The other changes are to add a redirect from http to https, and to configure SSL/TLS:

server {
    listen 80;
    return 301 https://$host$request_uri;
}

server {
    listen 443;
    server_name _;

    ssl_certificate /etc/nginx/server.crt;
    ssl_certificate_key /etc/nginx/server.key;

    ssl on;
    ssl_session_cache builtin:1000 shared:SSL:10m;
    ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
    ssl_ciphers HIGH:!aNULL:!eNULL:!EXPORT:!CAMELLIA:!DES:!MD5:!PSK:!RC4;
    ssl_prefer_server_ciphers on;

    location /pgadmin4/ {
        proxy_set_header X-Script-Name /pgadmin4;
        proxy_set_header X-Scheme $scheme;
        proxy_set_header Host $host;
        proxy_pass http://localhost:5050/;
        proxy_redirect off;
    }
}


Traefik

Traefik is becoming increasingly popular in containerised environments because it's designed to integrate with the orchestration platform being used and auto-configure itself as much as possible. The examples below show it working with plain Docker, but it will work with Swarm, Compose, Kubernetes and other orchestrators as well. Note that Traefik is designed purely for reverse proxying, routing and load balancing; it's not a general purpose webserver.

In the examples below, the following Traefik configuration is used. Because it auto-configures itself, the changes we make to get the desired configuration are to the way the pgAdmin container is deployed, not to the Traefik configuration:

defaultEntryPoints = ["http", "https"]

[entryPoints]
  [entryPoints.http]
    address = ":80"
      [entryPoints.http.redirect]
        entryPoint = "https"
  [entryPoints.https]
    address = ":443"
      [entryPoints.https.tls]

[docker]
domain = "domain_name"
watch = true

With this configuration, Traefik will automatically detect containers as they are launched, and reverse proxy to them using the virtual hostname generated from the container name and the domain in its config file, e.g. <container_name>.<domain>. SSL/TLS is enabled in this setup, with a redirect from plain http to https. The certificate used will be the default one built into Traefik; see the documentation for details on how Let's Encrypt or certificates from other issuers can be used.

To host pgAdmin at the root directory, we simply launch a container with the correct name, and no host to container port mapping:

docker pull dpage/pgadmin4
docker run --name "pgadmin4" \
    -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \
    -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \
    -d dpage/pgadmin4

With the configuration and commands above, Traefik will host pgAdmin at https://pgadmin4.domain_name/. Of course, the domain name should be changed to a real one, and a suitable CNAME, A or AAAA record should be added to the DNS zone file.

In order to host pgAdmin under a subdirectory, as in the Nginx example we need to tell both the proxy server and pgAdmin about the subdirectory. We tell pgAdmin by setting the SCRIPT_NAME environment variable, and we tell Traefik by adding a label to the container instance. For example:

docker pull dpage/pgadmin4
docker run --name "pgadmin4" \
    -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \
    -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \
    -e "SCRIPT_NAME=/pgadmin4" \
    -l "traefik.frontend.rule=PathPrefix:/pgadmin4" \
    -d dpage/pgadmin4


Conclusion

Users are using reverse proxy servers to provide an interface between their clients and the pgAdmin server. These can be more traditional servers such as Nginx, or purpose designed reverse proxy servers such as Traefik. In either case, it's simple to configure SSL/TLS support or to host pgAdmin in a subdirectory.

5 comments:

  1. have been searching for this solution for a long time now but none of those solutions seem to work. this one does beautifully. thanks very much dave. much appreciate it. youre awesome.

    ReplyDelete
  2. Is it possible to do the same with apache?
    I have tried this:


    RedirectMatch permanent ^/pgadmin4$ /pgadmin4/
    ProxyPreserveHost On
    ProxyPass http://127.0.0.1:5050/
    ProxyPassReverse http://127.0.0.1:5050/
    Header edit Location ^/ /pgadmin4/
    Header always set X-Script-Name /pgadmin4


    However, it doesn't work. The url is always truncated to http://foo.com/login?next=%2F instead of http://foo.com/pgadmin4/login?next=%2F

    ReplyDelete
  3. Hi Dave, thank you for the great work!
    pgAdmin is an essential tool for us, so we appreciate it a lot.

    I have one question about the latest version.
    We are r-proxying it via kong (which is essentially nginx at its core) and I have noticed that up until 4.17 the behavior after successfully logging in was normal - i.e. it would send me to /pgadmin4/browser/
    The latest version, however, redirects me to / which is the root URL for my application, which instead redirects to the authentication server, thus confusing users and breaking the flow.
    When I go manually to /pgadmin4/browser/ it becomes apparent that the login has been successful as it loads the browser.
    Could it be that I need to pass some additional info via headers?

    ReplyDelete
  4. There was a bug found shortly after release. If you update to the latest snapshot build it should work.

    ReplyDelete