====== Veloce introduzione a PostgreSQL ======
Autore: **//Fabio Di Matteo//** \\ Ultima revisione:  **//  14/06/2023  - 17:59   //**  // //

Andremo installare su una macchina Arch Linux il suddetto database e renderlo operati con un database contenente una tabella.

===== Installazione su Arch Linux =====

<file>sudo pacman -S postgresql</file>

Il pacchetto installera' i tutti file necessari a postgresql e creera' un utente di sistema di nome "postgres" .

===== Loggarsi come utente  di sistema postgres =====

<file>sudo -u postgres -i</file>
Sara' opportuno mettere una password.

===== Inizializzare Postgresql =====
La prima volta è necessario configurare il database con il locale e la cartella contenente i file dei nostri db.
<file>initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'</file>

===== Avviare Postgresql =====
<file> sudo systemctl start postgresql.service</file>

===== Creare il primo utente di Postgresql =====
Postgresql ha creato un utente di default (del db) con capacita' amministrative chiamato postgres (come l'utente di sistema che lancia il processo).Noi adesso andremo a creare un nostro utente attraverso l'utility da riga di comando interattiva:
<file> createuser --interactive</file>
Assegnare una password all'utente da shell (comando "psql" avviato da utente postgres)
<file>ALTER USER mioutente WITH PASSWORD 'miapassword'</file>
===== Creare un database =====
Creeremo un database di nome "test0" assegnando la proprieta' a un utente :
<file>createdb test0 -U <nome utente che ha accesso al db> </file>

===== Accedere al db =====
Da utente postgres impartiamo il seguente comando
<file>psql <mio db></file>

===== Dove si trovano i file di configurazione di Postgresql? =====
I file sono essenzialmente 2 e si trovano su Arch in queste percorsi:
<file>
/var/lib/postgres/data/postgresql.conf  (Configurazione principale)
/var/lib/postgres/data/pg_hba.conf      (Permessi di accesso da locale e da rete)
</file>

Se si vuole rendere postgresql accessibile a chiunque da rete basta aggiungere questa riga a **pg_hba.conf**:
<file>host   all             all               all            trust</file>

e assicurarsi che sia presente in **postgresql.con** la direttiva:
<file>listen_addresses = '*'</file>

Una volta modificati i file si deve riavviare il demone con:
<file>sudo systemctl restart postgresql.service</file>

===== Creare tabelle e popolare di dati =====
Creiamo una tabella con i campi id,nome,cognome,email con id chiave primaria autoincrementante.
<code sql>
CREATE TABLE persone(
 id  SERIAL PRIMARY KEY,
 nome VARCHAR (50) ,
 cognome VARCHAR (50) ,
 email VARCHAR (355)  
);
</code> 

Inseriamo qualche dato:
<file>
insert into persone (nome, cognome, email) values ('Fabio','Di Matteo','fabio@mail.com') ;
</file>

===== Utenti =====
Logghiamoci con l'utente di sistema postgres 
<file>sudo -u postgres -i</file>

==== Comandi da terminale per gestire gli utenti ====

^Comando^Descrizione^
|createuser --interactive|Crea un utente |
|dropuser <utente>|Cancella un utente  |

==== Comandi SQL per gestire gli utenti ====

Lanciamo ''psql'' per digitare i seguenti comandi.

^Comando^Descrizione^
|\du|Elenco utenti|
|SELECT rolname FROM pg_roles;|Elenco utenti|
|CREATE ROLE name ;|Crea un utente/ruolo (non abilita al login)|
|CREATE USER utente PASSWORD 'password'|Crea un utente, assegna una password e lo abilita al login|
|ALTER USER mioutente WITH PASSWORD 'miapassword'|sovrascrive password utente|
===== Database =====
Di seguito alcuni comandi per gestire i database. Lanciamo ''psql <nome database>'' per digitare i seguenti comandi.

^Comando^Descrizione^
|\l|elenca i database|
|dropdb <nome db>|elimina un database. Comando da impartire in shell di sistema|
|\d+ <nome tabella>|Descrive la stuttura della tabella|
|\dp|Elenca i privilegi degli utenti sulle tabelle del database|
|\dp <tabella>|elenca i privilegi degli utenti sulla tabella|
|GRANT SELECT ON <tabella> TO <user>;| da il privilegio di fare select all'utente nella tabella|
|GRANT  ALL PRIVILEGES  ON <tabella> TO <utente>;|Da tutti i privilegi sulla tabella all'utente|
|GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <utente>;|da tutti i privilegi di scrittura e lettura all'utente sull'intero database |
|GRANT USAGE, SELECT ON SEQUENCE <sequenza> TO <utente>;|Da i permessi all'utente per poter utilizzare l'autoincremento |
|GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO <utente>;|Da i permessi all'utente di utilizzare tutti gli autoincrementi dello schema public|
|REVOKE SELECT ON <tabella> FROM <user>;|revoca il privilegio di fare select all'utente nella tabella|
|REVOKE ALL PRIVILEGES ON <tabella> FROM <utente>;|Revoca tutti i privilegi all'utente sulla tabella|
|REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM <utente>;|revoca tutti i privilegi di scrittura e lettura all'utente sull'intero database|


===== Alterare struttura tabelle =====
Di seguito alcuni comandi per gestire le tabelle. Lanciamo psql <nome database> per digitare i seguenti comandi. 

^Comando^Descrizione^
|ALTER TABLE <tabella> RENAME TO <nuovo nome>;|Rinomina una tabella|
|ALTER TABLE <tabella> RENAME COLUMN <colonna> TO <nuovo nome colonna>;|Rinomina colonna di una tabella|
|ALTER TABLE <tabella> ADD COLUMN <nuova colonna> varchar(30);|Aggiunge una nuova colonna alla tabella|
|ALTER TABLE <tabella> DROP COLUMN <colonna> RESTRICT;|Elimina una colonna dalla tabella|
|ALTER TABLE <tabella> ALTER COLUMN <colonna> TYPE varchar(100);|Cambia il tipo di dato di una colonna|

===== Abilitare il supporto a Openssl =====
Postgresql permette di cifrare le connessioni grazie a openssl (se abilitato in compilazione). Vediamo come attivarlo.

Prima di tutto dobbiamo creare la chiave privata e il certificato del server (privkey.pem  server.req):
<file>openssl req -new -text -out server.req </file>
Inseriamo la passphrase per la chiave privata e tutto quello che ci viene chiesto interattivamente.Possiamo ignorare 'challenge password' . \\ \\
Evitiamo che postgres chieda la passphrase ogni volta che si avvia:
<file>
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt
</file>
Adesso diamo i permessi a chiavi e certificati:
<file>chmod og-rwx server.key</file>

Copiare i file generati nella cartella data di postgres e assegnare il proprietario all'utente "postgres" :
<file>chown postgres server.crt server.key server.req</file>

Assicurarsi che la sezione "ssl" della configurazione del server risulti così:
<file>
ssl = on     # (change requires restart)
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
								# (change requires restart)
ssl_prefer_server_ciphers = on          # (change requires restart)
ssl_ecdh_curve = 'prime256v1'           # (change requires restart)
ssl_cert_file = '/var/lib/postgres/data/server.crt'             # (change requires restart)
ssl_key_file =  '/var/lib/postgres/data/server.key'             # (change requires restart)
#ssl_ca_file = ''                       # (change requires restart)
#ssl_crl_file = ''                      # (change requires restart)
password_encryption = on
db_user_namespace = off
row_security = on
</file>

Aggiungere infine la regola a '/var/lib/postgres/data/pg_hba.conf' per far connettere gli utenti (in questo caso tutti) tramite ssl:
<file>hostssl	all	all all trust </file> 

===== Backup/restore =====
Fare il backup di un intero database su un file di testo:
<file>pg_dump  miodb > outfile.sql</file>

Restore del databse:
<file>psql --set ON_ERROR_STOP=on miodb < infile.sql</file>

Se volessimo copiare direttamente un db su un'altro:
<file>pg_dump -h host1 dbname | psql -h host2 dbname</file>