Sur ce document

Formation	Module X1
Titre	Extensions PostgreSQL pour l’utilisateur
Révision	24.09
PDF	https://dali.bo/x1_pdf
EPUB	https://dali.bo/x1_epub
HTML	https://dali.bo/x1_html
Slides	https://dali.bo/x1_slides
TP	https://dali.bo/x1_tp
TP (solutions)	https://dali.bo/x1_solutions

Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse formation@dalibo.com !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Jean‑Paul Argudo, Alexandre Anriot, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Jehan‑Guillaume de Rorthais, Ronan Dunklau, Vik Fearing, Stefan Fercot, Pierre Giraud, Nicolas Gollet, Dimitri Fontaine, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurées autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux détails qui ne peuvent être données à l’oral.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

Paternité
Pas d’utilisation commerciale
Partage des conditions initiales à l’identique

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.

Extensions PostgreSQL pour l’utilisateur

Qu’est-ce qu’une extension ?

Pour ajouter :
- types de données
- méthodes d’indexation
- fonctions et opérateurs
- tables, vues…
Tous sujets, tous publics
Intégrées (« contribs ») ou projets externes

Administration des extensions

Techniquement :

« packages » pour PostgreSQL, en C, SQL, PL/pgSQL…
Langages : SQL, PL/pgSQL, C (!)…
Ensemble d’objets livrés ensemble
contrib <> extension

Installation des extensions

Packagées ou à compiler
Par base :
- CREATE EXTENSION … CASCADE
- ALTER EXTENSION UPDATE
- DROP EXTENSION
- \dx
Listées dans pg_available_extensions

Au niveau du système d’exploitation, une extension nécessite des objets (binaires, scripts…) dans l’arborescence de PostgreSQL. De nombreuses extensions sont déjà fournies sous forme de paquets dans les distributions courantes ou par le PGDG, ou encore l’outil PGXN. Dans certains cas, il faudra aller sur le site du projet et l’installer soi-même, ce qui peut nécessiter une compilation.

L’extension doit être ensuite déclarée dans chaque base où elle est jugée nécessaire avec CREATE EXTENSION nom_extension. Les scripts fournis avec l’extension vont alors créer les objets nécessaires (vues, procédures, tables…). En cas de désinstallation avec DROP EXTENSION, ils seront supprimés. Une extension peut avoir besoin d’autres extensions : l’option CASCADE permet de les installer automatiquement.

Le mécanisme couvre aussi la mise à jour des extensions : ALTER EXTENSION UPDATE permet de mettre à jour une extension dans PostgreSQL suite à la mise à jour de ses binaires. Cela peut être nécessaire si elle contient des tables à mettre à jour, par exemple. Les versions des extensions disponibles sur le système et celles installées dans la base en cours sont visibles dans la vue pg_available_extensions.

Les extensions peuvent être exportées et importées par pg_dump/pg_restore. Un export par pg_dump contient un CREATE EXTENSION nom_extension, ce qui permettra de recréer d’éventuelles tables, et le contenu de ces tables. Une mise à jour de version majeure, par exemple, permettra donc de migrer les extensions dans leur dernière version installée sur le serveur (changement de prototypes de fonctions, nouvelles vues, etc.).

Sous psql, les extensions présentes dans la base sont visibles avec \dx :

 # \dx
                          Liste des extensions installées
        Nom         | Version |   Schéma   |                   Description
--------------------+---------+------------+--------------------------------------------------
 amcheck            | 1.2     | public     | functions for verifying relation integrity
 file_fdw           | 1.0     | public     | foreign-data wrapper for flat file access
 hstore             | 1.6     | public     | data type for storing sets of (key, value) pairs
 pageinspect        | 1.9     | public     | inspect the contents of database pages at...
 pg_buffercache     | 1.3     | public     | examine the shared buffer cache
 pg_prewarm         | 1.2     | public     | prewarm relation data
 pg_rational        | 0.0.1   | public     | bigint fractions
 pg_stat_statements | 1.10    | public     | track execution statistics of all SQL statements...
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 plpython3u         | 1.0     | pg_catalog | PL/Python3U untrusted procedural language
 postgres_fdw       | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
 unaccent           | 1.1     | public     | text search dictionary that removes accents

Contribs - Fonctionnalités

Livrées avec le code source de PostgreSQL
Habituellement packagées (postgresql-*-contrib)
De qualité garantie car maintenues par le projet
Optionnelles, désactivées par défaut
Ou en cours de stabilisation
Documentées : Chapitre F : « Modules supplémentaires fournis »

Une « contrib » est habituellement une extension, sauf quelques exceptions qui ne créent pas d’objets de catalogue (auto_explain par exemple). Elles sont fournies directement dans l’arborescence de PostgreSQL, et suivent donc strictement son rythme de révision. Leur compatibilité est ainsi garantie. Les distributions les proposent parfois dans des paquets séparés (postgresql-contrib-9.6, postgresql14-contrib…), dont l’installation est fortement conseillée.

Il s’agit soit de fonctionnalités qui n’intéressent pas tout le monde (hstore, uuid, pg_trgm, pgstattuple…), ou en cours de stabilisation (comme l’autovacuum avant PostgreSQL 8.1), ou à l’inverse de dépréciation (xml2).

La documentation des contribs est dans le chapitre F des annexes, et est donc fréquemment oubliée par les nouveaux utilisateurs.

Quelques extensions

…plus ou moins connues

pgcrypto

Module contrib de chiffrement :

Nombreuses fonctions pour chiffrer et déchiffrer des données
Gros inconvénient : oubliez les index sur les données chiffrées !
N’oubliez pas de chiffrer la connexion (SSL)
Permet d’avoir une seule méthode de chiffrement pour tout ce qui accède à la base

Fourni avec PostgreSQL, vous permet de chiffrer vos données :

directement ;
avec une clé PGP (gérée par exemple avec GnuPG), ce qui est préférable ;
selon divers algorithmes courants ;
différemment selon chaque ligne/champ.

Voici un exemple de code:

CREATE EXTENSION pgcrypto;
UPDATE utilisateurs SET mdp = crypt('mon nouveau mot de passe',gen_salt('md5'));
INSERT INTO table_secrete (encrypted)
VALUES (pgp_sym_encrypt('mon secret','motdepasse'));

L’appel à gen_salt permet de rajouter une partie aléatoire à la chaîne à chiffrer, ce qui évite que la même chaîne chiffrée deux fois retourne le même résultat. Cela limite donc les attaques par dictionnaire.

La base effectuant le (dé)chiffrement, cela évite certains allers-retours. Il est préférable que la clé de déchiffrement ne soit pas dans l’instance, et soit connue et fournie par l’applicatif. La communication avec cet applicatif doit être sécurisée par SSL pour que les clés et données ne transitent pas en clair.

Un gros inconvénient des données chiffrées dans la table est l’impossibilité complète de les indexer, même avec un index fonctionnel : les données déchiffrées seraient en clair dans cet index ! Une recherche implique donc de parcourir et déchiffrer chaque ligne…

hstore : stockage clé/valeur

Contrib
Type hstore
Stockage clé-valeur
Plus simple que JSON

INSERT INTO demo_hstore (meta) VALUES ('river=>t');
SELECT * FROM demo_hstore WHERE meta@>'river=>t';

hstore fournit un type très simple pour stocker des clés/valeur :

CREATE EXTENSION hstore ;

CREATE TABLE demo_hstore(id serial, meta hstore);
INSERT INTO demo_hstore (meta) VALUES ('river=>t');
INSERT INTO demo_hstore (meta) VALUES ('road=>t,secondary=>t');
INSERT INTO demo_hstore (meta) VALUES ('road=>t,primary=>t');
CREATE INDEX idxhstore ON demo_hstore USING gist (meta);

SELECT * FROM demo_hstore WHERE meta@>'river=>t';

 id |     meta
----+--------------
 15 | "river"=>"t"

Cette extension a rendu, et rend encore, bien des services. Cependant le type JSON (avec le type binaire jsonb) est généralement préféré.

PostgreSQL Anonymizer

Extension externe (Dalibo)
Masquage statique et dynamique
Export anonyme (pg_dump_anon)
Les règles de masquage sont écrites en SQL
Autodétection de colonnes identifiantes
Plus simple et plus sûr qu’un ETL

Postgresql Anonymizer est une extension pour masquer ou remplacer les données personnelles dans une base PostgreSQL. Elle est développée par Damien Clochard de Dalibo.

Le projet fonctionne selon une approche déclarative, c’est à dire que les règles de masquage sont déclarées directement dans le modèle de données avec des ordres DDL.

Une fois que les règles de masquage sont définies, on peut accéder aux données masquées de 3 façons différentes :

export anonyme : extraire les données masquées dans un fichier SQL ;
masquage statique : supprimer une fois pour toutes les données personnelles ;
masquage dynamique : cacher les données personnelles seulement pour les utilisateurs masqués.

Par ailleurs, l’extension fournit toute une gamme de fonctions de masquage : randomisation, génération de données factices, destruction partielle, brassage, ajout de bruit, etc. On peut également écrire ses propres fonctions de masquage !

Au-delà du masquage, il est également possible d’utiliser une autre approche appelée généralisation qui est bien adaptée pour les statistiques et l’analyse de données.

Enfin, l’extension offre un panel de fonctions de détection qui tentent de deviner quelles colonnes doivent être anonymisées.

Un module de formation lui est consacré.

Exemple :

=# SELECT * FROM people;

 id | firstname | lastname |   phone
----+----------+----------+------------
 T1 | Sarah    | Conor    | 0609110911

Étape 1 : activer le masquage dynamique

=# CREATE EXTENSION IF NOT EXISTS anon CASCADE;
=# SELECT anon.start_dynamic_masking();

Étape 2 : déclarer un utilisateur masqué

=# CREATE ROLE skynet LOGIN;
=# SECURITY LABEL FOR anon ON ROLE skynet IS 'MASKED';

Étape 3 : déclarer les règles de masquage

=# SECURITY LABEL FOR anon ON COLUMN people.lastname
-# IS 'MASKED WITH FUNCTION anon.fake_last_name()';

=# SECURITY LABEL FOR anon ON COLUMN people.phone
-# IS 'MASKED WITH FUNCTION anon.partial(phone,2,$$******$$,2)';

Étape 4 : se connecter avec l’utilisateur masqué

=# \c - skynet
=# SELECT * FROM people;

 id | firstname | lastname  |   phone
----+----------+-----------+------------
 T1 | Sarah    | Stranahan | 06******11

PostGIS

Projet indépendant, GPL, https://postgis.net/
Module spatial pour PostgreSQL
- Extension pour types géométriques/géographiques & outils
- La référence des bases de données spatiales
- « quelles sont les routes qui coupent le Rhône ? »
- « quelles sont les villes adjacentes à Toulouse ? »
- « quels sont les restaurants situés à moins de 3 km de la Nationale 12 ? »

PostGIS ajoute le support d’objets géographiques à PostgreSQL. C’est un projet totalement indépendant développé par la société Refractions Research sous licence GPL, soutenu par une communauté active, utilisée par des spécialistes du domaine géospatial (IGN, BRGM, AirBNB, Mappy, Openstreetmap, Agence de l’eau…), mais qui peut convenir pour des projets plus modestes.

Techniquement, c’est une extension transformant PostgreSQL en serveur de données spatiales, qui sera utilisé par un Système d’Information Géographique (SIG), tout comme le SDE de la société ESRI ou bien l’extension Oracle Spatial. PostGIS se conforme aux directives du consortium OpenGIS et a été certifié par cet organisme comme tel, ce qui est la garantie du respect des standards par PostGIS.

PostGIS permet d’écrire des requêtes de ce type :

SELECT restaurants.geom, restaurants.name FROM restaurants
  WHERE EXISTS (SELECT 1 FROM routes
                   WHERE ST_DWithin(restaurants.geom, routes.geom, 3000)
                AND route.name = 'Nationale 12')

PostGIS fournit les fonctions d’indexation qui permettent d’accéder rapidement aux objets géométriques, au moyen d’index GiST. La requête ci-dessus n’a évidemment pas besoin de parcourir tous les restaurants à la recherche de ceux correspondant aux critères de recherche.

La liste des fonctionnalités comprend le support des coordonnées géodésiques ; des projections et reprojections dans divers systèmes de coordonnées locaux (Lambert93 en France par exemple) ; des opérateurs d’analyse géométrique (enveloppe convexe, simplification…)

PostGIS est intégré aux principaux serveurs de carte, ETL, et outils de manipulation.

La version 3.0 apporte la gestion du parallélisme, un meilleur support de l’indexation SP-GiST et GiST, ainsi qu’un meilleur support du type GeoJSON.

Mais encore…

uuid-ossp : gérer des UUID
unaccent : supprime des accents
citex : recherche insensible à la casse

Autres extensions connues

Compatibilité :
- orafce
Extensions propriétaires évitant un fork :
- Citus (sharding)
- TimescaleDB (time series)
- être sûr que PostgreSQL a atteint ses limites !

Extensions pour de nouveaux langages

PL/pgSQL par défaut
Ajouter des langages :
- PL/python
- PL/perl
- PL/lua
- PL/sh
- PL/R
- PL/Java
- etc.

Accès distants

Accès à des bases distantes

Contribs :
- dblink (ancien)
- les foreign data wrappers : postgresql_fdw, mysql_fdw…
Sharding :
- PL/Proxy
- Citus

Contribs orientés DBA

Accès à des informations ou des fonctions de bas niveau :

pg_prewarm : sauvegarde & restauration de l’état du cache de la base
pg_buffercache : état du cache
pgstattuple (fragmentation des tables et index), pg_freespacemap (blocs libres), pg_visibility (visibility map)
pageinspect : inspection du contenu d’une page
pgrowlocks : informations détaillées sur les enregistrements verrouillés
pg_stat_statement (requêtes normalisées), auto_explain (plans)
amcheck : validation des index
… et de nombreux projets externes

PGXN

PostgreSQL eXtension Network :

Site web : pgxn.org
- nombreuses extensions
- volontariat
- aucune garantie de qualité
- tests soigneux requis
Et optionnellement client en python pour automatisation de déploiement
Ancêtre : pgFoundry
Beaucoup de projets sont aussi sur github

Le site PGXN fournit une vitrine à de nombreux projets gravitant autour de PostgreSQL.

PGXN a de nombreux avantages, dont celui de demander aux projets participants de respecter un certain cahier des charges permettant l’installation automatisée des modules hébergés. Ceci peut par exemple être réalisé avec le client pgxn fourni :

> pgxn search --dist fdw
multicdr_fdw 1.2.2
    MultiCDR *FDW* =================== Foreign Data Wrapper for representing
    CDR files stream as an external SQL table. CDR files from a directory
    can be read into a table with a specified field-to-column...

redis_fdw 1.0.0
    Redis *FDW* for PostgreSQL 9.1+ ============================== This
    PostgreSQL extension implements a Foreign Data Wrapper (*FDW*) for the
    Redis key/value database: http://redis.io/ This code is...

jdbc_fdw 1.0.0
    Also,since the JVM being used in jdbc *fdw* is created only once for the
    entire psql session,therefore,the first query issued that uses jdbc
    +fdw* shall set the value of maximum heap size of the JVM(if...

mysql_fdw 2.1.2
    ... This PostgreSQL extension implements a Foreign Data Wrapper (*FDW*)
    for [MySQL][1]. Please note that this version of mysql_fdw only works
    with PostgreSQL Version 9.3 and greater, for previous version...

www_fdw 0.1.8
    ... library contains a PostgreSQL extension, a Foreign Data Wrapper
    (*FDW*) handler of PostgreSQL which provides easy way for interacting
    with different web-services.

mongo_fdw 2.0.0
    MongoDB *FDW* for PostgreSQL 9.2 ============================== This
    PostgreSQL extension implements a Foreign Data Wrapper (*FDW*) for
    MongoDB.

firebird_fdw 0.1.0
    ...  -
    http://www.postgresql.org/docs/current/interactive/postgres-*fdw*.html *
    Other FDWs  - https://wiki.postgresql.org/wiki/*Fdw*  -
    http://pgxn.org/tag/*fdw*/

json_fdw 1.0.0
    ... This PostgreSQL extension implements a Foreign Data Wrapper (*FDW*)
    for JSON files. The extension doesn't require any data to be loaded into
    the database, and supports analytic queries against array...

postgres_fdw 1.0.0
    This port provides a read-only Postgres *FDW* to PostgreSQL servers in
    the 9.2 series. It is a port of the official postgres_fdw contrib module
    available in PostgreSQL version 9.3 and later.

osm_fdw 3.0.0
    ... "Openstreetmap pbf foreign data wrapper") (*FDW*) for reading
    [Openstreetmap PBF](http://wiki.openstreetmap.org/wiki/PBF_Format
    "Openstreetmap PBF") file format (*.osm.pbf) ## Requirements  *...

odbc_fdw 0.1.0
    ODBC *FDW* (beta) for PostgreSQL 9.1+
    =================================== This PostgreSQL extension implements
    a Foreign Data Wrapper (*FDW*) for remote databases using Open Database
    Connectivity(ODBC)...

couchdb_fdw 0.1.0
    CouchDB *FDW* (beta) for PostgreSQL 9.1+
    ====================================== This PostgreSQL extension
    implements a Foreign Data Wrapper (*FDW*) for the CouchDB document-
    oriented database...

treasuredata_fdw 1.2.14
    ## INSERT INTO statement This *FDW* supports `INSERT INTO` statement.
    With `atomic_import` is `false`, the *FDW* imports INSERTed rows as
    follows.

twitter_fdw 1.1.1
    Installation ------------  $ make && make install  $ psql -c "CREATE
    EXTENSION twitter_fdw" db The CREATE EXTENSION statement creates not
    only *FDW* handlers but also Data Wrapper, Foreign Server, User...

ldap_fdw 0.1.1
    ... is an initial working on a PostgreSQL's Foreign Data Wrapper (*FDW*)
    to query LDAP servers. By all means use it, but do so entirely at your
    own risk! You have been warned! Do you like to use it in...

git_fdw 1.0.2
    # PostgreSQL Git Foreign Data Wrapper [![Build Status](https://travis-
    ci.org/franckverrot/git_fdw.svg?branch=master)](https://travis-
    ci.org/franckverrot/git_fdw) git\_fdw is a Git Foreign Data...

oracle_fdw 2.0.0
    Foreign Data Wrapper for Oracle ===============================
    oracle_fdw is a PostgreSQL extension that provides a Foreign Data
    Wrapper for easy and efficient access to Oracle databases, including...

foreign_table_exposer 1.0.0
    # foreign_table_exposer This PostgreSQL extension exposes foreign tables
    like a normal table with rewriting Query tree. Some BI tools can't
    detect foreign tables since they don't consider them when...

cstore_fdw 1.6.0
    cstore_fdw ========== [![Build Status](https://travis-
    ci.org/citusdata/cstore_fdw.svg?branch=master)][status] [![Coverage](htt
    p://img.shields.io/coveralls/citusdata/cstore_fdw/master.svg)][coverage]
    ...

multicorn 1.3.5
    [![PGXN version](https://badge.fury.io/pg/multicorn.svg)](https://badge.
    fury.io/pg/multicorn) [![Build
    Status](https://jenkins.dalibo.info/buildStatus/public/Multicorn)]()
    Multicorn =========...

tds_fdw 1.0.7
    # TDS Foreign data wrapper * **Author:** Geoff Montee * **Name:**
    tds_fdw * **File:** tds_fdw/README.md ## About This is a [PostgreSQL
    foreign data...

pmpp 1.2.3
    ... Having foreign server definitions and user mappings makes for
    cleaner function invocations.

file_textarray_fdw 1.0.1
    ### File Text Array Foreign Data Wrapper for PostgreSQL This *FDW* is
    similar to the provided file_fdw, except that instead of the foreign
    table having named fields to match the fields in the data...

floatfile 1.3.0
    Also I'd need to compare the performance of this vs an *FDW*. If I do
    switch to an *FDW*, I'll probably use [Andrew Dunstan's
    `file_text_array_fdw`](https://github.com/adunstan/file_text_array_fdw)
    as a...

pg_pathman 1.4.13
    ... event handling;  * Non-blocking concurrent table partitioning;  *
    +FDW* support (foreign partitions);  * Various GUC toggles and
    configurable settings.

Pour peu que le Instant Client d’Oracle soit installé, on peut par exemple lancer :

> pgxn install oracle_fdw
INFO: best version: oracle_fdw 1.1.0
INFO: saving /tmp/tmpihaor2is/oracle_fdw-1.1.0.zip
INFO: unpacking: /tmp/tmpihaor2is/oracle_fdw-1.1.0.zip
INFO: building extension
gcc -O3 -O0 -Wall -Wmissing-prototypes -Wpointer-arith [...]
[...]
INFO: installing extension
/usr/bin/mkdir -p '/opt/postgres/lib'
/usr/bin/mkdir -p '/opt/postgres/share/extension'
/usr/bin/mkdir -p '/opt/postgres/share/extension'
/usr/bin/mkdir -p '/opt/postgres/share/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/opt/postgres/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 oracle_fdw.control '/opt/postgres/share/extension/'
/usr/bin/install -c -m 644 oracle_fdw--1.1.sql\oracle_fdw--1.0--1.1.sql
                            '/opt/postgres/share/extension/'
/usr/bin/install -c -m 644 README.oracle_fdw \
                            '/opt/postgres/share/doc/extension/'

Attention : le fait qu’un projet soit hébergé sur PGXN n’est absolument pas une validation de la part du projet PostgreSQL. De nombreux projets hébergés sur PGXN sont encore en phase de développement, voire abandonnés. Il faut avoir le même recul que pour n’importe quel autre brique libre.

Créer son extension

Pas si compliqué
Peut-être juste quelques fonctions SQL
Référence : documentation, Empaqueter des objets dans une extension
Exemples SQL et C : blog Dalibo

Conclusion

Un nombre toujours plus important d’extension pour étendre les possibilités de PostgreSQL
Un site central pour les extensions : PGXN.org
Rajoutez les vôtres !

Questions

N’hésitez pas, c’est le moment !

Travaux pratiques

Masquage statique de données avec PostgreSQL Anonymizer

But : Découverte de l’extension PostgreSQL Anonymizer et du masquage statique

Installer l’extension PostgreSQL Anonymizer en suivant la procédure décrite sur la page Installation de la documentation.

Créer une table customer :

CREATE TABLE customer (
    id SERIAL PRIMARY KEY,
    firstname TEXT,
    lastname TEXT,
    phone TEXT,
    birth DATE,
    postcode TEXT
);

Ajouter des individus dans la table :

INSERT INTO customer
VALUES
(107,'Sarah','Conor','060-911-0911', '1965-10-10', '90016'),
(258,'Luke', 'Skywalker', NULL, '1951-09-25', '90120'),
(341,'Don', 'Draper','347-515-3423', '1926-06-01', '04520')
;

Lire la documentation sur comment déclarer une règle de masquage et placer une règle pour générer un faux nom de famille sur la colonne lastname. Déclarer une règle de masquage statique sur la colonne lastname et l’appliquer. Vérifier le contenu de la table.

Réappliquer le masquage statique. Qu’observez-vous ?

Masquage dynamique de données avec PostgreSQL Anonymizer

Masquage statique de données avec PostgreSQL Anonymizer

Installer l’extension PostgreSQL Anonymizer en suivant la procédure décrite sur la page Installation de la documentation.

Sur Rocky Linux ou autre dérivé Red Hat, depuis les dépôts du PGDG :

sudo dnf install postgresql_anonymizer_14

Au besoin, remplacer 14 par la version de l’instance PostgreSQL.

La base de travail ici se nomme sensible. Se connecter à l’instance pour initialiser l’extension :

ALTER DATABASE sensible SET session_preload_libraries = 'anon' ;

Après reconnexion à la base sensible :

CREATE EXTENSION anon CASCADE;

SELECT anon.init();  -- ne pas oublier !

Créer une table customer :

CREATE TABLE customer (
    id SERIAL PRIMARY KEY,
    firstname TEXT,
    lastname TEXT,
    phone TEXT,
    birth DATE,
    postcode TEXT
);

Ajouter des individus dans la table :

INSERT INTO customer
VALUES
(107,'Sarah','Conor','060-911-0911', '1965-10-10', '90016'),
(258,'Luke', 'Skywalker', NULL, '1951-09-25', '90120'),
(341,'Don', 'Draper','347-515-3423', '1926-06-01', '04520')
;

SELECT * FROM customer ;

 id  | firstname | lastname  |    phone     |   birth    | postcode
-----+-----------+-----------+--------------+------------+----------
 107 | Sarah     | Conor     | 060-911-0911 | 1965-10-10 | 90016
 258 | Luke      | Skywalker |              | 1951-09-25 | 90120
 341 | Don       | Draper    | 347-515-3423 | 1926-06-01 | 04520

Lire la documentation sur comment déclarer une règle de masquage et placer une règle pour générer un faux nom de famille sur la colonne lastname. Déclarer une règle de masquage statique sur la colonne lastname et l’appliquer. Vérifier le contenu de la table.

SECURITY LABEL FOR anon ON COLUMN customer.lastname
IS 'MASKED WITH FUNCTION anon.fake_last_name()' ;

Si on consulte la table avec :

SELECT * FROM customer ;

les données ne sont pas encore masquées car la règle n’est pas appliquée. L’application se fait avec :

SELECT anon.anonymize_table('customer') ;

SELECT * FROM customer;

 id  | firstname | lastname |    phone     |   birth    | postcode
-----+-----------+----------+--------------+------------+----------
 107 | Sarah     | Waelchi  | 060-911-0911 | 1965-10-10 | 90016
 258 | Luke      | Lemke    |              | 1951-09-25 | 90120
 341 | Don       | Shanahan | 347-515-3423 | 1926-06-01 | 04520

NB : les données de la table ont ici bien été modifiées sur le disque.

Réappliquer le masquage statique. Qu’observez-vous ?

Si l’on relance l’anonymisation plusieurs fois, les données factices vont changer car la fonction fake_last_name() renvoie des valeurs différentes à chaque appel.

SELECT anon.anonymize_table('customer');

SELECT * FROM customer;

 id  | firstname | lastname |    phone     |   birth    | postcode
-----+-----------+----------+--------------+------------+----------
 107 | Sarah     | Smith    | 060-911-0911 | 1965-10-10 | 90016
 258 | Luke      | Sanford  |              | 1951-09-25 | 90120
 341 | Don       | Goldner  | 347-515-3423 | 1926-06-01 | 04520

Masquage dynamique de données avec PostgreSQL Anonymizer

Parcourir la liste des fonctions de masquage et écrire une règle pour cacher partiellement le numéro de téléphone. Activer le masquage dynamique. Appliquer le masquage dynamique uniquement sur la colonne phone pour un nouvel utilisateur nommé soustraitant.

SELECT anon.start_dynamic_masking();

SECURITY LABEL FOR anon ON COLUMN customer.phone
IS 'MASKED WITH FUNCTION anon.partial(phone,2,$$X-XXX-XX$$,2)';

SELECT anon.anonymize_column('customer','phone');

SELECT * FROM customer ;

Les numéros de téléphone apparaissent encore car ils ne sont pas masqués à l’utilisateur en cours. Il faut le déclarer pour les utilisateurs concernés :

CREATE ROLE soustraitant LOGIN ;
\password soustraitant

GRANT SELECT ON customer TO soustraitant ;
SECURITY LABEL FOR anon ON ROLE soustraitant  IS 'MASKED';

Ce nouvel utilisateur verra à chaque fois des noms différents (masquage dynamique), et des numéros de téléphone partiellement masqués :

\c sensible soustraitant
SELECT * FROM customer ;

 id  | firstname | lastname |    phone     |   birth    | postcode
-----+-----------+----------+--------------+------------+----------
 107 | Sarah     | Kovacek  | 06X-XXX-XX11 | 1965-10-10 | 90016
 258 | Luke      | Effertz  | ø            | 1951-09-25 | 90120
 341 | Don       | Turcotte | 34X-XXX-XX23 | 1926-06-01 | 04520

Pour consulter la configuration de masquage en place, utiliser une des vues fournies dans le schéma anon :

=# SELECT * FROM anon.pg_masks \gx

-[ RECORD 1 ]----+----------------------------------------------------------
attrelid         | 41853
attnum           | 3
relnamespace     | public
relname          | customer
attname          | lastname
format_type      | text
col_description  | MASKED WITH FUNCTION anon.fake_last_name()
masking_function | anon.fake_last_name()
masking_value    |
priority         | 100
masking_filter   | anon.fake_last_name()
trusted_schema   | t
-[ RECORD 2 ]----+----------------------------------------------------------
attrelid         | 41853
attnum           | 4
relnamespace     | public
relname          | customer
attname          | phone
format_type      | text
col_description  | MASKED WITH FUNCTION anon.partial(phone,2,$$X-XXX-XX$$,2)
masking_function | anon.partial(phone,2,$$X-XXX-XX$$,2)
masking_value    |
priority         | 100
masking_filter   | anon.partial(phone,2,$$X-XXX-XX$$,2)
trusted_schema   | t