PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Définition des données » Schémas

5.9. Schémas

Une instance de bases de données PostgreSQL contient une ou plusieurs base(s) nommée(s). Les rôles et quelques autres types d'objets sont partagés sur l'ensemble de l'instance. Une connexion cliente au serveur ne peut accéder qu'aux données d'une seule base, celle indiquée dans la requête de connexion.

Note

Les rôles d'une instance n'ont pas obligatoirement le droit d'accéder à toutes les bases de l'instance. Le partage des noms de rôles signifie qu'il ne peut pas y avoir plusieurs rôles nommés joe, par exemple, dans deux bases de la même instance ; mais le système peut être configuré pour n'autoriser joe à accéder qu'à certaines bases.

Une base de données contient un (ou plusieurs) schéma(s) nommé(s) qui, eux, contiennent des tables. Les schémas contiennent aussi d'autres types d'objets nommés (types de données, fonctions et opérateurs, par exemple). Le même nom d'objet peut être utilisé dans différents schémas sans conflit ; par exemple, schema1 et mon_schema peuvent tous les deux contenir une table nommée ma_table. À la différence des bases de données, les schémas ne sont pas séparés de manière rigide : un utilisateur peut accéder aux objets de n'importe quel schéma de la base de données à laquelle il est connecté, sous réserve qu'il en ait le droit.

Il existe plusieurs raisons d'utiliser les schémas :

  • autoriser de nombreux utilisateurs à utiliser une base de données sans interférer avec les autres ;

  • organiser les objets de la base de données en groupes logiques afin de faciliter leur gestion ;

  • les applications tierces peuvent être placées dans des schémas séparés pour éviter les collisions avec les noms d'autres objets.

Les schémas sont comparables aux répertoires du système d'exploitation, à ceci près qu'ils ne peuvent pas être imbriqués.

5.9.1. Créer un schéma

Pour créer un schéma, on utilise la commande CREATE SCHEMA. Le nom du schéma est libre. Par exemple :

CREATE SCHEMA mon_schema;

Pour créer les objets d'un schéma ou y accéder, on écrit un nom qualifié constitué du nom du schéma et du nom de la table séparés par un point :

schema.table

Cela fonctionne partout où un nom de table est attendu, ce qui inclut les commandes de modification de la table et les commandes d'accès aux données discutées dans les chapitres suivants. (Pour des raisons de simplification, seules les tables sont évoquées, mais les mêmes principes s'appliquent aux autres objets nommés, comme les types et les fonctions.)

La syntaxe encore plus générale

base.schema.table

peut aussi être utilisée, mais à l'heure actuelle, cette syntaxe n'existe que pour des raisons de conformité avec le standard SQL. Si un nom de base de données est précisé, ce doit être celui de la base à laquelle l'utilisateur est connecté.

Pour créer une table dans le nouveau schéma, on utilise :

CREATE TABLE mon_schema.ma_table (
 ...
);

Pour effacer un schéma vide (tous les objets qu'il contient ont été supprimés), on utilise :

DROP SCHEMA mon_schema;

Pour effacer un schéma et les objets qu'il contient, on utilise :

DROP SCHEMA mon_schema CASCADE;

La Section 5.14 décrit le mécanisme général sous-jacent.

Il n'est pas rare de vouloir créer un schéma dont un autre utilisateur est propriétaire (puisque c'est l'une des méthodes de restriction de l'activité des utilisateurs à des namespaces prédéfinis). La syntaxe en est :

CREATE SCHEMA nom_schema AUTHORIZATION nom_utilisateur;

Le nom du schéma peut être omis, auquel cas le nom de l'utilisateur est utilisé. Voir la Section 5.9.6 pour en connaître l'utilité.

Les noms de schéma commençant par pg_ sont réservés pour les besoins du système et ne peuvent être créés par les utilisateurs.

5.9.2. Le schéma public

Dans les sections précédentes, les tables sont créées sans qu'un nom de schéma soit indiqué. Par défaut, ces tables (et les autres objets) sont automatiquement placées dans un schéma nommé « public ». Toute nouvelle base de données contient un tel schéma. Les instructions suivantes sont donc équivalentes :

CREATE TABLE produits ( ... );

et :

CREATE TABLE public.produits ( ... );

5.9.3. Chemin de parcours des schémas

Non seulement l'écriture de noms qualifiés est contraignante, mais il est, de toute façon, préférable de ne pas fixer un nom de schéma dans les applications. De ce fait, les tables sont souvent appelées par des noms non qualifiés, soit le seul nom de la table. Le système détermine la table appelée en suivant un chemin de recherche, liste de schémas dans lesquels chercher. La première table correspondante est considérée comme la table voulue. S'il n'y a pas de correspondance, une erreur est remontée, quand bien même il existerait des tables dont le nom corresponde dans d'autres schémas de la base.

La possibilité de créer des objets de même nom dans différents schémas complique l'écriture d'une requête qui référence précisément les mêmes objets à chaque fois. Cela ouvre aussi la possibilité aux utilisateurs de modifier le comportement des requêtes des autres utilisations, par accident ou volontairement. À cause de la prévalence des noms non qualifiés dans les requêtes et de leur utilisation des internes de PostgreSQL, ajouter un schéma à search_path demande en effet à tous les utilisateurs d'avoir le droit CREATE sur ce schéma. Quand vous exécutez une requête ordinaire, un utilisateur mal intentionné capable de créer des objets dans un schéma de votre chemin de recherche peut prendre le contrôle et exécuter des fonctions SQL arbitraires comme si vous les exécutiez.

Le premier schéma du chemin de recherche est appelé schéma courant. En plus d'être le premier schéma parcouru, il est aussi le schéma dans lequel les nouvelles tables sont créées si la commande CREATE TABLE ne précise pas de nom de schéma.

Le chemin de recherche courant est affiché à l'aide de la commande :

SHOW search_path;

Dans la configuration par défaut, ceci renvoie :

 search_path
--------------
 "$user", public

Le premier élément précise qu'un schéma de même nom que l'utilisateur courant est recherché. En l'absence d'un tel schéma, l'entrée est ignorée. Le deuxième élément renvoie au schéma public précédemment évoqué.

C'est, par défaut, dans le premier schéma du chemin de recherche qui existe que sont créés les nouveaux objets. C'est la raison pour laquelle les objets sont créés, par défaut, dans le schéma public. Lorsqu'il est fait référence à un objet, dans tout autre contexte, sans qualification par un schéma (modification de table, modification de données ou requêtes), le chemin de recherche est traversé jusqu'à ce qu'un objet correspondant soit trouvé. C'est pourquoi, dans la configuration par défaut, tout accès non qualifié ne peut que se référer au schéma public.

Pour ajouter un schéma au chemin, on écrit :

SET search_path TO mon_schema,public;

($user est omis à ce niveau, car il n'est pas immédiatement nécessaire.) Il est alors possible d'accéder à la table sans qu'elle soit qualifiée par un schéma :

DROP TABLE ma_table;

Puisque mon_schema est le premier élément du chemin, les nouveaux objets sont, par défaut, créés dans ce schéma.

On peut aussi écrire :

SET search_path TO mon_schema;

Dans ce cas, le schéma public n'est plus accessible sans qualification explicite. Hormis le fait qu'il existe par défaut, le schéma public n'a rien de spécial. Il peut même être effacé.

On peut également se référer à la Section 9.25 qui détaille les autres façons de manipuler le chemin de recherche des schémas.

Le chemin de recherche fonctionne de la même façon pour les noms de type de données, les noms de fonction et les noms d'opérateur que pour les noms de table. Les noms des types de données et des fonctions peuvent être qualifiés de la même façon que les noms de table. S'il est nécessaire d'écrire un nom d'opérateur qualifié dans une expression, il y a une condition spéciale. Il faut écrire :

OPERATOR(schéma.opérateur)

Cela afin d'éviter toute ambiguïté syntaxique. Par exemple :

SELECT 3 OPERATOR(pg_catalog.+) 4;

En pratique, il est préférable de s'en remettre au chemin de recherche pour les opérateurs, afin de ne pas avoir à écrire quelque chose d'aussi étrange.

5.9.4. Schémas et privilèges

Par défaut, les utilisateurs ne peuvent pas accéder aux objets présents dans les schémas qui ne leur appartiennent pas. Pour le permettre, le propriétaire du schéma doit donner le droit USAGE sur le schéma. Pour autoriser les utilisateurs à manipuler les objets d'un schéma, des privilèges supplémentaires doivent éventuellement être accordés, en fonction de l'objet.

Un utilisateur peut aussi être autorisé à créer des objets dans le schéma d'un autre. Pour cela, le privilège CREATE sur le schéma doit être accordé. Par défaut, tout le monde bénéficie des droits CREATE et USAGE sur le schéma public. Cela permet à tous les utilisateurs qui peuvent se connecter à une base de données de créer des objets dans son schéma public. Certaines méthodes d'usage demandent à révoquer ce droit :

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Le premier « public » est le schéma, le second « public » signifie « tout utilisateur ». Dans le premier cas, c'est un identifiant, dans le second, un mot-clé, d'où la casse différente. (Se reporter aux règles de la Section 4.1.1.)

5.9.5. Le schéma du catalogue système

En plus du schéma public et de ceux créés par les utilisateurs, chaque base de données contient un schéma pg_catalog. Celui-ci contient les tables système et tous les types de données, fonctions et opérateurs intégrés. pg_catalog est toujours dans le chemin de recherche. S'il n'est pas nommé explicitement dans le chemin, il est parcouru implicitement avant le parcours des schémas du chemin. Cela garantit que les noms internes sont toujours accessibles. En revanche, pg_catalog peut être explicitement placé à la fin si les noms utilisateur doivent surcharger les noms internes.

Comme les noms des catalogues système commencent par pg_, il est préférable d'éviter d'utiliser de tels noms pour se prémunir d'éventuels conflits si une version ultérieure devait définir une table système qui porte le même nom que la table créée. (Le chemin de recherche par défaut implique qu'une référence non qualifiée à cette table pointe sur la table système). Les tables système continueront de suivre la convention qui leur impose des noms préfixés par pg_. Il n'y a donc pas de conflit possible avec des noms de table utilisateur non qualifiés, sous réserve que les utilisateurs évitent le préfixe pg_.

5.9.6. Utilisation

Les schémas peuvent être utilisés de différentes façons pour organiser les données. Un modèle d'utilisation de schéma sécurisé empêche tous les utilisateurs pour lesquels nous n'avons pas confiance de modifier le comportement des requêtes des autres utilisateurs. Quand une base de données n'utilise pas de modèle d'utilisation de schéma sécurisé, les utilisateurs souhaitant interroger cette base de données en toute sécurité devront prendre des mesures de protection au début de chaque session. Plus précisément, ils commenceraient chaque session par la configuration du paramètre search_path en une chaîne vide ou en supprimant de leur search_path les schémas accessibles en écriture par des utilisateurs standards. Il existe quelques modèles d'utilisation facilement pris en charge par la configuration par défaut :

  • Contraindre les utilisateurs ordinaires aux schémas privés des utilisateurs. Pour cela, exécutez REVOKE CREATE ON SCHEMA public FROM PUBLIC, et créez un schéma pour chaque utilisateur, du nom de cet utilisateur. Rappelez-vous que le chemin de recherche par défaut commence avec $user, qui se résout avec le nom de l'utilisateur. Par conséquent, si chaque utilisateur a un schéma distinct, ils accèdent à leur propres schémas par défaut. Après avoir adopté ce modèle dans une base de données où des utilisateurs non fiables se sont déjà connectés, pensez à vérifier dans le schéma public l'existence d'objets nommés comme des objets du schéma pg_catalog. Ce modèle est sécurisé sauf si un utilisateur non fiable est le propriétaire de la base de données ou détient l'attribut CREATEROLE, auquel cas aucun modèle d'utilisation de schéma sécurisé n'existe.

  • Supprimer le schéma public du chemin de recherche par défaut, en modifiant le fichier postgresql.conf ou en exécutant ALTER ROLE ALL SET search_path = "$user". Tout le monde conserve la possibilité de créer des objets dans le schéma public, mais seuls les noms qualifiés choisiront ces objets. Bien que les références de table qualifiées soient correctes, les appels aux fonctions dans le le schéma public seront dangereux ou peu fiables. Si vous créez des fonctions ou des extensions dans le schéma public, utilisez le premier modèle à la place. Sinon, tout comme le premier modèle, c'est sécurisé sauf si un utilisateur non fiable est le propriétaire de la base de données ou détient l'attribut CREATEROLE.

  • Conserver la valeur par défaut. Tous les utilisateurs ont accès au schéma public implicitement. Ceci simule la situation où les schémas ne sont pas du tout disponibles, réalisant ainsi une transition en douceur vers un monde qui ne connait pas les schémas. Néanmoins, ceci ne sera jamais un modèle sécurisé. C'est uniquement acceptable si la base de données ne contient qu'un seul utilisateur ou quelques utilisateurs qui se font mutuellement confiance.

Pour chaque méthode, pour installer des applications partagées (tables utilisées par tout le monde, fonctions supplémentaires fournies par des tiers, etc.), placez-les dans des schémas séparés. N'oubliez pas d'accorder les droits appropriés pour permettre aux autres utilisateurs d'y accéder. Les utilisateurs peuvent ensuite faire référence à ces objets supplémentaires en les qualifiant avec le nom du schéma, ou bien ils peuvent placer les schémas supplémentaires dans leur chemin de recherche, suivant leur préférence.

5.9.7. Portabilité

Dans le standard SQL, la notion d'objets d'un même schéma appartenant à des utilisateurs différents n'existe pas. De plus, certaines implantations ne permettent pas de créer des schémas de nom différent de celui de leur propriétaire. En fait, les concepts de schéma et d'utilisateur sont presque équivalents dans un système de base de données qui n'implante que le support basique des schémas tel que spécifié dans le standard. De ce fait, beaucoup d'utilisateurs considèrent les noms qualifiés comme correspondant en réalité à utilisateur.table. C'est comme cela que PostgreSQL se comporte si un schéma utilisateur est créé pour chaque utilisateur.

Le concept de schéma public n'existe pas non plus dans le standard SQL. Pour plus de conformité au standard, le schéma public ne devrait pas être utilisé.

Certains systèmes de bases de données n'implantent pas du tout les schémas, ou fournissent le support de namespace en autorisant (peut-être de façon limitée) l'accès inter-bases de données. Dans ce cas, la portabilité maximale est obtenue en n'utilisant pas les schémas.