Introduzione

PostgreSQL, o Postgres, è un sistema di gestione di database relazionali. Ha il vantaggio di essere conforme agli standard e con molte funzionalità avanzate.

In questo tutorial spiegheremo come installare PostgreSQL su una VPS con Ubuntu 20.04 LTS Focal Fossa.

Se desideri installare PostgreSQL su un server in remoto continua a leggere, altrimenti salta il primo paragrafo "Connessione al Server" e leggi il successivo.

Connessione al Server

Per accedere al server, è necessario conoscere l'indirizzo IP. Avrai anche bisogno dell'username e della password per l'autenticazione. Per connettersi al server come utente root digitare il seguente comando:

ssh [email protected]_DEL_SERVER

Successivamente sarà necessario inserire la password dell'utente root.

Se non utilizzate l'utente root potete connettervi con un'altro nome utente utilizzando lo stesso comando, quindi modificare root con il vostro nome_utente:

ssh [email protected]_DEL_SERVER

Successivamente vi verrà chiesto di inserire la password del vostro utente.

La porta standard per connettersi tramite ssh è la 22, se il vostro server utilizza una porta diversa, sarà necessario specificarla utilizzando il parametro -p, quindi digitare il seguente comando:

ssh [email protected]_DEL_SERVER -p PORTA

Siete ora connessi al vostro server, siete pronti per iniziare l'installazione di Postgres.

Installare PostgresSQL

È possibile installare PostgresSQL utilizzando il repository ufficiale di Ubuntu con apt.

Aggiornare la lista dei pacchetti:

sudo apt update

Installare PostgresSQL

sudo apt install postgresql postgresql-contrib

Utilizzare PostgresSQL

Di default Postgres utilizza un concetto chiamato roles per gestire autenticazione e autorizzazione, simili ai normali account in stile Unix.

Al momento dell'installazione, Postgres è impostato per utilizzare l'autenticazione ident, ovvero associa i roles di Postgres a un utente di sistema Linux corrispondente. Se esiste un role all'interno di Postgres, con lo stesso nome di un utente Linux, questo sarà in grado di accedere come tale ruolo.

La procedura di installazione ha creato un account utente chiamato postgres associato al role Postgres predefinito.

Le autorizzazioni di accesso al database in PostgreSQL sono gestite con il concetto di ruoli. Un ruolo può rappresentare un utente del database o un gruppo di utenti del database.

PostgreSQL supporta più metodi di autenticazione. I metodi più comunemente usati sono:

  • Trust: un ruolo può connettersi senza password, purché siano soddisfatte le condizioni definite in pg_hba.conf.
  • Password: un ruolo può connettersi fornendo una password. Le password possono essere memorizzate come scram-sha-256, md5, e password (con testo in chiaro).
  • Ident: supportato solo su connessioni TCP/IP. Funziona ottenendo il nome utente del sistema operativo del client, con una mappatura del nome utente opzionale.
  • Peer: identico a Ident, ma è supportato solo su connessioni locali.

L'autenticazione client PostgreSQL è definita nel file di configurazione denominato pg_hba.conf. Per impostazione predefinita, PostgreSQL utilizza il metodo di autenticazione peer per le connessioni locali.

L'utente postgres viene creato automaticamente quando si installa PostgreSQL. Questo utente è il superutente per l'istanza PostgreSQL ed è equivalente all'utente root di MySQL.

Per utilizzare questo account e per accedere a postgres ci sono diverse procedure.

Per loggare con l'utente postgres, da terminale:

sudo -i -u postgres

Quindi per accedere al prompt di Postgres e gestire il database, scrivere;

psql
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

postgres=#

Per uscire dal prompt di Postgres, scrivere:

\q

Creare un nuovo Role

Attualmente, hai solo il role di postgres configurato all'interno del database. È possibile creare nuovi roles dalla riga di comando con il comando createrole. L'opzione --interactive ti chiederà il nome del nuovo ruolo e se abilitare i permessi di superutente.

Se avete ancora il prompt di postgres attivo, scrivere:

\q

Se siete loggati con l'account postgres, è possibile creare un nuovo utente con il seguente comando:

createuser --interactive

Se siete loggati con un'altro account non ancora associato a postgres, utilizzare il seguente comando:

sudo -u postgres createuser --interactive

Vi verrà chiesto di inserire un nome per il nuovo role e di associargli i privilegi di superuser


Enter name of role to add: noviello
Shall the new role be a superuser? (y/n) y

Creare un nuovo Database

Il sistema di autenticazione di Postgres di default associa il role ad un database con lo stesso nome.

Quindi, se l'utente creato precedentemente è chiamato noviello, tale role tenterà di connettersi a un database che viene anche chiamato noviello per impostazione predefinita. È possibile creare il database appropriato con il comando createdb.

Se siete loggati con l'account postgres, è possibile creare un nuovo database con il seguente comando:

createdb noviello

Se siete loggati con un'altro account non ancora associato a postgres, utilizzare il seguente comando:

sudo -u postgres createdb noviello

Per accedere al prompt di Postgres con il nuovo role appena creato, è necessario che esista un utente Linux, in questo caso su Ubuntu, con lo stesso nome dato al role ed al database.

Se non esiste un utente con questo nome, è possibile crearne uno con il comando adduser.

Effettuare questa procedura con un account utente che non sia postgres.

Per creare un nuovo utente:

sudo adduser noviello

Adesso possiamo connetterci con il nuovo utente al database ed usare il prompt di postgres:

sudo -i -u noviello
psql

Per connettersi ad un database differente con l'utente corrente:

psql -d postgres

Per ottenere informazioni riguardo la connessione corrente:

\conninfo

Dovresti visualizzare un messaggio di risposta simile a questo:


You are connected to database "noviello" as user "noviello" via socket in "/var/run/postgresql" at port "5432".

Aggiungere ed eliminare dati nelle tabelle

Il seguente è un esempio per creare una tabella:

CREATE TABLE test (
    id serial PRIMARY KEY,
    esempio1 varchar (50) NOT NULL,
    esempio2 varchar (25) NOT NULL,
    esempio3 varchar(25) check (esempio3 in ('valore1', 'valore2', 'valore3', 'valore4', 'valore5', 'valore6', 'valore7', 'valore8')),
    datains date
);

Per visualizzare la tabella appena creata:

\d

Dovresti visualizzare un messaggio di risposta simile a questo:


                  List of relations
 Schema |          Name           |   Type   | Owner
--------+-------------------------+----------+---------
 public | test                    | table    | noviello
 public | testidseq             | sequence | noviello
(2 rows)

Per visualizzare la tabella senza ulteriori informazioni:

\dt

Dovresti visualizzare un messaggio di risposta simile a questo:


          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+---------
 public | test       | table | noviello
(1 rows)

Per inserire dei nuovi valori nella tabella:

INSERT INTO test (esempio1, esempio2, esempio3, datains) VALUES ('valore1', 'valore2', 'valore3', '2018-06-29');
INSERT INTO test (esempio1, esempio2, esempio3, datains) VALUES ('valore4', 'valore5', 'valore6', '2018-06-29');

Per recuperare le informazioni appena inserite:

SELECT * FROM test;

Dovresti visualizzare un messaggio di risposta simile a questo:


 id | esempio1 | esempio2 | esempio3 |  datains
----+-----------+-----------+-----------+------------
  1 | valore1  | valore2  | valore3  | 2018-06-29
  2 | valore4  | valore5  | valore6  | 2018-06-29
(2 row)

Per rimuovere una riga dal database:

DELETE FROM test WHERE esempio1 = 'valore1';

Recuperare nuovamente le informazioni dal database per verificare la corretta eliminazione della riga:

SELECT * FROM test;

Dovresti visualizzare un messaggio di risposta simile a questo:


 id | esempio1 | esempio2 | esempio3 |  datains
----+-----------+-----------+-----------+------------
  2 | valore4  | valore5  | valore6  | 2018-06-29
(1 row)

Eliminare e aggiungere colonne nelle tabelle

Per aggiungere una colonna al database:

ALTER TABLE test ADD altro date;

Per visualizzare le modifiche ripetere il comando seguente:

SELECT * FROM test;

Dovresti visualizzare un messaggio di risposta simile a questo:


 id | esempio1 | esempio2 | esempio3 |  datains  |  altro
----+-----------+-----------+-----------+------------+-----------
  2 | valore4  | valore5  | valore6  | 2018-06-29 | 
(1 row)

Per eliminare una colonna:

ALTER TABLE test DROP altro;

Per visualizzare le modifiche ripetere il comando seguente:

SELECT * FROM test;

Dovresti visualizzare un messaggio di risposta simile a questo, quindi senza la colonna creata in precedenza:


 id | esempio1 | esempio2 | esempio3 |  datains  
----+-----------+-----------+-----------+------------
  2 | valore4  | valore5  | valore6  | 2018-06-29 
(1 row)

Modificare i dati in una tabella

Per modificare dei dati è necessario specificare il nome del database e la colonna che si vuole modificare:

UPDATE test SET esempio2 = 'nuovovalore' WHERE esempio1 = 'valore4';

Per visualizzare le modifiche ripetere il comando seguente:

SELECT * FROM test;

Dovresti visualizzare un messaggio di risposta simile a questo:


 id | esempio1 | esempio2 | esempio3 |  datains
----+-----------+-----------+-----------+------------
  2 | valore4  | nuovovalore  | valore6  | 2018-06-29
(1 row)

Creare ruolo e database PostgreSQL

Solo i superutenti e i ruoli con privilegio CREATEROLE possono creare nuovi ruoli.

L'esempio seguente mostra come creare un nuovo ruolo chiamato john e un database chiamato  johndb e concedere i privilegi sul database:

Crea un nuovo ruolo PostgreSQL:

sudo su - postgres -c "createuser john"

Crea un nuovo database PostgreSQL:

sudo su - postgres -c "createdb johndb"

Per concedere le autorizzazioni all'utente sul database, connettersi alla shell PostgreSQL:

sudo -u postgres psql

Esegui la seguente query:

grant all privileges on database johndb to john;

Abilitare l'accesso remoto al server PostgreSQL

Per impostazione predefinita, il server PostgreSQL è in ascolto solo sull'interfaccia locale 127.0.0.1.

Per abilitare l'accesso remoto al tuo server PostgreSQL aprire il file di configurazione postgresql.conf e aggiungere listen_addresses = '*' nella sezione CONNECTIONS AND AUTHENTICATION.

sudo nano /etc/postgresql/12/main/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;
/etc/postgresql/12/main/postgresql.conf

Salvare il file e riavviare il servizio PostgreSQL:

sudo service postgresql restart

Verifica le modifiche con l'utility ss:

ss -nlt | grep 5432

L'output mostra che il server PostgreSQL è in ascolto su tutte le interfacce 0.0.0.0:

LISTEN  0        244              0.0.0.0:5432           0.0.0.0:*              
LISTEN  0        244                 [::]:5432              [::]:* 

Il prossimo passo è configurare il server per accettare le connessioni remote modificando il file pg_hba.conf.

Di seguito sono riportati alcuni esempi che mostrano diversi casi d'uso:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane can access all databases from all locations using md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane can access only the janedb from all locations using md5 password
host    janedb          jane            0.0.0.0/0                md5

# The user jane can access all databases from a trusted location (192.168.1.134) without a password
host    all             jane            192.168.1.134            trust
/etc/postgresql/12/main/pg_hba.conf

L'ultimo passo è aprire la porta del tuo firewall 5432.

Supponendo che si stia utilizzando UFW per gestire il firewall e che si desidera consentire l'accesso dalla sottorete 192.168.1.0/24, eseguire il comando seguente:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

Assicurarsi che il firewall sia configurato per accettare connessioni solo da intervalli IP attendibili.

Conclusione

L'installazione di PostgreSQL su Ubuntu 20.04 LTS Focal Fossa è terminata.