According to their homepage headline, PostgreSQL is world's most advanced open source relational database. More and more services here at Mimar rely on it, of which perhaps most popular would be GitLab, Synapse and Mattermost. While basic administrative tasks in PostgreSQL can be performed by means of interactive terminal, psql, most admins will find pgAdmin a better tool for the job. This article gives exact steps how to configure pgAdmin4 as web application hosted in Apache HTTP Server name-based VirtualHost on FreeBSD.

Conventions

Before we dive into installing and configuring stuff, here are a few introductory explanations:

  • It is assumed that user account under which installation will be performed has full sudo rights
  • Abovementioned user account has tcsh login shell
  • Commands which are performed as standard user will be prefixed with user@server:~ %
  • Commands which are performed in python virtualenv will be prefixed with (pgadmin4) user@server:~ %

Initial Setup

Let's install prerequisite packages first:

user@server:~ % pkg install  databases/postgresql11-client databases/py-sqlite3 devel/py-virtualenv www/apache24 www/mod_wsgi4

Next, as I prefer to run python virtualenv stuff as dedicated non-privileged user, I'm going to create group and user:

user@server:~ % sudo pw groupadd pgadmin -g 20002
user@server:~ % sudo pw useradd pgadmin -u 20002 -c "pgAdmin unprivileged user" -d /usr/local/www/pgadmin4 -g pgadmin -s /usr/sbin/nologin

Using pw useradd for user creation does not automatically create its homedir, so I will create it and give it appropriate permissions. I placed homedir under /usr/local/www/ instead under /home/ as it seemed more appropriate for a web application:

user@server:~ % sudo mkdir /usr/local/www/pgadmin4
user@server:~ % sudo chown pgadmin:pgadmin /usr/local/www/pgadmin4/

Using sudo to impersonate pgadmin user, I will create virtualenv under /usr/local/www/pgadmin4/:

user@server:~ % sudo -u pgadmin virtualenv /usr/local/www/pgadmin4/
Using base prefix '/usr/local'
New python executable in /usr/local/www/pgadmin4/bin/python3.6
Also creating executable in /usr/local/www/pgadmin4/bin/python
Installing setuptools, pip, wheel...
done.

I am going to source virtualenv. This command unfortunately cannot be run using sudo to impersonate pgadmin, but I believe there aren't any security risks in running it under currently logged-in user with full sudo rights, as long as consequent commands are run as dedicated unprivileged user.

user@server:~ % source /usr/local/www/pgadmin4/bin/activate.csh

Notice that prompt changed, which means we are now in virtualenv

You can exit virtualenv by typing deactivate

Inside virtualenv, we will use sudo-impersonated pgadmin user to pip install prerequisites for pgadmin4:

(pgadmin4) user@server:~ % sudo -u pgadmin pip install pyopenssl cryptography pyasn1  ndg-httpsclient

After prerequisites have been successfully installed, we can proceed to install pgadmin4:

Before running below command, check latest version at https://www.postgresql.org/ftp/pgadmin/pgadmin4/ and adjust it accordingly

(pgadmin4) user@server:~ % sudo -u pgadmin pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.14/pip/pgadmin4-4.14-py2.py3-none-any.whl

Next, we need to append a few lines to /usr/local/www/pgadmin4/lib/python3.6/site-packages/pgadmin4/config_distro.py, in order to make paths adhere to FreeBSD standards:

SERVER_MODE = True
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/db/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/run/pgadmin4/sessions'
STORAGE_DIR = '/var/run/pgadmin4/storage'

Let's create those dirs now, and give them appropriate ownership needed in install phase:

(pgadmin4) user@server:~ % sudo mkdir /var/log/pgadmin4
(pgadmin4) user@server:~ % sudo mkdir /var/db/pgadmin4
(pgadmin4) user@server:~ % sudo mkdir /var/run/pgadmin4
(pgadmin4) user@server:~ % sudo chown pgadmin:pgadmin /var/log/pgadmin4/
(pgadmin4) user@server:~ % sudo chown pgadmin:pgadmin /var/db/pgadmin4/
(pgadmin4) user@server:~ % sudo chown pgadmin:pgadmin /var/run/pgadmin4/

Let's configure pgadmin for server usage:

(pgadmin4) user@server:~ % sudo -u pgadmin python /usr/local/www/pgadmin4/lib/python3.6/site-packages/pgadmin4/setup.py`

After setting admin username (which is in email address format) and password, we can deactivate our virtual environment:

(pgadmin4) user@server:~ % deactivate

During install and setup phase, we impersonated unprivileged pgadmin user with sudo, which is why folders configured in config_distro.py, the ones pgadmin writes to, had to be owned by pgadmin:pgadmin. However, once we configure apache to serve it as web application, it is www user who will run commands by means of wsgi_module, therefore we need to change their ownership to www:www:

user@server:~ % sudo chown -R www:www /var/log/pgadmin4/
user@server:~ % sudo chown -R www:www /var/db/pgadmin4/
user@server:~ % sudo chown -R www:www /var/run/pgadmin4/

The final piece of the puzzle is apache web server. Different people have different setups, so I won't go into detailed configuration here. It is important to load mod_wsgi4 we installed earlier in httpd.conf. Vhost configuration is quite simple:

<VirtualHost *:80>
  ServerName pgadmin.example.org
  ServerAdmin webmaster@example.org
  Redirect permanent / https://pgadmin.example.org/
</VirtualHost>

<VirtualHost *:443>
  SSLEngine on
  ServerName pgadmin.example.org
  ServerAdmin webmaster@example.org
  SSLCertificateFile "/etc/ssl/certs/live/example.org/cert.pem"
  SSLCertificateKeyFile "/etc/ssl/certs/live/example.org/privkey.pem"
  SSLCertificateChainFile "/etc/ssl/certs/live/example.org/chain.pem"
  Header always set Strict-Transport-Security "max-age=15768000; includeSubDomains; preload"
  WSGIDaemonProcess pgadmin processes=1 threads=25 python-home=/usr/local/www/pgadmin4
  WSGIScriptAlias / /usr/local/www/pgadmin4/lib/python3.6/site-packages/pgadmin4/pgAdmin4.wsgi
  <Directory "/usr/local/www/pgadmin4/lib/python3.6/site-packages/pgadmin4">
    WSGIProcessGroup pgadmin
    WSGIApplicationGroup %{GLOBAL}
    Require all granted
  </Directory>
</VirtualHost>

Configuring DNS for name-based apache virtual host, as well as obtaining or creation of SSL certificates are out of the scope of this turorial

We should now be able to point our browser to https://pgadmin.example.org/ and log into pgadmin4 with username in form of email address and password we specified during setup phase.

Installing postgresql server, configuring postgresql.conf and pg_hba.conf are out of the scope of this tutorial, as well as connecting to it from pgadmin4

Upgrading

In order to upgrade pgadmin4, first make sure apache does not use it. If you have the luxury of stopping whole apache server you can go for that option, but commenting-out pgadmin vhost and reloading apache should be enough. Once it's done, backing up of working setup should be the first thing to do, after which we need to change ownership of folders configured in config_distro.py, the ones pgadmin writes to, back to pgadmin:pgadmin:

user@server:~ % sudo chown -R pgadmin:pgadmin /var/log/pgadmin4/
user@server:~ % sudo chown -R pgadmin:pgadmin /var/db/pgadmin4/
user@server:~ % sudo chown -R pgadmin:pgadmin /var/run/pgadmin4/

Let's source pgadmin's python virtualenv:

user@server:~ % source /usr/local/www/pgadmin4/bin/activate.csh

Upgrading is done the same way as installing, by passing URL with newer version to pip install:

Below command is not copypastable - make sure to specify real URL

(pgadmin4) user@server:~ % sudo -u pgadmin pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.XX/pip/pgadmin4-4.XX-py2.py3-none-any.whl

We'll run setup once again:

(pgadmin4) user@server:~ % sudo -u pgadmin python /usr/local/www/pgadmin4/lib/python3.6/site-packages/pgadmin4/setup.py`

...after which we need to revert ownership of folders configured in config_distro.py back to www:www:

user@server:~ % sudo chown -R pgadmin:pgadmin /var/log/pgadmin4/
user@server:~ % sudo chown -R pgadmin:pgadmin /var/db/pgadmin4/
user@server:~ % sudo chown -R pgadmin:pgadmin /var/run/pgadmin4/

Start apache server, or reload it after including pgadmin vhost, and use your browser to log into updated pgadmin4.

Credits

I eyed pgadmin4 for a long time, but having no previous experience vith python virtual environments, I prefer to use FreeBSD ports and packages for these kind of applications. Unfortunately, at the time of this writing, there's no official FreeBSD port of pgadmin4.

Besides Official Documentation, the following two articles helped me a lot to set up my first pgadmin instance:

Got any feedback? Drop me a line.

Previous Post