#postgresql
mardi 17 janvier 2017
Ce billet a été vu pour la première fois sur le blog de Synbioz le 17 January 2017 sous licence CC BY-NC-SA.

Foreign data wrappers ou comment interroger une base distante avec PostgreSQL ?

Il y a quelques semaines, je vous ai présenté quelques fonctionnalités méconnues de PostgreSQL et comment, à l’aide d’une bonne indexation de ses données, on peut tirer parti de la puissance de ce SGBD.

Je vous propose aujourd’hui de découvrir une autre fonctionnalité de PostgreSQL qui nous a été d’un grand secours, il y a peu, sur l’un de nos projets. Mais avant toute chose, précisons un peu le contexte.

La situation

Le projet en question est celui d’une fédération sportive qui, pour s’authentifier sur ses différentes applications, fait usage d’un SSO. Jusqu’ici rien de bien sorcier, le SSO dispose de sa propre base de données et contient les identifiants de connexion ainsi que les données personnelles des utilisateurs. Celles-ci étant de fait mutualisées au sein du SSO plutôt que dupliquées pour chaque application.

Nous avons donc un SSO et une application ayant chacun leur propre base de données. Seulement voilà, il arrive un moment où nous avons besoin de filtrer les données de l’application en fonction de critères dépendants des données du SSO. Par exemple, nous pourrions avoir besoin de lister l’ensemble des licences fédérales appartenant à des enfants de moins de 16 ans. Les données concernant les licences sont stockées au sein de la BDD de l’application, alors que la date de naissance de leur propriétaire se trouve côté SSO.

La fausse bonne idée

Notre première réaction face à cette situation est probablement la suivante : “Qu’à cela ne tienne ! Je vais récupérer tous les identifiants des enfants de moins de 16 ans en requêtant le SSO ; puis j’effectuerai une seconde requête sur la BDD de l’application en filtrant sur les identifiants ainsi récupérés.

Alors oui, ça marche. Ou du moins, ça ne pose aucun problème tant qu’il s’agit de récupérer une poignée d’identifiants. Malheureusement pour nous, ici chaque licencié de la fédération peut accéder à un espace personnel. On parle ici de plusieurs centaines de milliers d’utilisateurs. Il est donc impensable de procéder ainsi.

Et la pagination !?, me direz-vous. Remarque pertinente… dans une certaine mesure. Elle sera en effet nécessaire pour éviter d’afficher 10 000 licenciés d’une traite. Mais si les données doivent être triées selon des critères dépendants des informations du SSO et de l’application (ex: trier les licenciés par age puis par numéro de licence), alors nous sommes de nouveau coincés.

La solution : Foreign Data Wrapper

C’est là que PostgreSQL nous vient en aide avec une extension nommée postgres_fdw. FDW pour Foreign Data Wrapper. Cette extension va nous permettre de déclarer un serveur tiers auquel PostgreSQL va pouvoir se connecter et ainsi le requêter pour nous de manière totalement transparente !

La mise en place

Voyons comment cela fonctionne. Il nous faut dans un premier temps installer l’extension :

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

Puis déclarer le serveur auquel nous souhaitons nous connecter

CREATE SERVER foreign_sso_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'sso.example.com', port '5432', dbname 'sso');

Ainsi que l’identité de l’utilisateur qui sera utilisé par PostgreSQL pour se connecter à ce server

CREATE USER MAPPING FOR USER
  SERVER foreign_sso_server
  OPTIONS (user 'ssouser', password 'ssopass');

Voilà ! PostgreSQL a maintenant tout en main pour interroger notre SSO.

Les différents wrappers

Prenons le temps de nous arrêter sur la création de notre serveur foreign_sso_server. Nous avons opté pour le data wrapper postgres_fdw qui nous permettra de déclarer des tables étrangères (foreign tables) — c’est à dire des tables présentes dans une autre base de données PostgreSQL. Ce wrapper est disponible depuis la version 9.3 de PostgreSQL.

Si tel avait été notre besoin, nous aurions pu utiliser file_fdw pour créer des tables étrangères à partir de fichiers à plat. Ou encore un wrapper non standard tel que tds_fdw pour se connecter à d’autres SGBD.

Les tables étrangères

Il existe deux approches pour déclarer les tables étrangères que nous souhaitons utiliser. La première consiste à faire usage de CREATE FOREIGN TABLE. La seconde, disponible depuis PostgreSQL 9.5, fait appel à IMPORT FOREIGN SCHEMA.

Puisque nous n’avons pas besoin d’accéder à l’ensemble des tables du SSO, mais uniquement à la table users, nous choisirons la première approche.

CREATE FOREIGN TABLE sso_users (
  id uuid,
  created_at timestamp,
  updated_at timestamp,
  first_name varchar,
  last_name varchar,
  birth_date date,
  email varchar
)
SERVER foreign_sso_server
OPTIONS (schema_name 'public', table_name 'users');

Cette approche nous permet de ne rendre accessible que les tables, et au sein de celles-ci, les champs auxquels nous autorisons l’accès à travers le wrapper. J’ai ici arbitrairement choisi de préfixer le nom de la table étrangère par sso_; j’aurais tout aussi bien pu la nommer users et lui spécifier un schéma sso qui serait propre aux tables étrangères venant du SSO, afin d’éviter tout conflit avec une table de notre application. Par défaut, si aucun schéma n’est précisé explicitement, PostgreSQL utilisera le schéma public.

Et maintenant ?

Maintenant ? Et bien utilisons notre table sso_users comme n’importe quelle autre table de la BDD, PostgreSQL se charge de tout !

SELECT COUNT(*) FROM "sso_users";

-- +---------+
-- |   count |
-- |---------|
-- | 891,224 |
-- +---------+
-- SELECT 1
-- Time: 1.090s (a second)

SELECT "sso_users"."uid", "sso_users"."birth_date", "licenses"."number"
  FROM "licenses"
  INNER JOIN "sso_users" ON "licenses"."user_uid" = "sso_users"."uid"
  WHERE "sso_users"."birth_date" >= date('2000-01-12')
  ORDER BY "sso_users"."birth_date", "licenses"."number"
  LIMIT 25;

-- +--------------------------------------+--------------+----------+
-- | uid                                  | birth_date   | number   |
-- |--------------------------------------+--------------+----------|
-- | ea5f51fd-c2e0-e111-9ce3-000c298375e7 | 2000-01-12   | 05995627 |
-- | 1457229e-b8e0-e111-9ce3-000c298375e7 | 2000-01-12   | 09194839 |
-- | b736bcec-89e0-e111-9ce3-000c298375e7 | 2000-01-12   | 11157277 |
-- | c9046468-b938-e211-a856-0025900a66fc | 2000-01-12   | 13137809 |
-- | fc13a041-367c-e212-89cb-0025900bcc84 | 2000-01-12   | 13225470 |
-- | eef2bf47-de3f-e312-9d3c-0025900bcc84 | 2000-01-12   | 14131832 |
-- | a934fc45-324c-e312-9d3c-0025900bcc83 | 2000-01-12   | 14148658 |
-- | 74b88cbc-0284-e312-9d3c-0025901bcc83 | 2000-01-12   | 14211246 |
-- | 5dd29210-dcb8-e312-8d3c-0025901bcc84 | 2000-01-12   | 14240986 |
-- | 4facb35a-a56b-e411-a731-0025900bcc84 | 2000-01-12   | 15165083 |
-- | 023cd654-8d74-e411-a731-0025900bcc84 | 2000-01-12   | 15178880 |
-- | 0b36e0f0-c6a5-e411-94a1-0025900bcc84 | 2000-01-12   | 15220661 |
-- | 04904146-29b1-e410-93a1-0025899bcc84 | 2000-01-12   | 15226229 |
-- | ddf796d8-23bb-e510-93a0-0025899bcc84 | 2000-01-12   | 16206232 |
-- | 6114e65b-edd0-e511-93a0-0025899bcc85 | 2000-01-12   | 16216526 |
-- | c8efe23c-2ce0-e111-9ce3-000c298375e8 | 2000-01-13   | 05953200 |
-- | 3414cdfe-9ee0-e111-9ce3-000c298375e8 | 2000-01-13   | 12168946 |
-- | ecf63840-9d4c-e311-9d3c-0025900bcc84 | 2000-01-13   | 14149366 |
-- | d3fe970c-3654-e311-aa47-0025900a66fc | 2000-01-13   | 14163342 |
-- | fe72fd11-a978-e311-9d3c-0025901bcc83 | 2000-01-13   | 14199223 |
-- | 45853af0-e082-e311-9d3c-0025901bcc84 | 2000-01-13   | 14208886 |
-- | 514c9226-8a89-e311-a9dd-0025901a66fc | 2000-01-13   | 14215202 |
-- | 4606244c-3aa4-e311-a8dd-0025901a66fc | 2000-01-13   | 14233290 |
-- | 4f06244c-3aa4-e310-a8dd-0025900a66fc | 2000-01-13   | 14233291 |
-- | b1d4cd00-8dbb-e313-a9dd-0025900a66fc | 2000-01-13   | 14242186 |
-- +--------------------------------------+--------------+----------+
-- SELECT 25
-- Time: 5.398s (5 seconds)

Restons vigilants !

Cette solution, bien que tout à fait acceptable, n’est pas exempte de défauts. Faisons-en le tour rapidement.

Les performances

La première chose que l’on constate des exemples ci-dessus, c’est que les performances ne sont pas au rendez-vous. On est bien loin des quelques millisecondes auxquelles on est en droit de s’attendre d’une telle requête si elle avait été faite exclusivement en local.

Cependant, rien d’étonnant non plus car, même si PostgreSQL nous masque la complexité de cette requête, elle est belle et bien présente ; et il est toujours nécessaire de contacter un serveur distant, ce qui implique forcément une certaine latence.

Si cinq secondes de requêtage peuvent être tolérées dans le cas d’une tâche différée, il n’en va pas de même lorsqu’il s’agit de rendre une page HTML sur le navigateur de l’utilisateur, ou de répondre à un appel à une API. Dans ce cas, nous pourrions par exemple utiliser une vue matérialisée correctement indexée.

La mise à jour du schéma

Tout comme pour les vues matérialisées, qui pourraient faire l’objet d’un autre article, la mise à jour de la structure de nos tables étrangères n’est pas automatique. Il est donc important de penser à créer une migration des tables étrangères en cas de changement sur les champs auxquels nous souhaitons accéder. Cette migration ressemblerait à ceci :

BEGIN;
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

DROP SERVER IF EXISTS foreign_sso_server CASCADE;
CREATE SERVER foreign_sso_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'sso.example.com', port '5432', dbname 'sso');
CREATE USER MAPPING FOR USER
  SERVER foreign_sso_server
  OPTIONS (user 'ssouser', password 'ssopass');

DROP FOREIGN TABLE IF EXISTS sso_users;
CREATE FOREIGN TABLE sso_users (
  id uuid,
  …
)
SERVER foreign_sso_server
OPTIONS (schema_name 'public', table_name 'users');
COMMIT;

Attention cependant, ayant choisi de faire appel à CREATE FOREIGN TABLE, nous sommes contraints de déclarer explicitement chacun des champs de nos tables étrangères. Cela nous demande donc de recréer une nouvelle migration à chaque fois que la structure de celles-ci évolue.

En optant pour la seconde approche qui consiste à importer le schéma complet de notre SSO, nous aurions pu écrire ceci :

BEGIN;
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

DROP SERVER IF EXISTS foreign_sso_server CASCADE;
CREATE SERVER foreign_sso_server … ;
CREATE USER MAPPING … ;

DROP SCHEMA IF EXISTS sso;
CREATE SCHEMA sso;
IMPORT FOREIGN SCHEMA public
  FROM SERVER foreign_sso_server
  INTO sso;
COMMIT;

Ainsi formulé, ce script de migration peut être intégré à notre processus de déploiement et joué une fois les autres migrations effectuées. De cette manière, nous nous assurons d’être constamment à jour.

Pour résumer

PostgreSQL nous prouve une fois de plus ses qualités et son adaptabilité aux situations les plus diverses. Chaque nouvelle version nous apporte son lot de nouveautés et d’améliorations. La toute récente 9.6, sortie le 29 septembre 2016, n’est pas en reste et notre fameuse extension postgres_fdw peut dorénavant réaliser certaines opérations à distance, les performances s’en trouvant de fait accrues.

Major enhancements in PostgreSQL 9.6 include:
• postgres_fdw now supports remote joins, sorts, UPDATEs, and DELETEs
• …

PostgreSQL, quand on prend le temps de s’y pencher, nous réserve plein de bonnes surprises. Alors soyez curieux et n’hésitez pas à parcourir sa documentation !

Ressources