Visualizza wikitesto di Installazione Server PostgreSQL

(<FONT Size=2>guida a cura del membro ''[http://www.gambas-it.org/smf/index.php?action=profile;u=161 sotema]'' del forum gambas-it.org</font>)


=Installazione pacchetti=
Per eseguire un Server PostgreSQL su Ubuntu e derivate dovremo installare i pacchetti ''postgresql'' e ''postgresql-contrib''. Per la progettazione di database è possibile installare il programma PgAdmin3.
<BR>Sebbene non sia indispensabile postgresql-contrib risulta molto comodo poiché comprende admin-pack, una serie di utilità sfruttate da PgAdminIII. 

Procediamo quindi con l'installazione; da terminale digitiamo il comando:
 emanuele@Emy-Pav:~$ sudo apt-get install postgresql postgresql-contrib pgadmin3
al termine del processo avremo un Server PostgreSQL attivo sulla macchina locale.



=Configurazione del Server=

==I file di configurazione==
I file di configurazione di PostgreSQL sono contenuti nel percorso /etc/postgresql/x.y/main, dove x.y rappresentano la versione del server in esecuzione; pertanto se abbiamo installato la versione 9.4  il percorso sarà '''/etc/postgresql/9.4/main'''.
 emanuele@Emy-Pav:/etc/postgresql/9.4/main$ ls -C1
 environment
 pg_ctl.conf
 pg_hba.conf
 pg_hba.conf.ok
 pg_ident.conf
 postgresql.conf
 start.conf
di questi file ci interessano, al fine della presente guida, i due seguenti:
<UL Type=disc><LI>'''postgresql.conf''': contiene le opzioni globali di configurazione del server, in particolare
<UL Type=circle><LI>''listen_address'': indica le interfacce sulle quali il server accetta richieste di connessione; si possono indicare indirizzi ip separati da virgola; la chiave ''localhost'', ''valore predefinito'', indica la macchina su cui è in esecuzione postgresql, il carattere '*' indica tutte le interfacce attive <SUP>[[#Note|1]]</sup></li>
<LI>''port'': indica la porta tcp su cui verranno soddisfatte le richieste di connessione, il valore predefinito è 5432</li></ul></li></ul>
<UL Type=disc><LI>'''pg_hba.conf''': consente di configurare i metodi di accesso ed autenticazione al server.</li></ul>



=Connessione ed autenticazione=

==Connessione in locale==
Il processo di connessione a PostgreSQL può avvenire in modi differenti, nel corso della presente guida utilizzeremo i metodi <SUP>[[#Note|2]]</sup>:
<UL><LI>''local'': connessione dalla macchina locale; l'utente che tenta di accedere al server è un utente locale della macchina</li></ul>
<UL><LI>''host'': il tentativo di connessione viene eseguito dall'utente di una macchina collegata in rete.</li></ul>
Anche l'autenticazione dell'utente viene effettuata con differenti metodologie, qui vedremo i metodi:
<UL><LI>''trust'': considera l'utente sicuro, non necessita password.</li></ul>
<UL><LI>''md5'': l'autenticazione viene effettuata tramite una password cifrata.</li></ul>
Durante la fase di installazione viene configurato l'utente '''''postgres''''' <SUP>[[#Note|3]]</sup>, che può effettuare qualsiasi attività sul server. Tentiamo quindi una connessione al server specificando il nome utente (-U) ed il database (-d) al quale accedere.
 emanuele@Emy-Pav:~$ psql -U postgres -d template1
 psql: FATAL:  Peer authentication failed for user "postgres"
il messaggio di risposta del server ci dice che l'autenticazione tramite metodo ''Peer'' è fallita. Vediamo quindi come garantire l'accesso al server per l'utente '''''postgres'''''.

Apriamo con un editor il file pg_hba.conf
 emanuele@Emy-Pav:/etc/postgresql/9.4/main$ sudo -u postgres vi pg_hba.conf
 [sudo] password for emanuele:
e scendiamo fino alla sezione:
 #
 # Database administrative login by Unix domain socket
 local   all             postgres                                peer
ed impostiamo il metodo di autenticazione trust
 #
 # Database administrative login by Unix domain socket
 local   all             postgres                                trust
quindi ricarichiamo la configurazione con:
 emanuele@Emy-Pav:/etc/postgresql/9.4/main$ sudo service postgresql reload
e proviamo nuovamente la connessione.
 emanuele@Emy-Pav:/etc/postgresql/9.4/main$ psql -U postgres -d template1
 psql (9.4.5)
 Type "help" for help.
 
 template1=#
questa volta il server ha acconsentito alla connessione, in risposta otteniamo la versione del client in uso. Notare il simbolo '#' al termine del prompt, significa che siamo collegati come SUPERUSER.

Dobbiamo ora impostare una password per l'utente '''''postgres''''' per impedire che chiunque possa amministrare il nostro server; impartiamo il comando
 template1=# ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'miapassword';
 ALTER ROLE
 template1=#\q
abbiamo usato il qualificatore ROLE e non USER; la differenza tra i due sta nel fatto che ROLE può indicare un utente o un gruppo <SUP>[[#Note|4]]</sup> mentre USER indica un utente. Ora che '''''postgres''''' dispone di una password a noi nota dobbiamo istruire PostgreSQL affinché ci obblighi a fornirla ad ogni nuova connessione.

Riapriamo il file ''pg_hba.conf'' e alla riga precedentemente modificata impostiamo il metodo ''md5'':
 #
 # Database administrative login by Unix domain socket
 local   all             postgres                                md5
salviamo e ricarichiamo la configurazione. Proviamo ora una connessione
 emanuele@Emy-Pav:/etc/postgresql/9.4/main$ psql -U postgres -d template1
 Password for user postgres: 
 psql (9.4.5)
 Type "help" for help.
 
 template1=# 



==Connessione da rete==
Quanto visto sinora vale per la macchina su cui è in esecuzione PostgreSQL, ma se volessimo collegarci da un sistema in rete? La soluzione sta nel file ''postgresql.conf'' che andremo ad editare.
 emanuele@Emy-Pav:/etc/postgresql/9.4/main$ sudo -u postgres vi postgresql.conf
Come detto in precedenza questo file contiene le opzioni globali di configurazione del server, scendiamo fino alla sezione
 #------------------------------------------------------------------------------
 # CONNECTIONS AND AUTHENTICATION
 #------------------------------------------------------------------------------
dove andremo a modificare l'opzione '''listen_address='localhost'''' in  '''listen_address='*''''
 #------------------------------------------------------------------------------
 # CONNECTIONS AND AUTHENTICATION
 #------------------------------------------------------------------------------
 
 # - Connection Settings -
 
 listen_addresses = '*'               # what IP address(es) to listen on;
                                      # comma-separated list of addresses;
                                      # defaults to 'localhost'; use '*' for all
                                      # (change requires restart)
come indicato dal commento nel file l'opzione listen_address può contenere una lista di indirizzi IP separati da virgola, avendo due interfacce di rete attive sul sistema server potremmo decidere di porle entrambe in ascolto. Salviamo la modifica e riavviamo il server. Ora che il nostro server è in ascolta sulla rete locale dovremo impostare il metodo di autenticazione anche per gli utenti di rete.
Editiamo nuovamente il file ''pg_hba.conf'' e immediatamente dopo la riga:
 # IPv4 local connections:
 host    all             all             127.0.0.1/32            md5
inseriamo, supposto che la nostra rete abbia indirizzo 12.24.35.0
 host    all             all             12.24.35.0/24           md5
avremo come risultato:
 # IPv4 local connections:
 host    all             all	          127.0.0.1/32           md5
 host	 all             all              12.24.35.0/24          md5
quindi ricarichiamo la configurazione con
 emanuele@Emy-Pav:/etc/postgresql/9.4/main$ sudo service postgresql reload



=Configurazione utenti=
PostgreSQL utilizza il concetto di ''role'' per gestire gli accessi al database, laddove con tale qualificatore si possono identificare sia singoli utenti che gruppi di utenti. Vedremo ora come creare un utente che ci permetta di amministrare il server evitando l'uso del '''superuser''' .


==Creazione utente==
Stabiliamo la connessione
 emanuele@Emy-Pav:/etc/postgresql/9.4/main$ psql -U postgres -d template1
 Password for user postgres:
 psql (9.4.5)
 Type "help" for help.
 
 template1=#
configuriamo l'utente ''test'' che possa:
<UL><LI>creare ed eliminare database</li></ul>
<UL><LI>creare ed eliminare nuovi utenti</li></ul>
 CREATE ROLE test WITH CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'testone';
 CREATE ROLE
 template1=#
analizzando il comando vediamo:
<OL type="1"><LI>CREATE ROLE informa PostgreSQL che intendiamo creare un nuovo utente</li>
<LI>test è l'identificativo dell'utente, sarà usato per l'autenticazione</li>
<LI>WITH dichiara che seguono i qualificatori utente</li>
<LI>CREATEDB l'utente potrà creare nuovi DB (cluster)</li>
<LI>CREATEROLE il nuovo utente potrà configurare nuovi utenti</li>
<LI>LOGIN l'utente può effettuare l'accesso al server</li>
<LI>ENCRYPTED PASSWORD l'autenticazione sarà eseguita tramite password cifrata (md5)</li></ol>
PostgreSQL ci risponde con CREATE ROLE che conferma l'avvenuta creazione dell'utente ''test'' dal client psql possiamo verificare le carateristiche dell'utente test con il comando:
  template1=# \du
                              List of roles
  Role name |                   Attributes                   | Member of 
 -----------+------------------------------------------------+-----------
  postgres  | Superuser, Create role, Create DB, Replication | {}
  test      | Create role, Create DB                         | {}
il client mette a disposizioni svariati comandi attraverso i quali gestire il server, vedremo in seguito alcuni di essi.


==Connessione via rete==
Ora che il nostro utente è creato e abbiamo configurato l'autenticazione via rete tramite password, proviamo a connetterci da un altro sistema con il comando:
 psql -U test -h indirizzo.del.server.pgsql -d template1
Notare che il nome del database cui collegarsi  è obbligatorio solo se non esiste un database con lo stesso nome dell'utente. Se esistesse un DB test potremmo scrivere:
 psql -U test -h indirizzo.del.server.pgsql
PostgreSQL ci collegherà automaticamente al DB test.



=Gestione Database=
Abbiamo configurato l'utente test in grado di creare nuovi database, vediamo quindi il comando atto a questo scopo:
 emanuele@Emy-Pav:~$ psql -U test -d template1
 Password for user test: 
 psql (9.4.5)
 Type "help" for help.
 
 template1=> CREATE DATABASE rubinettofelice WITH OWNER=test TEMPLATE= template1 ENCODING='utf-8';
 CREATE DATABASE
 template1=>
vediamo in sintesi il comando:
<OL Type="1"><LI>CREATE DATABASE informa PostgreSQL che intendiamo creare un nuovo DB</li>
rubinettofelice il nome del DB
<LI>WITH seguono le proprietà del DB</li>
<LI>OWNER è il proprietario del DB, gode di tutti i privilegi sul DB e gli oggetti in esso contenuti (tabelle, sequenze, relazioni…)</li>
<LI>TEMPLATE è il database da cui clonare il nuovo database</li>
<LI>ENCODING è la codifica caratteri utilizzata per registrare i dati nel DB</li></ol>
Colleghiamoci ora al nuovo database
 template1=> \c rubinettofelice
 You are now connected to database "rubinettofelice" as user "test".
 rubinettofelice=>
ora che siamo collegati al nostro db possiamo popolarlo con tutte le strutture necessarie.



=PgAdmin3=
All'inizio di questa guida, laddove abbiamo installato il server, abbiamo installato anche il programma PgAdmin3, uno strumento ad interfaccia grafica che agevola molto lo svolgimento delle attività di gestione, amministrazione e sviluppo di database PgSQL. Per il suo utilizzo si rimanda alla documentazione ufficiale sul sito di [https://www.pgadmin.org/docs/pgadmin3/1.22.2/index.html Pgadmin Manuale PgAdmin3].



=Alcuni comandi psql=
Dal prompt psql possiamo impartire alcuni comandi per interrogare il server e verificarne lo stato.

Seguono alcuni esempi ed il loro risultato.

'''\l''' visualizza l'elenco dei database attivi sul server
 postgres=# \l
                                     List of databases
       Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
 -----------------+----------+----------+-------------+-------------+-----------------------
  postgres        | postgres | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 | 
  rubinettofelice | test     | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 | 
  template0       | postgres | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 | =c/postgres          +
                  |          |          |             |             | postgres=CTc/postgres
  template1       | postgres | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 | =c/postgres          +
                  |          |          |             |             | postgres=CTc/postgres
 (4 rows)
 
 postgres=#
'''\du''' visualizza l'elenco degli utenti configurati
 postgres=# \du
                              List of roles
  Role name |                   Attributes                   | Member of 
 -----------+------------------------------------------------+-----------
  postgres  | Superuser, Create role, Create DB, Replication | {}
  test      | Create role, Create DB                         | {}
 
 postgres=# 
'''\c''' collega un differente DB
 postgres=# \c rubinettofelice
 You are now connected to database "rubinettofelice" as user "postgres".
 rubinettofelice=#
'''\d''' mostra tutte le tabelle, le viste e le sequenze del DB
 rubinettofelice=> \d
                    List of relations
  Schema |            Name            |   Type   | Owner 
 --------+----------------------------+----------+-------
  public | cliente                    | table    | test
  public | cliente_cliente_id_seq     | sequence | test
  public | fornitore                  | table    | test
  public | fornitore_fornitore_id_seq | sequence | test
  public | prodotto                   | table    | test
  public | prodotto_prodotto_id_seq   | sequence | test
 (6 rows)
 
 rubinettofelice=> 
'''\dn''' elenca gli schemi
 rubinettofelice=> \dn
    List of schemas
    Name    |  Owner   
 -----------+----------
  manicotto | test
  public    | postgres
 (2 rows)
 
 rubinettofelice=>
'''\d+''' ''nome'' mostra la descrizione dettagliata dell'oggetto ''nome''
 rubinettofelice=> \d+ cliente_cliente_id_seq 
           Sequence "public.cliente_cliente_id_seq"
     Column     |  Type   |         Value          | Storage 
 ---------------+---------+------------------------+---------
  sequence_name | name    | cliente_cliente_id_seq | plain
  last_value    | bigint  | 1                      | plain
  start_value   | bigint  | 1                      | plain
  increment_by  | bigint  | 1                      | plain
  max_value     | bigint  | 9223372036854775807    | plain
  min_value     | bigint  | 1                      | plain
  cache_value   | bigint  | 1                      | plain
  log_cnt       | bigint  | 0                      | plain
  is_cycled     | boolean | f                      | plain
  is_called     | boolean | f                      | plain
 Owned by: public.cliente.cliente_id
 
 rubinettofelice=> 
'''\h''' mostra l'aiuto sui comandi sql
'''\h''' istruzionesql mostra l'aiuto di una specifica istruzione
 rubinettofelice=> \h create table
 Command:     CREATE TABLE
 Description: define a new table
 Syntax:
 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
   { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
     | table_constraint
     | LIKE source_table [ like_option ... ] }
     [, ... ]
 ] )
 [ INHERITS ( parent_table [, ... ] ) ]
 [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
 [ TABLESPACE tablespace_name ]

 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
     OF type_name [ (
   { column_name WITH OPTIONS [ column_constraint [ ... ] ]
     | table_constraint }
     [, ... ]
 ) ]
 [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
 [ TABLESPACE tablespace_name ]
 :
'''\i''' ''nomefile'' esegue i comandi sql presenti nel file ''nomefile''
'''\o''' ''nomefile'' salva il risultato di una query nel file ''nomefile''
per un elenco completo dei comandi disponibili digitare '''\?'''



[https://www.postgresql.org/docs/ Documentazione]
----
=Note=
[1] Le interfacce wireless controllate da network manager non vengono configurate in quanto il processo ''postmaster'' che controlla il server viene avviato prima che siano attivate.

[2] Vedere la documentazione ufficiale per una descrizione dei vari metodi.

[3] Data la natura di SUPERUSER di postgres se ne sconsiglia l'uso per attività di sviluppo.

[4] Una identità gruppo non può effettuare la connessione al server.