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.
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
incluant les types de données, fonctions et opérateurs. Dans un schéma, deux
objets du même type ne peuvent pas avoir le même nom. De plus, les tables,
séquences, index, vues, vues matérialisées et les tables distantes partagent
le même espace de nom, donc par exemple un index et une table doivent avoir
des noms différents s'ils sont dans le même schéma. 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.
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.15 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 SCHEMAnom_schemaAUTHORIZATIONnom_utilisateur;
Le nom du schéma peut être omis, auquel cas le nom de l'utilisateur est utilisé. Voir la Section 5.10.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.
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, 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.27 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. 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.)
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_.
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.
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
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.