PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 16.2 » 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 schémas 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, autrement dit 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 schémas natifs 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.26 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 droits #

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. Par défaut, tout le monde a ce droit sur le schéma public. Pour autoriser les utilisateurs à manipuler les objets d'un schéma, des droits 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 utilisateur. Pour cela, le droit CREATE doit être donné sur ce schéma. Dans les bases de données mises à jour à partir de PostgreSQL 14 ou les versions précédentes, tout le monde a ce droit sur le 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 à de schémas privés. Pour implémenter cela, assurez-vous tout d'abord que les schémas n'ont pas le droit CREATE pour public. Ensuite, pour chaque utilisateur devant créer des objets permanents, créez un schéma de même nom que l'utilisateur, par exemple CREATE SCHEMA alice AUTHORIZATION alice. (Rappelez-vous que le chemin de recherche par défaut commence avec $user, ce qui est remplacé par le nom de l'utilisateur. De ce fait, si chaque utilisateur a un schéma séparé, ils accèdent à leur schéma par défaut.) Cette méthode est une méthode d'utilisation de schéma sécurisé sauf si un utilisateur malin est le propriétaire de la base de données ou dispose de l'attribut ADMIN OPTION sur un rôle adéquat, auquel cas aucun modèle d'utilisation de schéma sécurisé n'existe.

    Avec PostgreSQL 15 et les versions suivantes, la configuration par défaut accepte cette méthode d'utilisation. Dans les versions précédentes, ou lors de l'utilisation d'une base de donnée mise à jour d'une version précédente, vous aurez besoin de supprimer l'attribut CREATE sur public à partir du schéma public (lancez REVOKE CREATE ON SCHEMA public FROM PUBLIC). Puis considérez la réalisation d'un audit du schéma public pour des objets nommés comme les objets du schéma pg_catalog.

  • 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". Puis donnez les droits pour créer des objets dans le schéma public. Seuls les noms qualifiés choisiront des objets du schéma public. Bien que les références de table qualifiées soient correctes, les appels aux fonctions dans 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 s'est vu donné ADMIN OPTION sur un rôle adéquat.

  • Conserver le chemin de recherche par défaut, et donner les droits de création sur le schéma public. 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. Dans les bases mises à jour à partir de PostgreSQL 14 ou antérieures, c'est le comportement par défaut.

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.