Install (and Secure) PgAdmin as a Web App on Ubuntu 20.04

You can follow this tutorial if you want your PgAdmin interface to be available on a public server that is accessible to other people as well. Usually I try to make the tutorials as short as possible, so I wrote a shorter version for people who want to do this using Docker (link here). 

Now my own experience is that it is more robust to do it old school – for now at least. And if you want to do things old school it will take multiple steps. So please go ahead and make yourself a good cup of coffee for this one ūüôā

 

  

Step 1: Install PostgreSQL

I can’t explain this one better than the official documentation – don’t worry it is pretty short => Install PostgreSQL

  

Step 2: Enable Remote Access to PostgreSQL

I personally use vim (despite Richard Stallman’s rather dull comments on VI), but of course feel free to use your own favorite code editor for all that follows.

 

First edit the PostgreSQL Client authentication configuration file

# Command line
$ sudo vim /etc/postgresql/12/main/pg_hba.conf

Update the lines to

# "local" is for Unix domain socket connections only
local   all         	all                                 	trust
# IPv4 local connections:
host	all         	all         	0.0.0.0/0           	md5
# IPv6 local connections:
host	all         	all         	::1/128             	md5

Then edit PostgreSQL’s Parameter Configuration File now

# Command line
$ sudo vim /etc/postgresql/12/main/postgresql.conf

Update the lines to

listen_addresses = '*'
# FYI, this will allow listening for all IPv4 addresses 

 

At this point it is a good idea to test that everything worked as planned. So you can use this command to test the connection (from another machine ideally, and of course the username, server domain name and database name should be your own).

# Command line
$ PGPASSWORD=’postgres-password’ psql -h server-domain-or-ip -U postgresql-username -d database-name

 

Step 3: Install pgAdmin as a Web App

Note I have adapted these steps for Ubuntu-20.04 and for the latest version of pgadmin, but the originals come from this tutorial which works fine for Ubuntu 16.04.

 

# Command line
$ sudo apt update
$ sudo apt install build-essential libssl-dev libffi-dev libgmp3-dev virtualenv python3-pip libpq-dev python3-dev
$ sudo apt install apache2 apache2-utils libapache2-mod-wsgi libexpat1 ssl-cert python3

Before you proceed with the next steps, please visit this link and replace the pgadmin .whl file with the latest version that is available (or the version that you want to install).

# Command line
$ cd $HOME
$ virtualenv pgadmin
$ cd pgadmin
$ . bin/activate
$ wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v5.7/pip/pgadmin4-5.7-py3-none-any.whl
$ pip install pgadmin4-5.7-py3-none-any.whl

 

Here I am setting the logs to /var/log and /var/lib but of course you can change to what you prefer.  

# Command line
$ vim lib/python3.8/site-packages/pgadmin4/config_distro.py

Add the following lines or modify if you want the logs to be stored elsewhere 

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'

 

If you have set the paths like I did, the following is a temporary fix to be able to run the setup for PgAdmin. First, we will create these paths because they don’t exist yet. Then, for a short period of time, we will make you *yes you* the owner of these paths.

# Command line
cd /var/log/
sudo mkdir pgadmin4/
sudo chown -R $(whoami):$(whoami) /var/log/pgadmin4/

cd /var/lib/
sudo mkdir pgadmin4/
sudo chown -R $(whoami):$(whoami) /var/lib/pgadmin4/

OK now we are ready to run the setup

# Command line
python lib/python3.8/site-packages/pgadmin4/setup.py

At some point you will be asked to type an email address and a password, please store them somewhere, as you will use them to connect to the Web App later.

Now you can leave the Python virtual environment

# Command line
deactivate

It is also time to restore the PgAdmin paths to their rightful owner.

# Command line
sudo chown -R www-data:www-data /var/lib/pgadmin4/ 
sudo chown -R www-data:www-data /var/log/pgadmin4/

 

Step 4: Apache Server Setup

First, let’s install Apache if it is not done yet

# Command line
sudo apt update
sudo apt install apache2

Also you will need this for Python3

# Command line
sudo apt-get install libapache2-mod-wsgi-py3

Now, you have two options (A) Run your PgAdmin using HTTP or (B)Run your PgAdmin using HTTPS. I strongly advise you to use HTTPS, but for that you will need an SSL certificate. So for the sake of completion I will show how to do both methods.

# Command line
sudo vim  /etc/apache2/sites-available/pgadmin4.conf

Regardless of the option you choose, do not forget to replace the red text so it matches your own configuration.

 

Option (A): HTTP

Contents of /etc/apache2/sites-available/pgadmin4.conf
<VirtualHost *:80>
       ServerName your-server-hostname
       LogLevel debug
       ErrorLog /var/log/apache2/pgadmin-error.log
       CustomLog /var/log/apache2/pgadmin-access.log combined
       LoadModule wsgi_module modules/mod_wsgi.so
       WSGIDaemonProcess pgadmin processes=1 threads=25 python-home=/home/your-username/pgadmin
       WSGIScriptAlias / /home/your-username/pgadmin/lib/python3.8/site-packages/pgadmin4/pgAdmin4.wsgi

       <Directory "/home/your-username/pgadmin/lib/python3.8/site-packages/pgadmin4/">
       WSGIProcessGroup pgadmin
       WSGIApplicationGroup %{GLOBAL}
       Require all granted
       </Directory>
</VirtualHost>

Option (B): HTTPS

Contents of /etc/apache2/sites-available/pgadmin4.conf
<VirtualHost *:80>
  ServerName your-server-hostname
  Redirect / https://your-server-hostname/
</VirtualHost>

<VirtualHost _default_:443>
       ServerName your-server-hostname
       LogLevel debug
       ErrorLog /var/log/apache2/pgadmin-error.log
       CustomLog /var/log/apache2/pgadmin-access.log combined
       LoadModule wsgi_module modules/mod_wsgi.so
       WSGIDaemonProcess pgadmin processes=1 threads=25 python-home=/home/your-username/pgadmin
       WSGIScriptAlias / /home/your-username/pgadmin/lib/python3.8/site-packages/pgadmin4/pgAdmin4.wsgi

      <Directory "/home/your-username/pgadmin/lib/python3.8/site-packages/pgadmin4/">
       WSGIProcessGroup pgadmin
       WSGIApplicationGroup %{GLOBAL}
       Require all granted
        </Directory>

       SSLEngine on
       SSLCertificateFile /path/to/*.crt
       SSLCertificateKeyFile /path/to/*.key
       SSLCertificateChainFile /path/to/*.ca-bundle

</VirtualHost>

 

Finally,¬† let’s enable the config and reload Apache

# Command line
apachectl -t
sudo a2enmod ssl
sudo a2ensite pgadmin4.conf
sudo service apache2 reload

And voila! You can visit your website hostname or ip address, you should find PgAdmin waiting for you there ūüôā

 

Debugging commands

If PgAdmin doen’t show up, here are some commands to help you debug the thing

$ sudo tail -f /var/log/apache2/error.log
$ sudo tail -f sudo tail -f /var/log/apache2/pgadmin-error.log

One last thing though: if you are doing all this on a Cloud server, don’t forget to enable the ports 80 (HTTP) or 443 (HTTPS) in your networking options for this particular server.