Contraintes d'intégrité référentielle

 Considérons cet écran libreoffice:

Les données qui sont affichées proviennent de deux tables: la table 'employés' dont la colonne 'service' fait référence à la clef primaire 'service_id' d'une autre table, la table 'services'.

Le contenu de 'service' doit nécessairement être une valeur de la clef primaire 'service_id'. C'est l'intégrité référentielle. 'service' est une clef étrangère (foreign key).

Si les tables sont dans une base de données Postgresql, cette intégrité référentielle peut être garantie par une contrainte.

Par exemple si je tente de remplacer le service 06 de l'employés 1011 par le service 07 (qui n'existe pas) j'obtiens:

Mais on pourrait arriver au même résultat en se basant uniquement sur libreoffice. Ici le nom du service est affiché mais pas son code (06):

Et l'utilisation du menu déroulant me permet uniquement de remplacer le service du matricule 1011 par un  service existant:

Mais que se passe-t-il si je touche à la table 'services' ? Cette fois l'existence de la contrainte postgresql est essentielle pour assurer l'intégrité référentielle.

Si je tente de supprimer la rangée de clef 03 de la table 'services' je reçois ce message:

La suppression n'a pas lieu. Cependant si je regarde de plus près cette contrainte (employés_services_fkey) à l'aide de pgAdmin4, je constate que d'autres possibilité existent:

En cas de suppression (On delete) les actions le plus courantes sont:

SET NULL:

Pour tous les employés dont le code service est 03, ce code service est mis à NULL, si c'est permis.

NULL, ce n'est pas des espaces pour du texte, ni zéro pour un nombre.

CASCADE:

Tous les employés du service 03 sont supprimés

SET DEFAULT:

Si le code service d'un employé est 03, il est remplacé par sa valeur par défaut, si celle-ci existe et s'il lui correspond une rangée de la table 'services'.

Par exemple, cette valeur par défaut pourrait être 99, mais il faudrait aussi dans la table 'services' une rangée 99.

En cas d'update:

CASCADE

Le code service de tous les employés concernés est modifié

Postgesql Fedora: accès depuis le réseau

Je vais montrer comment accéder à une base de données postgresql depuis un poste client qui n'est pas sur la machine du serveur mais sur le même réseau. Tout d'abord dans le fichier postgresql.conf, dé-commenter et modifier la ligne listen_addresses:

listen_addresses = '*' # what IP address(es) to listen on;

Ensuite je m'occupe du fichier pg_hba.conf en ajoutant le lignes qui conviennent:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer map=map01
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident map=map01
# IPv4 lan connections
host    all             all             192.168.1.0/24          ident map=map02
# IPv6 local connections:
host    all             all             ::1/128                 ident map=map01
# IPv6  lan connections
host    all             all             fe80::/10               ident map=map02

(en rouge ce qui est ajouté)

Puis j'adapte pg_ident.conf qui pourrait maintenant ressembler à:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME

map01 postgres postgres
map01 mifel michel
map01 mifel titi
map02           michel michel
map02 michel titi
map02 viviane titi

Les fichiers concernés se trouvent dans /var/lib/pgsql/data. J'utilise sudo nano pour procéder.

En ce qui concerne le pare-feu, les commandes à lancer sont:

mifel@fedora:~$ sudo firewall-cmd --permanent --add-rich-rule 'rule family="ipv4" source address="192.168.1.0/24" service name="postgresql" accept'
mifel@fedora:~$ sudofirewall-cmd --permanent --add-rich-rule 'rule family="ipv6" source address="fe80::/10" service name="postgresql" accept'
mifel@fedora:~$ sudo firewall-cmd --reload

Le service postgresql ouvre le port 5432:

mifel@fedora:~$ firewall-cmd --permanent --service=postgresql --get-ports
5432/tcp

La commande hostnamectl m'informe que le nom 'hostname' n'est pas défini de manière statique: j'y remédie:

mifel@fedora:~$ sudo hostnamectl hostname fedora

Pour les connexions réseau je pouvais choisir dans pg_hba.conf une identification par mot de passe, mais j'ai préféré garder ident.
Donc je dois maintenant sur chaque poste client installer et activer oidentd (voir post précédent) 

Ensuite pour le pare-feu:

michel@arcturus:~$ sudo firewall-cmd --permanent --add-rich-rule 'rule family="ipv4" source address="192.168.1.0/24" service name="ident" accept'
michel@arcturus:~$ sudo firewall-cmd --permanent --add-rich-rule 'rule family="ipv6" source address="fe80::/10" service name="ident" accept'
michel@arcturus:~$ sudo firewall-cmd --reload

Le port 113 est maintenant ouvert:

michel@arcturus:~$ firewall-cmd --permanent --service=ident --get-ports
113/tcp

Et voilà:

Postgresql Fedora: démarrer

Postgresql est puissant gestionnaire de base de données, mais le démarrage de cet outil pose parfois certains problèmes. Je vais expliquer comment les résoudre.  

Il s'agit en premier de sélectionner le paquet qui convient. Dans Fedora le paquet 'postgresql' donnera seulement le client. Il faut donc installer le paquet 'postgresql-server', ce qui provoquera par entrainement l'installation du paquet 'postgresql'. Je procède en ligne de commande (dans un terminal):

mifel@fedora:~$ sudo dnf install postgresql-server

Puis

mifel@fedora:~$ sudo /usr/bin/postgresql-setup --initdb

mifel@fedora:~$ sudo systemctl start postgresql

mifel@fedora:~$ sudo systemctl enable postgresql

mifel@fedora:~$ 

Ensuite je passe à l'étape de la création d'un utilisateur postgresql.

Seul l'utilisateur postgres peut créer un nouvel utilisateur. Le problème est de devenir pour un temps postgres:

mifel@fedora:~$ sudo su postgres

[sudo] Mot de passe de mifel : 

bash-5.2$ whoami

postgres

bash-5.2$ createuser -d -r michel

bash-5.2$ exit

exit

mifel@fedora:~$ 

Le retour de la commande whoami montre bien que je suis devenu postgres. En tant que tel, je crée l'utilisateur michel, qui peut créer des bases de données et d'autres utilisateurs. Puis je redeviens mifel. 

Je tente ensuite de créer la base de données bd01:

mifel@fedora:~$ createdb bd01

createdb: erreur : la connexion au serveur sur le socket « /var/run/postgresql/.s.PGSQL.5432 » a échoué : FATAL:  le rôle « mifel » n'existe pas

mifel@fedora:~$ createdb bd01 -U michel

createdb: erreur : la connexion au serveur sur le socket « /var/run/postgresql/.s.PGSQL.5432 » a échoué : FATAL:  authentification peer échouée pour l'utilisateur « michel »

mifel@fedora:~$ 

Le plus simple aurait été bien sûr de créer mifel lorsque j'étais postgres. Mais comme mifel n'existe pas dans postgresql, il faut qu'il puisse se connecter en tant que michel. Je modifie en conséqence le fichier /var/lib/pgsql/data/pg_hba.conf (par exemple avec sudo nano /var/lib/pgsql/data/pg_hba.conf):

# "local" is for Unix domain socket connections only

local   all             all                                     peer    map=map01

# IPv4 local connections:

host    all             all             127.0.0.1/32            ident   map=map01

# IPv6 local connections:

host    all             all             ::1/128                 ident   map=map01

(en rouge, ce qui a été ajouté)

De même à la fin du fichier /var/lib/pgsql/data/pg_ident.conf, j'ajoute les lignes qui définissent map01:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME

map01           postgres                postgres

map01           mifel                   michel

map01           mifel                   titi

Pour finir je redémarre posgresql:

mifel@fedora:~$ sudo systemctl restart postgresql

[sudo] Mot de passe de mifel : 

mifel@fedora:~$ 

Je peux maintenant créer la base de données bd01 et l'utilisateur titi.

En images:

Utilisant psql, je me connecte à bd01 en tant que titi:

Toutes les connexions ont été jusqu'à présent effectuées via le socket unix. Mais dans le cas d'une connexion via la boucle locale, le processus d'authentification s'effectue avec ident comme indiqué dans le fichier pg_hba.conf.

Pour tester je force psql à passer par la boucle locale:

mifel@fedora:~$ psql -h localhost bd01 titi

psql: erreur : la connexion au serveur sur « localhost » (::1), port 5432 a échoué : FATAL:  Échec de l'authentification Ident pour l'utilisateur « titi »

mifel@fedora:~$ 

Ça ne fonctionne plus !

En fait il faut installer oidentd:

mifel@fedora:~$ sudo dnf install oidentd

Puis

mifel@fedora:~$ sudo systemctl start oidentd.socket

mifel@fedora:~$ sudo systemctl enable oidentd.socket

mifel@fedora:~$ 

Et maintenant ça fonctionne:

Dans un prochain article je traiterai des connexions depuis un poste client situé sur une autre machine.