−Indice

Veloce introduzione a PostgreSQL

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

sudo pacman -S postgresql

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

Loggarsi come utente di sistema postgres

sudo -u postgres -i

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.

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

Avviare Postgresql

 sudo systemctl start postgresql.service

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:

 createuser --interactive

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

ALTER USER mioutente WITH PASSWORD 'miapassword'

Creare un database

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

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

Accedere al db

Da utente postgres impartiamo il seguente comando

psql <mio db>

Dove si trovano i file di configurazione di Postgresql?

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

Creare tabelle e popolare di dati

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') ;

Utenti

Logghiamoci con l'utente di sistema postgres

sudo -u postgres -i

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

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

Backup/restore

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