Objectif

Apporter un niveau de sécurité supplémentaire en restreignant l’accès aux privilèges d’administration de PostgreSQL aux seuls scripts de migration (et non plus à toutes les applications).

Historique

Jusqu'à présent, le compte utilisateur PostgreSQL utilisé par les applications était superuser de la base de données. C'est une faille de sécurité potentielle et n'est donc pas souhaitable.

On a donc décidé de restreindre les privilèges des applications aux seules opérations utiles pour leur exécution : SELECT, INSERT, DELETE, UPDATE ….

Cadrage technique

Concrètement, le moteur PostgreSQL utilisé doit disposer de 2 comptes utilisateurs distincts :

le compte historique existant aujourd'hui, avec les droits supersuser, est conservé pour l’exécution des scripts de migration des applications (stockés dans le dossier /resources/sql).

Les objets dans Postgres (tables, schémas…) sont possédés par le compte qui les a créé.
L'utilisateur existant actuellement doit continuer à exécuter les scripts de migration afin d'éviter des incohérences de possession.

un nouveau compte nommé “apps”, sans les droits superuser, doit être créé et ajouté à la configuration de l’ENT (entcore.conf). Il sera utilisé par les applications directement.

Postgres ne fait pas de distinction entre les notions de ROLE et de USER.
=> Un USER est en réalité un rôle avec un droit de LOGIN.

En pratique, “apps” est donc le ROLE qui disposera des droits nécessaires aux applis.
On peut alors créer un utilisateur disposant des droits de “apps”, ou lui donner le droit de LOGIN.

Le reste du guide utilise le nom “apps” sans distinction.

Ces 2 comptes utilisateurs sont alors exploitables via 2 instances différentes du persistor SQL https://github.com/opendigitaleducation/mod-postgresql/blob/master/src/main/java/fr/wseduc/sql/SqlPersistor.java, accessibles via le bus vertx à 2 adresses différentes :

"address": "sql.persistor.admin" pour les scripts de migration,
"address": "sql.persistor" pour les applications.

Le compte utilisateur historique étant configuré sous l’adresse "sql.persistor", il doit basculer sous l'adresse "sql.persistor.admin".

Le nouveau compte “apps” sera rattaché à l’adresse "sql.persistor".

Enfin, chaque application pourra s’attribuer, dans ses scripts de migration, les privilèges dont elle a besoin.

C’est le module infra qui exécute tous les scripts de migration au démarrage de l'ENT.
Il dispose pour cela d’une config "postgresConfig": {sqlAdminAdress : "" }, variabilisée avec une valeur par défaut à "sql.persistor.admin"

Procédure générale

Création du user “apps” dans PostgreSQL

Depuis la CLI postgres :

CREATE USER "apps" WITH PASSWORD 'ChangeThisPassword';
GRANT TEMPORARY ON DATABASE ong TO "apps";
GRANT USAGE ON SCHEMA information_schema TO "apps";
GRANT SELECT ON information_schema.tables TO "apps";

Adaptation du paramétrage de l’ENT

Il doit y avoir 2 mods postgresql déclarés.
Si ce n’est pas déjà fait, ajouter le paramétrage manquant, par exemple :

{
    "name" : "fr.wseduc~mod-postgresql~1.2-SNAPSHOT",
    "priority" : 5,
    "waitDeploy" : true,
    "worker" : true,
    "multi-threaded" : true,
    "config" : {
      "address" : "sql.persistor",
      "url" : "jdbc:postgresql://postgres:5432/ong?stringtype=unspecified",
      "username" : "apps",
      "password" : "ChangeThisPassword",
      "pool_size" : 1,
      "cwd" : "/srv/springboard/mods/fr.wseduc~mod-postgresql~1.2-SNAPSHOT",
      "assets-path" : "/srv/springboard"
    },
    "consulKey" : "fr.wseduc~mod-postgresql~1.2-SNAPSHOT"
  }, {
    "name" : "fr.wseduc~mod-postgresql~1.2-SNAPSHOT",
    "priority" : 5,
    "waitDeploy" : true,
    "worker" : true,
    "multi-threaded" : true,
    "config" : {
      "address" : "sql.persistor.admin",
      "url" : "jdbc:postgresql://postgres:5432/ong?stringtype=unspecified",
      "username" : "the-historic-account",
      "password" : "ChangeThisPassword",
      "pool_size" : 1,
      "cwd" : "/srv/springboard/mods/fr.wseduc~mod-postgresql~1.2-SNAPSHOT",
      "assets-path" : "/srv/springboard"
    },
    "consulKey" : "fr.wseduc~mod-postgresql~1.2-SNAPSHOT"
  }

Notes pour les développeurs

SB local

Script shell à exécuter pour initialiser le rôle apps localement, une fois postgres démarré:

  BASE_CONTAINER_NAME=`basename "$PWD"`
  POSTGRES_USER=`grep 'sqlUsername=' conf.properties | sed 's/sqlUsername=//'`
  POSTGRES_PASSWORD=`grep 'sqlPassword=' conf.properties | sed 's/sqlPassword=//'`
  POSTGRES_DB=`grep 'sqlDb=' conf.properties | sed 's/sqlDb=//'`

  docker exec -i ${BASE_CONTAINER_NAME}_postgres_1 psql -U $POSTGRES_USER $POSTGRES_DB <<-EOSQL
    DO \$\$
    BEGIN
      CREATE USER "apps" WITH PASSWORD '$POSTGRES_PASSWORD';
      EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
    END
    \$\$;
    GRANT TEMPORARY ON DATABASE $POSTGRES_DB TO "apps";
    GRANT USAGE ON SCHEMA information_schema TO "apps";
    GRANT SELECT ON information_schema.tables TO "apps";
EOSQL

Autre possibilité, se connecter à la CLI postgres et exécuter les commandes manuellement…

Attribution des privilèges requis par chaque application

Chaque appli doit désormais attribuer à apps les privilèges dont elle a besoin, dans ses scripts de migration.

Créer un script de migration dans le dossier resources/sql,
Les privilèges minimaux à s’attribuer sont sont :
A adapter aux besoins spécifiques de l’application.

Les applications suivantes ont déjà été traitées :

Application	Package	Nom du schéma BDD (remplace xxx)

Application	Package	Nom du schéma BDD (remplace xxx)
infra	org.entcore~infra	PAS DE SCHEMA NI TABLE
Messagerie	org.entcore~conversation	`conversation`
Messagerie	fr.openent~zimbra	`zimbra`
Page d’accueil	org.entcore~timeline	`flashmsg`
Fake SSO	fr.wseduc~fake-sso	`sso`
Réservation de ressources	net.atos~rbs	`rbs`
Actualités	net.atos~actualites	`actualites`
Exercices	fr.openent~exercizer	`exercizer`
Vie scolaire	fr.openent~viescolaire	`viesco`
Vie scolaire	fr.openent~viescolaire	`memento`
Compétences	fr.openent~competences	`notes`
Carnet de liaison	fr.wseduc~schoolbook	`schoolbook`
Support	net.atos~support	`support`
Cahier de texte	fr.openent~diary	A FAIRE
Presences	fr.openent~presences	`presences`
	fr.openent~statistics-presences	`presences_statistics`
	fr.openent~incidents	`incidents`
	fr.openent~massmailing	`massmailing`
Emploi du temps	fr.cgi~edt	`edt`
CRM	com.opendigitaleducation~crm	`crm`
Formulaires	fr.openent~formulaire	`formulaire`
Visioconf	fr.openent~web-conference	`webconference`
CRRE	fr.openent~crre	`crre`
Lystore	fr.openent~lystrore	`lystore`
Mediacentre	fr.openent~mediacentre	`mediacentre`
MiniBadge	fr.cgi~minibadge	`minibadge`
Moodle	fr.openent~moodle-connector	`moodle`
Nextcloud	fr.openent~nextcloud	`nextcloud`
Scratch	fr.opent~scratch	`scratch`
Pmb	fr.openent~pmb	`pmb`

Framework

2022-10 : Guide de migration PostgreSQL (droits restreints)

Analytics