Les schémas PostgreSQL

Les schémas PostgreSQL permettent de segmenter et d'isoler les tables au sein d'une base de données.

Ci dessous, l'essentiel du billet Using Postgres Schemas.

Hiérarchie PostgreSQL

Les bases de données peuvent avoir plusieurs schémas, et les schémas peuvent avoir plusieurs tables :

Généralités

Schéma par défaut : public.

Lister les schémas et leurs propriétaires :

  • \dt (colonne de gauche)
  • \dn+ (liste tous les schémas d'une base de données)

search_path

Des schémas différents peuvent avoir des tables portant le même nom.

CREATE SCHEMA "private";
CREATE TABLE "private"."example" (id SERIAL PRIMARY KEY);
CREATE TABLE "public"."example" (id SERIAL PRIMARY KEY);

Dans ce cas, avec une requête non-qualifée (sans préciser le schéma), la priorité du schéma est déterminée par la valeur search_path de l'utilisateur :

SHOW search_path;

       search_path
--------------------------
 "$user", public, private

search_path peut être modifié de façon permanente :

-- Current session.
SET search_path TO "$user", "private", "public";
-- Future sessions.
ALTER ROLE postgres SET search_path = "$user", "private", "public";

En définissant search_path comme une chaîne vide, on force les requêtes à utiliser des noms pleinement qualifiés.

Noms pleinement qualifiés

INSERT INTO private.example DEFAULT VALUES;

Quand on utilise des guillemets, le schéma et la table doivent être séparés :

INSERT INTO "private"."example" DEFAULT VALUES;

Car . est un caractère valide dans le nom d'une table (mais source intarissable de complications).

Privilèges

Comme tous les objets PostgreSQL, les schémas appartiennent au rôle qui les a créés, sauf indication contraire.

Pour faire d'un nouvel utilisateur website le propriétaire d'un schéma orm :

CREATE USER website WITH PASSWORD 'secret';
CREATE SCHEMA orm AUTHORIZATION website;

Ce n'est pas parce que l'utilisateur website est propriétaire du schéma orm qu'il est propriétaire de tous les objets du schéma.

Si un superuser crée une table dans ce schéma, website n'aura pas les droits dessus.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA orm TO website;

Pour vérifier les privilèges d'accès, utiliser la commande \z :

 Schema |         Name         |   Type   |     Access privileges     
--------+----------------------+----------+---------------------------
 orm    | privileged           | table    | postgres=arwdDxt/postgres+
        |                      |          | website=arwdDxt/postgres
 orm    | privileged_id_seq    | sequence |

Ci-dessus, website n'a pas les droits de type sequence qui permettent de générer la clé primaire, d'où :

INSERT INTO orm.privileged DEFAULT VALUES;
ERROR:  permission denied for sequence privileged_id_seq

Pour y remédier :

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA orm TO website;

Tous les prochains objets créés par un superuser devront être autorisés avant que l'utilisateur website ne puisse y accéder.

Il existe heureusement des commandes pour donner les droits à l'utilisateur website sur toutes les futures tables et séquences créées par un superuser :

ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA orm GRANT ALL ON TABLES TO website;
ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA orm GRANT ALL ON SEQUENCES TO website;

Avant Module pathlib en Python Après EditorConfig

Tag Kemar Joint