La commande CREATE EXTENSION repose sur un fichier de contrôle associé à chaque extension. Ce fichier doit avoir le même nom que l'extension suivi du suffixe .control, et doit être placé dans le sous-répertoire SHAREDIR/extension du répertoire d'installation. Il doit être accompagné d'au moins un fichier de script SQL dont le nom doit répondre à la syntaxe extension--version.sql (par exemple, foo--1.0.sql pour la version 1.0 de l'extension foo). Par défaut, les fichiers de script sont eux-aussi situés dans le répertoire SHAREDIR/extension. Le fichier de contrôle peut toutefois spécifier un répertoire différent pour chaque fichier de script.

Le format du fichier de contrôle d'une extension est le même que pour le fichier postgresql.conf, à savoir une liste d'affectation nom_paramètre = valeur avec un maximum d'une affectation par ligne. Les lignes vides et les commentaires introduits par # sont eux-aussi autorisés. Prenez garde à placer entre guillemets les valeurs qui ne sont ni des nombres ni des mots isolés.

Un fichier de contrôle peut définir les paramètres suivants :

En complément au fichier de contrôle extension.control, une extension peut disposer de fichiers de contrôle secondaires pour chaque version dont le nommage correspond à extension--version.control. Ces fichiers doivent se trouver dans le répertoire des fichiers de script de l'extension. Les fichiers de contrôle secondaires suivent le même format que le fichier de contrôle principal. Tout paramètre spécifié dans un fichier de contrôle secondaire surcharge la valeur spécifiée dans le fichier de contrôle principal concernant les installations ou mises à jour à la version considérée. Cependant, il n'est pas possible de spécifier les paramètres directory et default_version dans un fichier de contrôle secondaire.

Un fichier de script SQL d'une extension peut contenir toute commande SQL, à l'exception des commandes de contrôle de transaction (BEGIN, COMMIT, etc), et des commandes qui ne peuvent être exécutées au sein d'un bloc transactionnel (comme la commande VACUUM). Cette contrainte est liée au fait que les fichiers de script sont implicitement exécutés dans une transaction.

Les scripts SQL d'une extension peuvent aussi contenir des lignes commençant par \echo, qui seront ignorées (traitées comme des commentaires) par le mécanisme d'extension. Ceci est souvent utilisé pour renvoyer une erreur si le script est passé à psql plutôt qu'exécuter par CREATE EXTENSION (voir un script d'exemple dans Section 36.17.7). Sans cela, les utilisateurs pourraient charger accidentellement le contenu de l'extension sous la forme d'objets « autonomes » plutôt que faisant partie d'une extension, ce qui est assez pénible à corriger.

Si le script d'extension contient la chaine @extowner@, celle-ci est remplacé avec le nom (encadré de double apostrophe si nécessaire) de l'utilisateur exécutant CREATE EXTENSION ou ALTER EXTENSION. Habituellement, cette fonctionnalité est employée par les extensions notées de confiance pour affecter l'appropriation des objets sélectionnés par l'utilisateur exécutant plutôt que par le bootstrap superuser. (Il faut cependant être prudent avec cela. Par exemple, affecter à une fonction en langage C un propriétaire non superutilisateur ouvre une voie pour une escalade de privilèges à cet utilisateur.)

Bien que les fichiers de script puissent contenir n'importe quel caractère autorisé par l'encodage spécifié, les fichiers de contrôle ne peuvent contenir que des caractères ASCII non formatés. En effet, PostgreSQL ne peut pas déterminer l'encodage utilisé par les fichiers de contrôle. Dans la pratique, cela ne pose problème que dans le cas où vous voudriez utiliser des caractères non ASCII dans le commentaire de l'extension. Dans ce cas de figure, il est recommandé de ne pas utiliser le paramètre comment du fichier de contrôle pour définir ce commentaire, mais plutôt la commande COMMENT ON EXTENSION dans un fichier de script.

Les utilisateurs souhaitent souvent charger les objets d'une extension dans un schéma différent de celui imposé par l'auteur. Trois niveaux de déplacement sont supportés :

Dans tous les cas, le fichier de script sera exécuté avec comme valeur de search_path le schéma cible. Cela signifie que la commande CREATE EXTENSION réalisera l'équivalent de la commande suivante :

SET LOCAL search_path TO @extschema@, pg_temp;
    

Cela permettra aux objets du fichier de script d'être créés dans le schéma cible. Le fichier de script peut toutefois modifier la valeur de search_path si nécessaire, mais cela n'est généralement pas le comportement souhaité. La variable search_path retrouvera sa valeur initiale à la fin de l'exécution de la commande CREATE EXTENSION.

Le schéma cible est déterminé par le paramètre schema dans le fichier de contrôle s'il est précisé, sinon par l'option SCHEMA de la commande CREATE EXTENSION si elle est spécifiée, sinon par le schéma de création par défaut actuel (le premier rencontré en suivant le chemin de recherche search_path de l'appelant). Quand le paramètre schema du fichier de contrôle est utilisé, le schéma cible sera créé s'il n'existe pas encore. Dans les autres cas, il devra exister au préalable.

Si des extensions requises sont définies par requires dans le fichier de contrôle, leur schéma cible est ajouté à la valeur initiale de search_path, d'après le schéma cible de la nouvelle extension. Cela permet à leurs objets d'être visibles dans le fichier de script de l'extension installée.

Pour des raisons de sécurité, pg_temp est ajouté automatiquement à la fin de search_path dans tous les cas.

Une extension peut contenir des objets répartis dans plusieurs schémas. Il est alors conseillé de regrouper dans un unique schéma l'ensemble des objets destinés à un usage externe à l'extension, qui sera alors le schéma cible de l'extension. Une telle organisation est compatible avec la définition par défaut de search_path pour la création d'extensions qui en seront dépendantes.

Si une extension référence des objets appartenant à une autre extension, il est recommandé de qualifier ces références avec le nom du schéma. Pour faire cela, écrivez @extschema:nom@ dans le script de l'extension, où nom est le nom de l'autre extension (qui doit être listé dans la liste requires de cette extension). Cette chaîne sera remplacée par le nom (avec guillemets doubles si nécessaire) du schéma cible de cette extension. Bien que cette notation évite le besoin de coder en dur le nom supposé des schémas dans le script de l'extension, son utilisation peut intégrer le nom du schéma de l'autre extension dans les objets installés de cette extension. (Typiquement, ceci survient quand @extschema:name@ est utilisé dans une chaîne constante, telle que le corps d'une fonction ou dans le paramétrage du search_path. Dans d'autres cas, la référence de l'objet est réduite à son OID lors de l'analyse, et ne requiert pas de recherche ultérieure.) Si le nom du schéma de l'autre extension est très embarquée, vous devez empêcher l'autre extension d'être changée de schéma après que la votre ne soit installée, en ajoutant le nom de l'autre extension dans votre liste no_relocate.

Certaines extensions incluent des tables de configuration, contenant des données qui peuvent être ajoutées ou changées par l'utilisateur après l'installation de l'extension. Normalement, si la table fait partie de l'extension, ni la définition de la table, ni son contenu ne sera sauvegardé par pg_dump. Mais ce comportement n'est pas celui attendu pour une table de configuration. Les données modifiées par un utilisateur nécessitent d'être sauvegardées, ou l'extension aura un comportement différent après rechargement.

Pour résoudre ce problème, un fichier de script d'extension peut marquer une table ou une séquence comme étant une relation de configuration, ce qui indiquera à pg_dump d'inclure le contenu de la table ou de la séquence (et non sa définition) dans la sauvegarde. Pour cela, il s'agit d'appeler la fonction pg_extension_config_dump(regclass, text) après avoir créé la table ou la séquence, par exemple

CREATE TABLE my_config (key text, value text);
CREATE SEQUENCE my_config_seq;

SELECT pg_catalog.pg_extension_config_dump('my_config', '');
SELECT pg_catalog.pg_extension_config_dump('my_config_seq', '');
    

Cette fonction permet de marquer autant de tables ou de séquences que nécessaire. Les séquences associées avec des colonnes de type serial ou bigserial peuvent être marquées ainsi.

Si le second argument de pg_extension_config_dump est une chaîne vide, le contenu entier de la table sera sauvegardé par l'application pg_dump. Cela n'est correct que si la table était initialement vide après l'installation du script. Si un mélange de données initiales et de données ajoutées par l'utilisateur est présent dans la table, le second argument de pg_extension_config_dump permet de spécifier une condition WHERE qui sélectionne les données à sauvegarder. Par exemple, vous pourriez faire

CREATE TABLE my_config (key text, value text, standard_entry boolean);

SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');
    

et vous assurer que la valeur de standard_entry soit true uniquement lorsque les lignes ont été créées par le script de l'extension.

Pour les séquences, le deuxième argument de pg_extension_config_dump n'a pas d'effet.

Des situations plus compliquées, comme des données initiales qui peuvent être modifiées par l'utilisateur, peuvent être prises en charge en créant des triggers sur la table de configuration pour s'assurer que les lignes ont été marquées correctement.

Vous pouvez modifier la condition du filtre associé avec une table de configuration en appelant de nouveau pg_extension_config_dump. (Ceci serait typiquement utile dans un script de mise à jour d'extension.) La seule façon de marquer une table est de la dissocier de l'extension avec la commande ALTER EXTENSION ... DROP TABLE.

Notez que les relations de clés étrangères entre ces tables dicteront l'ordre dans lequel les tables seront sauvegardées par pg_dump. Plus spécifiquement, pg_dump tentera de sauvegarder en premier la table référencé, puis la table référante. Comme les relations de clés étrangères sont configurées lors du CREATE EXTENSION (avant que les données ne soient chargées dans les tables), les dépendances circulaires ne sont pas gérées. Quand des dépendances circulaires existent, les données seront toujours sauvegardées mais ne seront pas restaurables directement. Une intervention de l'utilisateur sera nécessaire.

Les séquences associées avec des colonnes de type serial ou bigserial doivent être directement marquées pour sauvegarder leur état. Marquer la relation parent n'est pas suffisant pour ça.

Un des avantages du mécanisme d'extension est de proposer un moyen simple de gérer la mise à jour des commandes SQL qui définissent les objets de l'extension. Cela est rendu possible par l'association d'un nom ou d'un numéro de version à chaque nouvelle version du script d'installation de l'extension. En complément, si vous voulez qu'un utilisateur soit capable de mettre à jour sa base de données dynamiquement d'une version à une autre, vous pouvez fournir des scripts de mise à jour qui feront les modifications nécessaires. Les scripts de mise à jour ont un nom qui correspond au format extension--ancienne_version--nouvelle_version.sql (par exemple, foo--1.0--1.1.sql contient les commandes pour modifier la version 1.0 de l'extension foo en la version 1.1).

En admettant qu'un tel script de mise à jour soit disponible, la commande ALTER EXTENSION UPDATE mettra à jour une extension installée vers la nouvelle version spécifiée. Le script de mise à jour est exécuté dans le même environnement que celui que la commande CREATE EXTENSION fournit pour l'installation de scripts : en particulier, la variable search_path est définie de la même façon et tout nouvel objet créé par le script est automatiquement ajouté à l'extension. De plus, si le script choisit de supprimer des objets membres de l'extension, ils sont automatiquement dissociés de l'extension.

Si une extension a un fichier de contrôle secondaire, les paramètres de contrôle qui sont utilisés par un script de mise à jour sont ceux définis par le script de la version cible.

La commande ALTER EXTENSION peut exécuter des mises à jour en séquence pour réussir une mise à jour. Par exemple, si seuls les fichiers foo--1.0--1.1.sql et foo--1.1--2.0.sql sont disponibles, la commande ALTER EXTENSION les exécutera séquentiellement si une mise à jour vers la version 2.0 est demandée alors que la version 1.0 est installée.

PostgreSQL ne suppose rien au sujet des noms de version. Par exemple, il ne sait pas si 1.1 suit 1.0. Il effectue juste une correspondance entre les noms de version et suit un chemin qui nécessite d'appliquer le moins de fichier de script possible. Un nom de version peut en réalité être toute chaîne qui ne contiendrait pas -- ou qui ne commencerait ou ne finirait pas par -.

Il peut parfois être utile de fournir des scripts de retour en arrière, comme par exemple foo--1.1--1.0.sql pour autoriser d'inverser les modifications effectuées par la mise à jour en version 1.1. Si vous procédez ainsi, ayez conscience de la possibilité laissée à PostgreSQL d'exécuter un tel script de retour en arrière s'il permet d'atteindre la version cible d'une mise à jour en un nombre réduit d'étapes. La cause du risque se trouve dans les scripts de mise à jour optimisés permettant de passer plusieurs versions en un seul script. La longueur du chemin commençant par un retour en arrière suivi d'un script optimisé pourrait être inférieure à la longueur du chemin qui monterait de version une par une. Si le script de retour en arrière supprime un objet irremplaçable, les conséquences pourraient en être facheuses.

Pour vérifier que vous ne serez pas confronté à des chemins de mise à jour inattendus, utilisez cette commande :

SELECT * FROM pg_extension_update_paths('extension_name');
    

Cette commande permet d'afficher chaque paire de noms de version connues pour l'extension spécifiée, ainsi que le chemin de mise à jour qui serait suivi depuis la version de départ jusque la version cible, ou la valeur NULL si aucun chemin valable n'est disponible. Le chemin est affiché sous une forme textuelle avec des séparateurs --. Vous pouvez utiliser regexp_split_to_array(path,'--') si vous préférez le format tableau.

Une extension qui a existé un certain temps existera probabement dans plusieurs version, pour lesquelles l'auteur devra écrire des scripts de mise à jour. Par exemple, si vous avez sorti une extension foo dans les versions 1.0, 1.1, et 1.2, il devrait exister les scripts de mise à jour foo--1.0--1.1.sql et foo--1.1--1.2.sql. Avant PostgreSQL 10, il était nécessaire de créer également de nouveaux fichiers de scripts foo--1.1.sql et foo--1.2.sql qui construisent directement les nouvelles versions de l'extension, ou sinon les nouvelles version ne pourraient pas être installées directement, mais uniquement en installant 1.0 puis en effectuant les mises à jour. C'était fastidieux et source de doublons, mais c'est maintenant inutile car CREATE EXTENSION peut suivre les chaînes de mise à jour automatiquement. Par exemple, si seuls les fichiers de script foo--1.0.sql, foo--1.0--1.1.sql, et foo--1.1--1.2.sql sont disponibles, alors une demande d'installation de la version 1.2 pourra être effectuée en lançant ces trois scripts les uns à la suite des autres. Le traitement est le même que si vous aviez d'abord installé 1.0 puis mis à jour vers 1.2. (Comme pour ALTER EXTENSION UPDATE, si de multiples chemins sont disponibles alors le plus court sera choisi.) Arranger les fichiers de script d'une extension de cette façon peut réduire la quantité nécessaire d'effort de maintenance à fournir pour produires de petites mises à jour.

Si vous utilisez des fichiers de contrôle secondaires (spécifique à la version) avec une extension maintenant de cette façon, gardez à l'esprit que chaque version nécessite un fichier de contrôle même s'il n'y a pas de script d'installation spécifique pour cette version, car ce fichier de contrôle déterminera comment une mise à jour implicite vers cette version est effectuée. Par exemple, si foo--1.0.control spécifie requires = 'bar' mais que l'autre fichier de contrôle de foo ne le spécifie pas, la dépendance sur l'extension bar sera supprimée lors de la mise à jour de 1.0 vers une autre version.

Les extensions largement distribuées devraient assumer peu sur la base qu'elles occupent. De ce fait, il est adéquat d'écrire des fonctions fournies par une extension dans un style sécurisé qui ne peut pas être compromis par des attaques basées sur le search_path.

Une extension qui dispose de la propriété superuser configurée à true doit aussi considérer les risques de sécurité pour les actions effectuées par ses scripts d'installation et de mise à jour. Il n'est pas particulièrement compliqué pour un utilisateur mal intentionné de créer des objets chevaux de Troie qui compromettront une exécution ultérieure d'un script d'extension mal écrit, permettant à son utilisateur de gagner les droits d'un superutilisateur.

Des conseils sur l'écriture de fonctions sécurisées sont donnés dans Section 36.17.6.1 ci-dessous, et d'autres conseils, sur l'écriture de scripts d'installation sécurisés, sont donnés dans Section 36.17.6.2.

Ci-après, un exemple complet d'une extension écrite uniquement en SQL, un type composite de deux éléments qui peut stocker n'importe quelle valeur dans chaque emplacement, qui sont nommés « k » et « v ». Les valeurs non textuelles sont automatiquement changées en texte avant stockage.

Le fichier de script pair--1.0.sql ressemble à ceci:

-- se plaint si le script est exécuté directement dans psql, plutôt que via CREATE EXTENSION
\echo Use "CREATE EXTENSION pair" to load this file. \quit

CREATE TYPE pair AS ( k text, v text );

CREATE pair(text, text)
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;';

CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);

-- "SET search_path" is easy to get right, but qualified names perform better.
CREATE FUNCTION lower(pair)
RETURNS pair LANGUAGE SQL
AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;'
SET search_path = pg_temp;

CREATE FUNCTION pair_concat(pair, pair)
RETURNS pair LANGUAGE SQL
AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,
               $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';

    

Le fichier de contrôle pair.control ressemble à ceci:

# extension pair
comment = 'Un type de donnees representant un couple clef/valeur'
default_version = '1.0'
# n'est pas déplaçable à cause de l'utilisation de @extschema@
relocatable = false
    

Si vous avez besoin d'un fichier d'installation pour installer ces deux fichiers dans le bon répertoire, vous pouvez utiliser le fichier Makefile qui suit :

EXTENSION = pair
DATA = pair--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
    

Ce fichier d'installation s'appuye sur PGXS, qui est décrit dans Section 36.18. La commande make install va installer les fichiers de contrôle et de script dans le répertoire adéquat tel qu'indiqué par pg_config.

Une fois les fichiers installés, utilisez la commande CREATE EXTENSION pour charger les objets dans une base de donnée.