Comprendre les schémas avec Postgresql
Avec Postgresql, la syntaxe complète pour identifier une table est la suivante :
select * from database.schema.table ;
Par défaut, quand seule la table est indiquée, Postgresql va rechercher la table dans la base de données courante, et le schéma est le schéma courant. Mais qu’est-ce qu’un schéma ?
Dans une première approche, un schéma peut être vu comme un espace de nommage, qui permet de regrouper ensemble des tables plutôt que d’avoir un espace unique les regroupant toutes. Le fait de placer des tables dans des schémas différents n’empêche en rien de les relier entre elles en créant des jointures ou des contraintes d’intégrité.
C’est déjà très bien : avec ce principe, il est possible de regrouper ensemble des tables qui sont fonctionnellement proches. Mais les schémas présentent également d’autres avantages. Il est ainsi possible d’indiquer des droits différents, et donc d’autoriser des opérations différentes selon les profils des utilisateurs.
Les schémas existent depuis probablement le début des bases de données relationnelles. Ils ont rarement été utilisés jusqu’ici, et certaines bases de données, comme MySql, ne disposent pas de cette finesse : les bases de données et les schémas sont fusionnés en un seul type d’objet, appelé schéma.
Quels usages pour les schémas ?
Regrouper les tables fonctionnellement
C’est souvent le premier usage des schémas. Une base de données est créée pour gérer des informations portant sur un sujet, qui peut comporter plusieurs facettes différentes. Par exemple, les données d’un programme de recherche sur un poisson peuvent concerner le suivi d’une station d’élevage, les captures accidentelles dans le milieu naturel, ou les lâchers de repeuplement. La possibilité de croiser les différentes sources d’informations justifie le fait de créer une base de données unique, mais la répartition des tables dans des schémas dédiés permet d’avoir une vision rapide des informations portant sur un aspect particulier (élevage, captures, lâchers dans notre exemple).
Avec cette séparation, il est alors possible de définir des droits de lecture ou d’écriture différents selon les utilisateurs, attribués selon les schémas.
Créer un schéma en écriture pour les travaux temporaires
Dans une base de données, il est rare que les utilisateurs disposent directement d’un droit d’écriture dans les tables, surtout si les informations sont alimentées par une application. Par défaut, ils ne peuvent accéder qu’en lecture aux tables, pour éviter les fausses manipulations. Toutefois, ils peuvent vouloir créer des extractions ou des vues adaptées à leurs besoins.
Bien sûr, ils ont toujours la possibilité de créer des objets temporaires, mais cela oblige à relancer les scripts de création à chaque interrogation. De plus, ils peuvent souhaiter disposer d’un jeu de données dont les valeurs ne risquent pas d’évoluer au fil du temps, par exemple pour servir de point de référence.
Pour réaliser cela, le plus simple est de créer un schéma particulier, par exemple travaux, dans lequel ils disposeront des droits de création d’objets. Ainsi, il leur suffira de créer des requêtes de type create travaux.table as select(...) ou create view (...) pour manipuler, à leur guise, les informations dans la base, sans risquer d’altérer les données initiales.
Masquer certaines tables aux utilisateurs
Lors de la mise en place d’une application web, si la plupart des tables contiennent des données liées au processus informatisé, certaines d’entre elles sont des tables techniques, qui servent à gérer les utilisateurs ou les droits attribués.
Ces informations n’ont pas d’intérêt pour les utilisateurs, et peuvent même contenir des données confidentielles, comme les dates/heure de connexion, le nombre de fiches modifiées, etc., si des traces sont enregistrées.
Il est toujours possible de stocker ces tables dans une base de données dédiée, mais cela veut dire que, pour un même projet, deux bases sont nécessaires, l’une pour les données, l’autre pour la gestion des droits.
L’autre solution consiste à stocker les droits dans un schéma dédié, accessible uniquement au login utilisé par l’application pour gérer les droits ; il est d’ailleurs possible de définir deux login dans le logiciel, l’un pour interroger le schéma des droits, l’autre pour l’accès aux tables applicatives.
Ainsi, les utilisateurs n’auront pas accès à ces tables, dont l’accès est strictement confidentiel.
Créer une application multi-domaines
Une équipe de chercheurs m’a demandé de leur écrire un logiciel pour gérer des informations portant sur un territoire bien défini. Les données font l’objet d’une déclaration CNIL, et seuls les chercheurs concernés doivent pouvoir accéder à ces données.
Le protocole d’obtention des données a été parfaitement déterminé, et l’équipe de recherche souhaite adopter la même méthode pour un autre secteur géographique, avec les contraintes suivantes :
- les données doivent être indépendantes les unes des autres ;
- les personnes susceptibles d’accéder aux données ne sont pas les mêmes selon le secteur géographique ;
- l’application doit être identique.
S’il aurait été possible de laisser l’application gérer les droits d’accès selon les secteurs géographiques, les interrogations SQL directes auraient été quasiment impossibles à réaliser, en garantissant une séparation fonctionnelle des secteurs.
Nous avons décidé de créer un schéma par secteur géographique, chacun ayant la même structure. Au niveau de l’application, il suffit d’indiquer lequel doit être utilisé.
Travailler avec les schémas
Le schéma public
Une base de données Postgresql est toujours créée avec un schéma, le schéma public. Si aucun autre n’est défini, toutes les tables de la base sont créées dans celui-ci.
Ce schéma est également utilisé par Postgresql pour stocker des données complémentaires. Ainsi, si le module Postgis, qui permet de manipuler des informations géographiques, est déclaré dans la base de données, environ un millier de fonctions différentes sont créées dans le schéma public.
Cela ne pose guère de problème tant qu’un seul schéma est défini. Par contre, dès lors que plusieurs schémas sont créés, et que les droits d’accès sont différenciés selon ceux-ci, l’utilisation de public comme espace de création de tables devient périlleuse. Si les tables créées dans public doivent faire l’objet de restrictions d’accès, par exemple en interdisant l’accès complet du schéma à certains utilisateurs, ceux-ci ne pourront plus utiliser les fonction Postgis leur permettant de manipuler les objets géographiques : ils n’auront pas accès aux fonctions déclarées dans celui-ci.
Dans ce cas, il vaut mieux :
- ne pas stocker de tables contenant des données à accès limité dans le schéma public ;
- donner un accès à tous les utilisateurs de la base de données à ce schéma, tout au moins en lecture/exécution ;
- stocker les informations dans des schémas dédiés.
Définir le schéma par défaut
Nous l’avons vu au début de cet article : toute commande SQL implémente par défaut le schéma public, si celui-ci n’est pas précisé.
Ce n’est pas toujours pratique, et parfois inadapté, comme pour le cas d’utilisation où la base de données stocke des informations issues de domaines séparés : l’application doit pouvoir accéder à chaque table avec le même code, sans qu’il soit nécessaire de préfixer chaque table par le schéma correspondant au domaine.
Pour cela, Postgresql dispose d’une commande qui permet de modifier le schéma par défaut :
set search_path = nom_du_schema;
Le schéma par défaut, public, va alors être remplacé par celui indiqué dans la commande.
Il est également possible d’indiquer plusieurs schémas dans la commande :
set search_path = nom_du_schema, public ;
Les commandes SQL vont alors rechercher les tables (ou les autres objets) à la fois dans le schéma nom_du_schema et dans public, ce qui est indispensable pour travailler de façon pratique avec les commandes Postgis : celles-ci sont décrites sous forme de fonctions dans public.
Créer un schéma
La commande est classique :
create schema nom_du_schema;
Attribuer les droits
C’est la partie la plus complexe. Postgresql n’est pas réputé pour la simplicité de la gestion des droits...
Pour attribuer les droits en lecture :
grant usage on schema nom_du_schema to group base_r;
grant select on all tables in schema nom_du_schema to group base_r;
grant execute on all functions in schema nom_du_schema to group base_r;
Pour attribuer les droits en écriture :
grant usage on schema nom_du_schema to group base_rw;
grant select, insert, update, delete on all tables in schema public to group base_rw; grant all on all functions in schema nom_du_schema to group base_rw;
grant all on all sequences in schema nom_du_schema to group base_rw;
Pour définir les droits par défaut :
alter default privileges in schema nom_du_schema grant select on tables to group base_r;
alter default privileges in schema nom_du_schema grant execute on functions to group base_r;
Modifier le schéma d’un objet
Voici comment basculer rapidement une table d’un schéma à un autre :
alter table schema_old.nom_table set schema schema_new;
Les indexes et les séquences seront également déplacés.
Pensez à redéfinir les droits d’accès à la table : il est possible qu’ils ne soient pas correctement positionnés après cette opération.