Olav Grønås Gjerde

profile image
System architect with passion for Unix, Java, Python and machine learning.
3 months ago

Upgrade pgAdmin4 to a new version

If you followed my earlier blog How to install and configure pgAdmin4 for FreeBSD and with uWSGI you will see it was written for version 4.6 of pgAdmin4.

Upgrading pgAdmin4 isn't difficult, on fact you only need todo 3 steps to complete the upgrade:

First step, activate the virtual environment

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

Second step, upgrade pgAdmin4

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

Third and last step, restart uwsgi

$ sudo service uwsgi restart

That's it, super simple!

4 months ago

Install and configure pgAdmin4 for FreeBSD with UWSGI and Nginx

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;
  }
}
5 months ago

Default Sequence Generator Strategy for Grails 3 and Grails 4

When using PostgreSQL together with Grails, one thing that has annoyed me when creating domain classes is that I have to manually add a line for telling hibernate how it should create, name and use the sequences.

Finally Hibernate now has a new SequenceStyleGenerator this solves this:

Create your application.groovy file, application.yml will not work and write this:

grails.gorm.default.mapping = {
    id (
            generator: 'org.hibernate.id.enhanced.SequenceStyleGenerator',
            params: [prefer_sequence_per_entity: true]
    )
}
5 months ago

Secure cookie flag for Grails 3

Add this to your application.yml

server:
    session:
        cookie:
            domain: adminpanel-test-boost.ai
            http-only: true
            path: /
            secure: true

View older blog posts ⇾