PostgreSQL e pgAdmin4 su ubuntu

Installazione di PostgreSQL e pgAdmin4 in un server Ubuntu

Premessa

PostgreSQL è un database server molto performate e potente, è meno diffuso di MySQL perché il secondo è standard nei server LAMP, ma ha prestazioni, sicurezza e scalabilità migliori e per questo motivo è il database di riferimento di molte applicazioni e quando le applicazioni lo supportano come alternativa è sicuramente da preferire.

Chi amministra un database spesso ha bisogno di un buon tool di manutenzione. In ambito MySQL lo standard è PHPMyAdmin che solitamente è presente nei repositories delle distribuzioni linux e quindi molto facile da installare.

Per PstgreSQL la situazione è un po' più complessa, in passato esisteva una applicazione web di nome PHPpgAdmin che però non viene più aggiornata dal 2013, era anche disponibile una applicazione desktop di nome pgAdmin3 che però non essendo web based ha un ambito limitato al desktop.

Attualmente il tool di riferimento per l'amministrazione di PostgreSQL è pgAdmin4: https://www.pgadmin.org/ che è una applicazione web moderna e completa ma che è un po' più complicata da installare.

Questo tutorial prende in considerazione l'installazione su un server (tipicamente una VPS) e propone due metodi alternativi di installazione di pgAdmin4 il primo a mio parere preferibile per l'installazione su servere ed il secondo per l'installazione nella propria macchina desktop locale.

Il sistema operativo di riferimento è ubuntu 18.04 server

Installazione di PostgreSQL

riferimenti:

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04

Con Ubuntu è facile, dopo aver aggiornato la macchina ed aver installato lo stack LAMP e tutto quello che serve per la propria VPS, installiamo PostgreSQL con:

   $ apt update && apt upgrade
   $ apt install postgresql postgresql-contrib

Creiamo anche un utente che useremo come primo utente PostgreSQL con la possibilità di installarne altri in seguito.

   $ adduser pguser
   $ adduser pguser sudo
   $ usermod -s /bin/false pguser

Per motivi di sicurezza viene revocato l'uso della shell così l'utente potrà operare esclusivamente quando viene abilitato dall'utente root con il comando su.

Ora proviamo la console di PostgreSQL e creiamo un utente ed un database. Le operazioni a basso livello vengono svolte dall'utente postgres che viene aggiunto automaticamente in fase di installazione di PostgreSQL, l'utente postgres è speciale e non si dovrebbe usare da pgAdmin4, anche se non è difficile abilitarlo.

Ora attiviamo l'utente pguser ed in cascata l'utente postgres e proviamo l'ambiente interattivo.

   $ su -s /bin/bash pguser
   $ cd ~
   $ sudo -i -u postgres
   $ psql
   $ \q
   $ exit

Creiamo un utente PostgreSQL ed il suo database, i nomi di utente e database saranno uguali all'utente linux, in questo modo il database riconosce automaticamente le credenziali da usare in funzione dell'utente.

   $ sudo -u postgres createuser --interactive
   $ sudo -u postgres createdb pguser

Ovviamente l'utente si chiamerà pguser e gli assegnamo i privilegi di amministratore.

Per poter accedere da pgAdmin4 come utente è necessario attivare la gestione della password e si deve assegnare il metodo di autenticazione con richiesta di password. Ora l'utente corrente è ancora pguser e quindi possiamo dare i comandi:

   $ psql
   $ \password
   $ \q

Ora però se torniamo a dare il comando psql, non ci viene ancora chiesta la password, per abilitarla è necessario configurare PostgreSQL per accettare accessi con password

   $ sudo nano /etc/postgresql/10/main/pg_hba.conf

e nel testo facciamo questa modifica:

   # cambiamo da:
   local   all             all                                     peer
   # a:
   local   all             all                                     md5

Notiamo che l'utente postgres ha ancora l'accesso peer che è più sicuro di md5 perché consente l'accesso senza password, ma solo se l'utente corrente della console è quello che accede. Noi per tutti gli altri utenti consentiamo l'accesso con password perché abbiamo necessità di accedere estermnamente, però in questo file si possono impostare parametri più articolati.

Ora salviamo e riavviamo il server

   $ sudo service postgresql restart

e possiamo provare l'accesso con password:

   $ psql
   $ \p

come possiamo notare ora per entrare ci viene chiesta la password.

e possiamo ritornare all' utente root:

   $ exit

Proponiamo due modi diversi di installare pgAdmin4, alla fine il risultato che si raggiunge è lo stesso, la scelta del metodo dipende da alcuni parametri che vengono spiegati nelle sessioni che seguono.

Installazione di pgAdmin4 con il pacchetto deb

riferimenti:

https://www.pgadmin.org/
https://wiki.postgresql.org/wiki/Apt

Questa versione rispetto alla successiva, in caso di installazione su un server VPS, ha il vantaggio di essere più semplice e veloce e che viene automaticamente aggiornata dai server, per contro è globale rispetto alla macchina e quindi potrebbe generare conflitti con i pacchetti python utilizzati da altre applicazioni.

La documentazione è un po' laconica e soprattutto non prevede una automatismo per l'installazione come service. In queso momento supero questa carenza attivando un servizio cron che lo fa ripartire quando il servizio non è attivo.

ecco i comandi:

   $ apt install gnupg2
   $ apt install wget ca-certificates
   $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
   $ sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
   $ apt update && apt upgrade
   $ apt install pgadmin4

Fatto! pgAdmin4 è installato, ma non è ancora operativo, per attivarlo dopo un po' di tentativi ho trovato questa soluzione:

   $ python3 /usr/share/pgadmin4/web/pgAdmin4.py

Questo fa partire l'applicativo web pgAdmin4, ma la prima volta chiede che venga inserito l'indirizzo email del utente pgAdmin4 e la sua password, che non sono da confondere con gli utenti di PostgreSQL perché permettera solamente di accedere all'applicativo.

Premiano CTRL+C per uscire dal server ed ora se rientriamo con:

   $ python3 /usr/share/pgadmin4/web/pgAdmin4.py

il server viene avviato

Ora manca la parte del test del server, del avvio come servizio cron e della attivazione del VirtualHost di Apache, ma siccome questa parte è comune alla soluzione che segue verrà trattata alla fine della seconda versione.

Installazione di pgAdmin4 con virtualenv

riferimenti:

https://www.pgadmin.org/
https://kiahosseini.github.io/help/2016/10/18/installing-pgadmin4-ubuntu-16.04.html

Questa versione rispetto alla precedente, a mio parere è più adeguata per l'installazione nella propria macchina desktop perché fa uso in un ambiente isolato con virtualenv. Nel tutorial citato, si spiegna anche come aggiungere una voce di menu per attivare il programma dall'ambiente GUI.

Per contro è un po' più complessa e non usufruisce degli aggiornamenti automatici. Comunque la soluzione qui proposta va bene per il nostro server VPS.

Tutte le operazioni per questa versione vengono eseguite dall'utente pguser:

   $ su -s /bin/bash pguser
   $ sudo apt install python2.7-dev virtualenv python-pip libpq-dev
   $ mkdir -p ~/apps/pgadmin4/
   $ cd ~/apps/pgadmin4
   $ virtualenv venv -p /usr/bin/python2.7
   $ source ./venv/bin/activate

Con questo abbiamo creato l'ambiente virtuale in cui verrà fatta l'installazione. ora installiamo pgAdmin4

   $ wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v3.2/pip/pgadmin4-3.2-py2.py3-none-any.whl
   $ pip install pgadmin4-1.4-py2.py3-none-any.whl

La URL indicata si riferisce all'ultima versione disponibile al momento che sto scrivendo, ovviamente sarà opportuno scegliere la versione più recente.

L'installazione è fatta, ma serve fare ancora alcune cose:

   $ cp ./venv/lib/python2.7/site-packages/pgadmin4/config.py \
        ./venv/lib/python2.7/site-packages/pgadmin4/config_local.py

Con questo comando si attiva il file di configurazione locale. La versione attuale di defautl installa pgAdmin4 come server per cui è necessario concedere al nostro utente l'accesso a due cartelle che normalmente sono riservate a root:

   $ sudo mkdir "/var/log/pgadmin"
   $ sudo chmod a+wrx "/var/log/pgadmin"
   $ sudo mkdir "/var/lib/pgadmin"
   $ sudo chmod a+wrx "/var/lib/pgadmin"

ora possiamo provare il server:

   $ python ./venv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py

Come per la versione precedente, anche in questo caso al primo avvio vengono chieste le credenziali del utente di pgAdmin4, ed ancora una volta dopo averle inserite, uscimo con CTRL-C.

Nella mia installazione il driver python per il database mi consiglia di installare una versione diversa, probabilmente per motivi di prestazioni, con:

   $ pip install psycopg2-binary

Ora se riavvio il server, dovrei essere opeativo:

   $ python ./venv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py

verificato che tutto funziona, posso uscire da virtualenv e dall'utente pguser e ritornare a root con:

   $ exit
   $ exit

Test del database ed installazione del servizio cron

Per verificare se il nostro sistema funziona, dato che la macchina non dispone di un ambiente grafico, per fare il test utilizziamo il browser in modalità testo links, usiamo anche il comando screen per attivare il server prima di fare il test, da root digitiamo:

   $ apt install links screen

Ora i comandi sono diversi se abbiamo installto il pacchetto deb oppure il virtualenv, con il pacchetto deb digitiamo:

   $ screen python3 /usr/share/pgadmin4/web/pgAdmin4.py

il comando screen ci permette di attivare un processo figlio di quello attuale che può essere mantenuto attivo mentre facciamo altro, per ritornare alla shell padre digitiamo CTRL-A seguito dal tasto D che esegue il "detouch" da screen. Per ritornare nello screen basta dare il comando

   $ screen -r

Se abbiamo optato invece per la versione virtualenv dobbiamo eseguire:

   $ su -s /bin/bash pguser
   $ cd ~/apps/pgadmin4
   $ virtualenv venv -p /usr/bin/python2.7
   $ source ./venv/bin/activate

e poi:

   $ screen python ./venv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py

anche in questo caso eseguiamo il detouch con CTRL-A seguito dal tasto D

ora possiamo verificare se il servizio è attivo con:

   $ ps -e | grep python

che ci fa vedere i processi attivi di python, probabilmente ce ne sarà solo uno ed è il nostro server.

Ora vediamo se effettivamente il nostro server risponde con:

   $ links

Viene avviato il browser e se navighiamo su hhtp://127.0.0.1:5050 dopo un po' dovrebbe uscire la pagina di login. Il browser links non ha la capacità di interagire con una applicazione complessa come pgAdmin4, ma se esce la pagina di login significa se il programma funziona.

E se funziona, è giunto il momento di attivare il servizio cron.

Possiamo chiudere il server rientrando in screen con ```screen -r``` e premendo CTRL-C ed ora i comandi sono diversi a seconda della soluzione scelta, ma la sostanza è la stessa. Scriviamo uno script di shell per eseguire il comando che attiva il server:

   $ nano ~/pgadmin_cron.sh

il contenuto per la versione con il pacchetto deb sarà:

   #! /bin/bash

   if ! nc -z localhost 5050 ; then

           python3 /usr/share/pgadmin4/web/pgAdmin4.py

   fi

mentre per la versione virtualenv sarà:

   #! /bin/bash

   if ! nc -z localhost 5050 ; then

           source ~/apps/pgadmin4/venv/bin/activate
           python ~/apps/pgadmin4/venv/local/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py

   fi

Il comando "nc -z localhost 5050" controlla se è attiva la porta 5050 e se non lo è fa partire il server. A questo punto rendiamo eseguibile lo script e lo inseriamo nel sistema cron di linux:

   $ chmod +x ~/pgadmin_cron.sh
   $ crontab -e

inseriamo la regola per far partire il server, aggiungiamo il seguente testo per la versione deb:

   # m h  dom mon dow   command
   */5 *  *   *   *     /root/pgadmin_cron.sh > /dev/null

ed il seguente per la versione virtualenv:

   # m h  dom mon dow   command
   */5 *  *   *   *     /home/pguser/apps/pgadmin4/pgadmin_cron.sh > /dev/null

e con:

   $ service cron reload

o

   $ sudo service cron reload

facciamo ripartire il server cron in modo che riconosca le nuove direttive. In entrame i casi lo script viene lanciato ogni 5 minuti e se per qualche motivo non è attivo, ad esempio perché abbiamo riavviato la macchina, viene riavviato.

se siamo in virtulenv torniamo a root con:

   $ exit

aspettiamo un massimo di 5 minuti e digitiamo:

   $ ps -e | grep python

vedremo che cron ha fatto il suo lavoro perché avremo un processo python attivo.

Configurazione del VirtualHost di Apache con proxy

riferimenti:

https://www.digitalocean.com/community/tutorials/how-to-use-apache-http-server-as-reverse-proxy-using-mod_proxy-extension

Ora abbiamo il server pgAdmin4 funzionante, ma che non è raggiungibile dall'esterno della macchina in qui è stato installato, per poterlo raggiungere è necessario attivare un VirtualHost di apache. Questa parte vale per entrambe le configurazioni.

Se non abbiamo abbiamo ancora installato apache lo possiamo fare subito e dato che ci siamo installiamo tutto lo stack LAMP con:

   $ apt install lamp-server^

per le nostre necessità dobbiamo installare i moduli proxy di apache con:

   $ apt install libapache2-mod-proxy-* libxml2-dev

e li abilitamo con:

   $ a2enmod proxy
   $ a2enmod proxy_http
   $ a2enmod proxy_ajp
   $ a2enmod rewrite
   $ a2enmod deflate
   $ a2enmod headers
   $ a2enmod proxy_balancer
   $ a2enmod proxy_connect
   $ a2enmod proxy_html

Per attivare il VirtualHost abbiamo necessità che ci sia un nome di dominio a cui risponda la macchina, ad esempio potremmo aver attivato i DNS dominio pgadmin.ilmiodominio.it

Scriviamo un nuovo file di configurazione:

   $ nano /etc/apache2/sites-available/pgadmin.ilmiodominio.it.conf

il conetnuto sarà:

    <VirtualHost *:80>

       ServerName pgadmin.ilmiodominio.it

       ErrorLog ${APACHE_LOG_DIR}/error.log
       CustomLog ${APACHE_LOG_DIR}/access.log combined
   
       UseCanonicalName Off
   
       <Proxy *>
               Order deny,allow
               Allow from all
       </Proxy>
   
       ProxyRequests Off
       ProxyPass / http://127.0.0.1:5050/
       ProxyPassReverse / http://127.0.0.1:5050/
   
   </VirtualHost>

salviamo abilitimo il sito e riavviamo il server apache con:

   $ a2ensite pgadmin.cslx.conf
   $ service apache2 restart

ora dal browser della nostra macchina locale dovremo poter avviare pgAdmin4 alla URL:

   http://pgadmin.ilmiodominio.it

Due parole pgAdmin4

Se abbiamo fatto tutto bene, a questo punto pgAdmin4 ci sta chiedendo username e password, inseriamo le credenziali che abbiamo attivato la prima volta che abbiamo avviato il server.

Il compito di questo tutorial non è certo quello di speigare l'interfaccia utente di pgAdmin4 e tanto meno i comandi di PostgreSQL, ma c'è un ultima cosa che dobbiamo fare ed è quella di attivare il server.

pgAdmin4 permette infatti di attivare più server PostgreSQL per utenti diversi ma anche per server residenti su macchine diverse. Noi attiveremo solo il server per l'utente pguser sulla macchina in qui è stato installato.

Facciamo click su servers ed andiamo su Object -> Create -> Server...

Nel tab General inseriamo:

   Name: pguser@pgadmin.ilmiodominio.it

e nel tab Connection inseriamo:

   Host name/address: 127.0.0.1
   Port: 5432
   Maintenance databae: pguser
   Username: pguser

l'indirizzo è 127.0.0.1 perché pgAdmin4 è installato nella stessa macchina in cui risiede il server PostgreSQL.

Ora ci possiamo connettere e fare tutte le operazioni sul database.