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.

sudo pacman -S postgresql

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

sudo -u postgres -i

Sara' opportuno mettere una password.

La prima volta è necessario configurare il database con il locale e la cartella contenente i file dei nostri db.

initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'

 sudo systemctl start postgresql.service

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:

 createuser --interactive

Assegnare una password all'utente da shell (comando “psql” avviato da utente postgres)

ALTER USER mioutente WITH PASSWORD 'miapassword'

Creeremo un database di nome “test0” assegnando la proprieta' a un utente :

createdb test0 -U <nome utente che ha accesso al db> 

Da utente postgres impartiamo il seguente comando

psql <mio db>

I file sono essenzialmente 2 e si trovano su Arch in queste percorsi:

/var/lib/postgres/data/postgresql.conf  (Configurazione principale)
/var/lib/postgres/data/pg_hba.conf      (Permessi di accesso da locale e da rete)

Se si vuole rendere postgresql accessibile a chiunque da rete basta aggiungere questa riga a pg_hba.conf:

host   all             all               all            trust

e assicurarsi che sia presente in postgresql.con la direttiva:

listen_addresses = '*'

Una volta modificati i file si deve riavviare il demone con:

sudo systemctl restart postgresql.service

Creiamo una tabella con i campi id,nome,cognome,email con id chiave primaria autoincrementante.

CREATE TABLE persone(
 id  SERIAL PRIMARY KEY,
 nome VARCHAR (50) ,
 cognome VARCHAR (50) ,
 email VARCHAR (355)  
);

Inseriamo qualche dato:

insert into persone (nome, cognome, email) values ('Fabio','Di Matteo','fabio@mail.com') ;

Logghiamoci con l'utente di sistema postgres

sudo -u postgres -i

Lanciamo psql per digitare i seguenti comandi.

Di seguito alcuni comandi per gestire i database. Lanciamo psql <nome database> per digitare i seguenti comandi.

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

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):

openssl req -new -text -out server.req 

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:

openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt

Adesso diamo i permessi a chiavi e certificati:

chmod og-rwx server.key

Copiare i file generati nella cartella data di postgres e assegnare il proprietario all'utente “postgres” :

chown postgres server.crt server.key server.req

Assicurarsi che la sezione “ssl” della configurazione del server risulti così:

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

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

hostssl	all	all all trust 

Fare il backup di un intero database su un file di testo:

pg_dump  miodb > outfile.sql

Restore del databse:

psql --set ON_ERROR_STOP=on miodb < infile.sql

Se volessimo copiare direttamente un db su un'altro:

pg_dump -h host1 dbname | psql -h host2 dbname