Olav Grønås Gjerde

This guide will help you install pgAdmin4 on your FreeBSD machine. What is pgAdmin? It is a free open source graphical management tool for PostgreSQL and derivative relational databases such as EnterpriseDB's EDB Advanced Server. It has a powerful query tool with colour syntax highlighting and graphical query plan display. For people that is not familiar with the Linux shell and the psql client, pgAdmin4 is an excellent tool for interacting with PostgreSQL.

(Screenshot of pgAdmin4)

FreeBSD Setup

Update ports

$ sudo portsnap fetch && portsnap extract

Install necessary packages (you can omit BATCH if you want to configure installations)

$ sudo make -C /usr/ports/lang/python36 install clean BATCH=yes
$ sudo make -C /usr/ports/devel/py-setuptools install clean FLAVOR=py36 BATCH=yes
$ sudo make -C /usr/ports/devel/py-virtualenv install clean FLAVOR=py36 BATCH=yes
$ sudo make -C /usr/ports/databases/py-sqlite3 install clean FLAVOR=py36 BATCH=yes
$ sudo make -C /usr/ports/databases/py-psycopg2 install clean FLAVOR=py36 BATCH=yes
$ sudo make -C /usr/ports/www/uwsgi install clean BATCH=yes

We use Python 3.6 as that is currently working with pgAdmin4 version 4.6.

Setup Python Environment

Create pgadmin app folder and data folder

$ sudo mkdir -p /opt/pgadmin/data

Create virtual python environment

$ sudo virtualenv --python=/usr/local/bin/python3.6 /opt/pgadmin/pgadm_env

Activate virtual env (make sure you are using bash, not sh)

$ source /opt/pgadmin/pgadm_env/bin/activate

Install and configure pgAdmin4

Check the latest release of pgAdmin4 here https://ftp.postgresql.org/pub/pgadmin/pgadmin4/

(pgadm_env)$ sudo pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.6/pip/pgadmin4-4.6-py2.py3-none-any.whl

We also need a local uwsgi binary

(pgadm_env)$ sudo pip install uwsgi

Create local pgadmin config file

$ sudo vim /opt/pgadmin/pgadm_env/lib/python3.6/site-packages/pgadmin4/config_local.py

Add following content to config_local.py

import os
DATA_DIR = os.path.realpath(os.path.expanduser(u'/opt/pgadmin/data'))
LOG_FILE = os.path.join(DATA_DIR, 'pgadmin4.log')
SQLITE_PATH = os.path.join(DATA_DIR, 'pgadmin4.db')
SESSION_DB_PATH = os.path.join(DATA_DIR, 'sessions')
STORAGE_DIR = os.path.join(DATA_DIR, 'storage')

Create PGAdmin superuser

(pgadm_env)$ sudo python /opt/pgadmin/pgadm_env/lib/python3.6/site-packages/pgadmin4/setup.py

NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: myemail@example.org
Password: 
Retype password:
pgAdmin 4 - Application Initialisation
======================================

Create pgadmin user

$ sudo pw useradd pgadmin

Make user pgadmin the owner of the pgadmin installation

$ sudo chown -R pgadmin:pgadmin /opt/pgadmin

Configure UWSGI

This is the most annoying and probably hardest part. Hopefully this guide will help you through this.

First we want to use UWSGI Emperor mode, it makes it possible for us to to host multiple python applications through UWSGI. Even with different python version.

Create Vassals folder for UWSGI

$ sudo mkdir -p /usr/local/etc/uwsgi/vassals

Create pgadmin4.ini

$ sudo vim /usr/local/etc/uwsgi/vassals/pgadmin4.ini

[uwsgi]
uid = pgadmin
gid = pgadmin
socket = /tmp/pgadmin4.sock
chmod-socket = 666
logto = /var/log/uwsgi.log
chdir = /opt/pgadmin/pgadm_env/lib/python3.6/site-packages/pgadmin4
wsgi-file = pgAdmin4.wsgi
virtualenv = /opt/pgadmin/pgadm_env
unprivileged-binary-patch = /opt/pgadmin/pgadm_env/bin/uwsgi
threads = 10

Edit /etc/rc.conf

uwsgi_enable="YES"
uwsgi_flags="--emperor /usr/local/etc/uwsgi/vassals"

Now you can start UWSGI

$ sudo service uwsgi start

You still need to configure Nginx though

Configure Nginx

Create a new virtual host file for nginx that looks like tihs

upstream pgadmin_upstream {
  server unix:/tmp/pgadmin4.sock;
}

server {
      listen 80;
      server_name pgadmin.example.org;
      access_log off;
      error_log off;
      ## redirect http to https ##
      return 301 https://$server_name$request_uri;
}

# HTTPS server
server {
  listen 443 ssl http2;
  server_name pgadmin.example.org;

  ssl_certificate      /path/to/my/ssl/server.crt;
  ssl_certificate_key   /path/to/my/ssl//server.key;
  ssl_session_timeout  5m;

  error_page   500 502 503 504  /50x.html;
    # Only allow local network
  allow 192.168.1.0/24; 
  deny all;
  
  ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
  ssl_ciphers EECDH AES128:RSA AES128:EECDH AES256:RSA AES256:EECDH 3DES:RSA 3DES:EECDH RC4:RSA RC4:!MD5;
  ssl_prefer_server_ciphers on; 

  location / {
    uwsgi_pass pgadmin_upstream;
    include uwsgi_params;
    uwsgi_modifier1 30;
  }
}