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 quaux éléments de cette DATABASE. Cela signifie que, même sil est possible dutiliser DATABASE.SCHEMA.ELEMENT, il est plus courant de rencontrer SCHEMA.ELEMENT puisque DATABASE est nécessairement la base courante.
Les utilisateurs dOracle Database ne créent pas de SCHEMAS. La création dun 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 quil sagit simplement de regrouper dans une transaction une suite de créations de tables et dattributions 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 lordre de recherche dans les schémas si vous désignez un élément par ELEMENT et non par SCHEMA.ELEMENT. Lordre de recherche par défaut est "$user", public. Cela signifie quen labsence 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 :
Assez trompeur non ? Lutilisateur 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 nest pas parce que quelque chose est possible quil 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 :
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