Schémas

(sujet mis à jour avec la version 10)

Poupées gigognes

      Un schéma (SCHEMA) appartient à une base de données (DATABASE). Une table (TABLE) ou une vue (VIEW) etc. appartiennent à un schéma (SCHEMA). Une table est donc désignée de manière complète sous la forme DATABASE.SCHEMA.TABLE. Cependant, avec PostgreSQL, vous vous connectez à une DATABASE et ne pouvez ensuite accéder qu’aux éléments de cette DATABASE. Cela signifie que, même s’il est possible d’utiliser DATABASE.SCHEMA.ELEMENT, il est plus courant de rencontrer SCHEMA.ELEMENT puisque DATABASE est nécessairement la base courante.
      Les utilisateurs d’Oracle Database ne créent pas de SCHEMAS. La création d’un utilisateur crée implicitement un schéma portant le nom de cet utilisateur. Il existe pourtant sous Oracle Database une syntaxe CREATE SCHEMA largement méconnue. Mais il est bien précisé dans la documentation qu’il s’agit simplement de regrouper dans une transaction une suite de créations de tables et d’attributions de privilèges objet sur ces tables. Sans CREATE SCHEMA, Oracle Database validerait la transaction après chaque création de table et attribution de privilège. Voir la documentation Oracle si vous voulez des précisions.
      PostgreSQL permet de créer explicitement des schémas. Le paramètre de niveau session SEARCH_PATH précise l’ordre de recherche dans les schémas si vous désignez un élément par ELEMENT et non par SCHEMA.ELEMENT. L’ordre de recherche par défaut est "$user", public. Cela signifie qu’en l’absence de schéma portant son nom un utilisateur créera ses tables etc. dans un schéma PUBLIC si vous ne précisez pas SCHEMA.ELEMENT. PostgreSQL va par ailleurs plus loin que la norme SQL en permettant à un utilisateur de créer un élément dans un schéma ne lui appartenant pas tout en conservant la propriété de cet élément. Cela peut donner des situations compliquées comme dans cet exemple :

-- connecte postgres (SUPERUSER) select version(); version ----------------------------------------------------------------------------------------------- PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 ligne) create role toto login; CREATE ROLE create schema toto authorization toto; CREATE SCHEMA create table toto.titi(a smallint); CREATE TABLE postgres=# set search_path="$user",toto; SET postgres=# \dt Liste des relations Schéma | Nom | Type | Propriétaire --------+------+-------+-------------- toto | titi | table | postgre -- connecte toto select * from titi; ERREUR: droit refusé pour la relation titi drop schema toto cascade; NOTICE: DROP cascade sur table titi -- connecte postgres (SUPERUSER) set search_path="$user",toto; SET select * from titi; ERREUR: la relation " titi " n'existe pas

      Assez trompeur non ? L’utilisateur postgres, un superuser, a créé une table dans le schéma de toto. Toto ne pouvait pas lire la table. Mais il a pu détruire le schéma toto et donc la table appartenant à postgres. Remarque de Vincent et Julien : ce serait comme planter un pied de tomates dans le jardin de son voisin. Le voisin pourrait arracher le pied de tomates mais pas récolter les tomates si vous ne lui en donnez pas le droit.
      Ce n’est pas parce que quelque chose est possible qu’il est recommandé de le faire. Je conseille, spécialement si vous êtes habitués à Oracle Database, de vous en tenir aux principes suivants :       Bref, à moins d’avoir une excellente raison, privilégiez la simplicité. Cela vous évitera les confusions et conflits entre voisins !

Mise à jour : 27/03/2016