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 root@IP_DEL_SERVERSuccessivamente 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 nome_utente@IP_DEL_SERVERSuccessivamente 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 nome_utente@IP_DEL_SERVER -p PORTASiete 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, epassword(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 novielloAdesso possiamo connetterci con il nuovo utente al database ed usare il prompt di postgres:
sudo -i -u noviellopsqlPer connettersi ad un database differente con l'utente corrente:
psql -d postgresPer ottenere informazioni riguardo la connessione corrente:
\conninfoDovresti 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 psqlEsegui 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;Salvare il file e riavviare il servizio PostgreSQL:
sudo service postgresql restartVerifica le modifiche con l'utility ss:
ss -nlt | grep 5432L'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
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 5432Assicurarsi 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.
