PostgreSQL admin tool behind nginx

Question

I have a VPS with PostgreSQL installed. I have a nginx setup with a some reverse proxies to different tools, grafana, Node.js sites and so on. I'm trying to setup PostgreSQL right now, and I just can't work out the web based admin tool, one that is reachable from outside.

I tried installing normal pgAdmin with Apache2 hosting, but that obviously conflicted with nginx. I've tried to find a good guide on pgAdmin behind a nginx reverse proxy, but the only one I found (https://github.com/rbernardes/nginx-pgadmin) did not work at all when I tried to run the py config script. I could dive into that harder, but my point is; pgAdmin does not seem to play well with nginx, and I thought there must be a valid web based alternative? But it seems a bit dry here...

Can anybody shed some light on this? Thanks!

Answer 1

Here is much simpler way:

First Install PgAdmin from ftp.postgresql.org repository:

curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add -
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'
sudo apt update
sudo apt install pgadmin4 

Here is when the Fun part begins, Open sudo vim /etc/apache2/ports.conf and comment out 80 and 443 ports and add new port 5050 or any port you desire. apache ports.conf

Now lets check sudo vim /etc/apache2/conf-available/pgadmin4.conf this is where pgadmin runs from [WSGI][2].

Remove the pgadmin front IN of WSGIScriptAlias an the code should look like this.

WSGIDaemonProcess pgadmin processes=1 threads=25 python-home=/usr/pgadmin4/venv
WSGIScriptAlias / /usr/pgadmin4/web/pgAdmin4.wsgi

<Directory /usr/pgadmin4/web/>
    WSGIProcessGroup pgadmin
    WSGIApplicationGroup %{GLOBAL}
    Require all granted
</Directory>

[comment]: Do not add more processes (you will get CSRF error), add more threads If you whant.

Now lets check our VirtualHost sudo vim /etc/apache2/sites-available/000-default.conf, just pass the ssl certificate files and some basic stuff (remember to serve port 5050 or whatever port you chowsen):

<VirtualHost *:5050>
  ServerAdmin [email protected]

  SSLEngine on
  SSLCertificateFile      /etc/ssl/certs/pub.pem
  SSLCertificateKeyFile   /etc/ssl/private/perv.key
  ErrorDocument 404 "looks like you not found the page :("
  ErrorDocument 403 "It's Forbidden!"
  ErrorLog ${APACHE_LOG_DIR}/error.log
  CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>

[Note]: remember to sudo a2enmod ssl, If you are using ssl.

Now complete your PgAdmin installation, set the password and enable WSGI module.

sudo /usr/pgadmin4/bin/setup-web.sh

Check if everything is alright and restart the apache service.

sudo apachectl -t
sudo systemctl restart apache2

Lets see our beloved nginx, It's a simple config but quite unbreakable:)

server {
  listen  443 ssl http2;
  server_name  your.domain.com;

  ssl_certificate     /etc/ssl/certs/pub.pem;
  ssl_certificate_key /etc/ssl/private/perv.key;

  location / {
    proxy_pass https://your.domain.com:5050;
    proxy_set_header Host $http_host;
  }

}

At the end restart the Nginx.

sudo systemctl restart nginx

---

Thanks tecadmin for installing PgAdmin on Ubuntu.

Answer 2

tried installing normal pgAdmin with Apache2 hosting, but that obv. conflicted with nginx

it won't if you configure Apache2 to listen to different port that nginx didn't use.

pgAdmin does not seem to play well with nginx

install and configure uWSGI with pgAdmin app, then configure nginx to use uwsgi_pass to communicate to uWSGI.

---

Edit 2020-05-10: More details and information

You got two solutions here, either use nginx + uwsgi_pass, or Apache httpd + mod_proxy_wsgi. Let's delve into both.

Prerequisites

1. Python and Python development headers

   Install Python and its compatible development headers, preferably Python >=3.6. PCRE 3 libraries are required for uwsgi to handle regex routings.

$ sudo apt-get update && apt-get -y install build-essential python3 python3-dev libpcre3-dev libpcre3

2. Python virtual environment

   Install your pgAdmin on a virtual environment, whether it's venv, virtualenv, conda, anything even; just to make sure that your OS package manager won't interfere with your python environments (versions, packages, etc).

   If you're using venv, it should be something like

$ cd /path/to/working_directory

# Install virtual environment on .env, and activate it.
$ python3 -m venv .env
$ source .env/bin/activate

# upgrade existing pip packages, and install wheel.
(.env) $ pip install --upgrade pip setuptools
(.env) $ pip install wheel

# install pgAdmin 4.21
(.env) $ pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.21/pip/pgadmin4-4.21-py2.py3-none-any.whl

uWSGI and pgAdmin

uWSGI Installation

To let your webserver connect to pgAdmin, you'll use WSGI or HTTP server. pgAdmin supports uWSGI and Gunicorn.

Here, we'll use uWSGI, but if you're using another WSGI server, just make sure that it's listening at UNIX socket /tmp/wsgi/pgadmin.sock

First, you'll need to install uWSGI. You can install uWSGI with apt-get or with pip.

Both are fine; but with pip, you'll need to write your own service file; and with apt-get, you'll need to accept you're using older version of uWSGI. We'll install both, using Ubuntu-distributed service file and pip-distributed latest version uwsgi binary

Install Ubuntu-distributed uwsgi
$ sudo apt-get -y install uwsgi

Install pip-distributed uwsgi
$ source /path/to/working_directory/.env/bin/activate
(.env) $ pip install uwsgi

# Ubuntu-distributed version
(.env) $ /usr/bin/uwsgi --version
2.0.15-debian

# pip-distributed version
(.env) $ uwsgi --version
2.0.18

# service file
(.env) $ ls /etc/init.d/uwsgi
/etc/init.d/uwsgi

pgAdmin Installation

Next, we're setting pgAdmin up for uWSGI. You'll need to get into our virtual environment again. We're roughly following this pgAdmin official guide.

First, create a local configuration.

$ cd /path/to/working_directory/.env/lib/python3.6/site-packages/pgadmin4
$ cp config.py config_local.py
$ vim config_local.py

Note that you can follow pgAdmin's documentation, but we can just edit DATA_DIR = '/var/lib/pgadmin' to anything sensible, because everything else will refer to DATA_DIR. Here, we use /path/to/working_directory/lib

Next, create pgAdmin's required directories

$ sudo mkdir -p /var/log/pgadmin
$ mkdir /path/to/working_directory/lib

/var/log/pgadmin and DATA_DIR should be fully controlled by uWSGI, because pgAdmin will be running under uWSGI. Here, we'll be using www-data as uWSGI user and group.

$ sudo chown www-data:www-data /var/log/pgadmin /path/to/working_directory/lib

Note that we need to allow writes to /var/log/pgadmin and DATA_DIR to let it set up. We only let it be writable by our user now, and we will fix this permission later.

$ sudo chmod 777 /var/log/pgadmin /path/to/working_directory/lib

Enter our virtual environment and run pgAdmin's setup.py.

$ source /path/to/working_directory/.env/bin/activate
(.env) $ cd /path/to/working_directory/.env/lib/python3.6/site-packages/pgadmin4
(.env) $ python setup.py

Follow setup instructions until it's complete, and take note of your email and password created here.

Next, fix /var/log/pgadmin and DATA_DIR permissions.

$ chmod 755 /var/log/pgadmin /path/to/working_directory/lib
$ chown -R www-data:www-data /var/log/pgadmin /path/to/working_directory/lib

pgAdmin should be completely set up by now.

Integrating uWSGI and pgAdmin

Now, we're ready to integrate pgAdmin and uWSGI, creating WSGI server that listens at UNIX socket /tmp/wsgi/pgadmin.sock.

First, using your favorite editor, create a uWSGI configuration file in /etc/uwsgi/apps-available. Let's call it pgadmin.ini.

$ sudo vim /etc/uwsgi/apps-available/pgadmin.ini

It should contain at least:

[uwsgi]
socket = /tmp/wsgi/pgadmin.sock
chdir = /path/to/working_directory/.env/lib/python3.6/site-packages/pgadmin4
manage-script-name = true
venv = /path/to/working_directory/.env/
master = true
processes = 1
threads = 4
mount = /=pgAdmin4:app
log-format = %(addr) - %(user) [%(ltime)] "%(method) %(uri) %(proto)" %(status) %(size) "%(referer)" "%(uagent)"
logger = file:/tmp/wsgi/pgadmin-access.log
uid = www-data
gid = www-data

create required directories, and enable our pgAdmin uWSGI app

$ sudo mkdir -p /tmp/wsgi
$ sudo chown www-data:www-data /tmp/wsgi
$ sudo ln -s /etc/uwsgi/apps-available/pgadmin.ini /etc/uwsgi/apps-enabled/pgadmin.ini

copy default uWSGI file to another service file, and edit it to use pip-installed uWSGI binary.

$ sudo cp /etc/init.d/uwsgi /etc/init.d/uwsgi-venv
$ vim /etc/init.d/uwsgi-venv
# replace DAEMON="/usr/bin/uwsgi" to DAEMON="/path/to/working_directory/.env/bin/uwsgi

uWSGI configuration should be done.

Now, try to start uWSGI.

$ sudo service uwsgi-venv start pgadmin

Here, pgadmin refers to what we've soft-linked in /etc/uwsgi/apps-enabled before. If you linked it as pgadmin.ini, you'll use pgadmin.

Check if uWSGI is running.

$ ps -eF | grep uwsgi
www-data 10582     1  2 32144 91480   0 03:56 ?        00:00:03 /path/to/working_directory/.env/bin/uwsgi --ini /usr/share/uwsgi/conf/default.ini --ini /etc/uwsgi/apps-enabled/pgadmin.ini --daemonize /var/log/uwsgi/app/pgadmin.log
www-data 10593 10582  0 87443  2640   0 03:56 ?        00:00:00 /path/to/working_directory/.env/bin/uwsgi --ini /usr/share/uwsgi/conf/default.ini --ini /etc/uwsgi/apps-enabled/pgadmin.ini --daemonize /var/log/uwsgi/app/pgadmin.log

nginx and uWSGI

If you haven't installed nginx, install it with apt-get.

$ sudo apt-get -y install nginx

If you have already installed nginx, you may need to adjust some configurations. Let me know if you had any trouble then.

Next, let's configure nginx so that it'll pass any requests to uWSGI we configured earlier.

In /etc/nginx/sites-available directory, create a new nginx configuration file. Here, we're using pgadmin.local as virtual hostname.

$ sudo vim /etc/nginx/sites-available/pgadmin.conf

This configuration file should look at least like this:

server {
    listen 80;
    server_name pgadmin.local;

    location / {
        include /etc/nginx/uwsgi_params;
        uwsgi_pass unix:/tmp/wsgi/pgadmin.sock;
    }
}

If you need it to listen to other port, change listen 80; to listen <your_port>;.

Enable this configuration.

$ sudo ln -s /etc/nginx/sites-available/pgadmin.conf /etc/nginx/sites-enabled/pgadmin.conf

Test and reload your new nginx configuration.

 $ sudo nginx -t && sudo service nginx reload

Depending on your configuration, you may need to configure your firewall after this, but after that, you can browse to your configured virtual host with your favorite browser.

 $ firefox http://pgadmin.local

httpd and uWSGI

There's not a lot of difference between httpd and nginx configuration here, you just need to forward http request as WSGI request to WSGI server backend, in this case, using mod_proxy_wsgi.

Install httpd first if you haven't installed it yet.

$ sudo apt-get -y install apache2 apache2-utils

If you have already installed httpd, you may need to adjust some configurations. Let me know if you had any trouble then.

Don't forget to install mod-proxy and mod-proxy-uwsgi, as both these modules are required.

$ sudo apt-get -y install libapache2-mod-proxy-uwsgi

Next, let's configure httpd to pass any requests to uWSGI we configured earlier.

In /etc/apache2/sites-available directory, create a new virtual host configuration file. Here, we're using pgadmin.local as virtual hostname.

$ sudo vim /etc/apache2/sites-available/pgadmin.conf

This configuration file should look at least like this:

<VirtualHost *:80>
  ServerName pgadmin.local
  ProxyRequests off
  ProxyPass "/" "unix:/tmp/wsgi/pgadmin.sock|uwsgi://localhost/"
</VirtualHost>

If you need it to listen to other port, you'll need a few changes.

1. Change <VirtualHost *:80> to <VirtualHost *:<your_port>>

2. In /etc/apache2/ports.conf, remove all irrelevant Listens including those under <IfModule>s, and add Listen <your_port>.

3. If you still have default site enabled, disable it with a2dissite.

Enable this configuration.

$ sudo ln -s /etc/apache2/sites-available/pgadmin.conf /etc/apache2/sites-enabled/pgadmin.conf

Enable mod_proxy and mod_proxy_uwsgi

$ sudo a2enmod proxy proxy_uwsgi

Test and reload your new httpd configuration.

$ sudo apachectl configtest && sudo service apache2 reload

Depending on your configuration, you may need to configure your firewall after this, but after that, you can browse to your configured virtual host with your favorite browser.

 $ firefox http://pgadmin.local