5.7. Schémas

Pour créer un schéma, on utilise la commande CREATE SCHEMA(7). 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.12, « Gestion des dépendances » 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.7.6, « Utilisation » 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.

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 ( ... );

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 correspond 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 contrôle et exécuter des fonctions SQL arbitraire 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, « Fonctions d'informations système » 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.

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 d'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, « identificateurs et mots clés ».)

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èmes 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.

Dans les versions de PostgreSQL™ antérieures à la 7.3, les noms de table commençant par pg_ étaient réservés. Cela n'est plus vrai : une telle table peut être créée dans n'importe quel schéma qui n'est pas un schéma système. En revanche, il est préférable de continuer à é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èmes 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_.

Les schémas peuvent être utilisés de différentes façons pour organiser les données. Il existe quelques méthodes facilement supportées par la configuration par défaut, dont une seule est suffisant lorsque les utilisateurs de l'instance ne font pas confiance aux autres utilisateurs :

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. Rappelez-vous de donner 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 ils peuvent placer les schémas supplémentaires dans leur chemin de recherche, suivant leur préférence.

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.