Schémas

(sujet préalablement traité avec la version 9.4)

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 10.3 (Ubuntu 10.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu,e compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 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 SET search_path="$user",toto; SET \dt Liste des relations Schéma | Nom | Type | Propriétaire --------+------+-------+-------------- toto | titi | table | postgres (1 ligne) -- connecte toto SELECT * FROM titi; ERREUR: droit refusé pour la relation titi DROP SCHEMA toto CASCADE; NOTICE: DROP cascade sur table titi DROP SCHEMA -- connecte postgres, superuser SET search_path="$user",toto; SET SELECT * FROM titi; ERREUR: la relation « titi » n'existe pas LIGNE 1 : select * from titi;

      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 :       Les versions mineures de PostgreSQL de 9.3 à 10 sorties le 1er mars 2018 documentent une faille de sécurité, CVE-2018-1058. Cette faille permet des attaques de type cheval de Troie liées aux schémas et n'est exploitable que par un utilisateur préalablement authentifié. Pour éviter cette faille, il convient de ne jamais avoir dans son chemin de recherche (search_path) de schémas dans lesquels un utilisateur non sûr peut écrire, à commencer par le schéma public. Cette précaution est bien sûr à respecter particulièrement lors des opérations effectuées en tant que SUPERUSER.
      La configuration que je proposais précédemment permet d'éviter les confusions et erreurs humaines, mais pas d'éventuelles malveillances par un utilisateur authentifié mais non sûr. La communauté PostgreSQL recommande à présent d'aller plus loin pour éviter ces malveillances. J'ajoute donc à la liste :       Cette page en anglais vous donne des explications et consignes complémentaires.

      Démonstration :
SELECT version(); version -------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 10.3 (Ubuntu 10.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu,e compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit (1 ligne) SHOW search_path; search_path ----------------- "$user", public (1 ligne) REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE CREATE ROLE test LOGIN PASSWORD 'aereelekrelellllee556566'; CREATE ROLE CREATE SCHEMA test AUTHORIZATION test; CREATE SCHEMA ALTER SYSTEM SET search_path = "$user"; ALTER SYSTEM SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 ligne) SHOW search_path; search_path ------------- "$user" (1 ligne)

      L'utilisateur test peut à présent travailler, donner des privilèges sur ses objets à d'autres utilisateurs etc.
      Je recommande d'appliquer les recommandations de cette page avec la méthode d'automatisation utilisée sur vos environnements. Privilégier la simplicité et la sécurité doit être la règle et pas l'exception. A moins d’avoir une excellente raison : un schéma par utilisateur portant son nom et ne contenant que des objets de cet utilisateur, interdiction d'utiliser le schéma public.
      Comme ce sont toujours les cordonniers les plus mal chaussés, vous pouvez noter que je n'utilise pas toujours les recommandations de cette page dans les exemples de mes autres pages. Je les fais bien sûr appliquer sur les environnements de développement, test et production dont j'ai la charge mais, afin de toujours donner le bon exemple, je vais les utiliser même sur mes environnements de démo jetables.
      Enfin, au sujet du traqueur, il a été mis à jour pour limiter search_path mais je recommandais de toute façon déjà d'aller plus loin au niveau des restrictions. Il est en effet conseillé de ne jamais le connecter directement à une base contenant des données applicatives et de ne le connecter qu'à des bases dédiées sans AUCUN privilège public (e.g : CREATE DATABASE traqueur + REVOKE ALL PRIVILEGES ON DATABASE traqueur FROM public)

Mise à jour : 03/03/2018