Découverte des fonctionnalités

17 avril 2024

Dalibo SCOP

Sur ce document

Formation Module A2
Titre Découverte des fonctionnalités
Révision 24.04
PDF https://dali.bo/a2_pdf
EPUB https://dali.bo/a2_epub
HTML https://dali.bo/a2_html
Slides https://dali.bo/a2_slides

Vous trouverez en ligne les différentes versions complètes de ce document.


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 !

À 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.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

Découverte des fonctionnalités

PostgreSQL

Au menu

  • Fonctionnalités du moteur

  • Objets SQL

  • Connaître les différentes fonctionnalités et possibilités

  • Découvrir des exemples concrets

Ce module propose un tour rapide des fonctionnalités principales du moteur : ACID, MVCC, transactions, journaux de transactions… ainsi que des objets SQL gérés (schémas, index, tablespaces, triggers…). Ce rappel des concepts de base permet d’avancer plus facilement lors des modules suivants.


Fonctionnalités du moteur

  • Standard SQL
  • ACID : la gestion transactionnelle
  • Niveaux d’isolation
  • Journaux de transactions
  • Administration
  • Sauvegardes
  • Réplication
  • Supervision
  • Sécurité
  • Extensibilité

Cette partie couvre les différentes fonctionnalités d’un moteur de bases de données. Il ne s’agit pas d’aller dans le détail de chacune, mais de donner une idée de ce qui est disponible. Les modules suivants de cette formation et des autres formations détaillent certaines de ces fonctionnalités.


Respect du standard SQL

  • Excellent support du SQL ISO
  • Objets SQL
    • tables, vues, séquences, routines, triggers
  • Opérations
    • jointures, sous-requêtes, requêtes CTE, requêtes de fenêtrage, etc.

La dernière version du standard SQL est SQL:2023. À ce jour, aucun SGBD ne la supporte complètement, mais :

  • PostgreSQL progresse et s’en approche au maximum, au fil des versions ;
  • la majorité de la norme est supportée, parfois avec des syntaxes différentes ;
  • PostgreSQL est le SGDB le plus respectueux du standard.

ACID

Gestion transactionnelle : la force des bases de données relationnelles :

  • Atomicité (Atomic)
  • Cohérence (Consistent)
  • Isolation (Isolated)
  • Durabilité (Durable)

Les propriétés ACID sont le fondement même de toute bonne base de données. Il s’agit de l’acronyme des quatre règles que toute transaction (c’est-à-dire une suite d’ordres modifiant les données) doit respecter :

  • A : Une transaction est appliquée en « tout ou rien ».
  • C : Une transaction amène la base d’un état stable à un autre.
  • I : Les transactions n’agissent pas les unes sur les autres.
  • D : Une transaction validée sera conservée de manière permanente.

Les bases de données relationnelles les plus courantes depuis des décennies (PostgreSQL bien sûr, mais aussi Oracle, MySQL, SQL Server, SQLite…) se basent sur ces principes, même si elles font chacune des compromis différents suivant leurs cas d’usage, les compromis acceptés à chaque époque avec la performance et les versions.

Atomicité :

Une transaction doit être exécutée entièrement ou pas du tout, et surtout pas partiellement, même si elle est longue et complexe, même en cas d’incident majeur sur la base de données. L’exemple basique est une transaction bancaire : le montant d’un virement doit être sur un compte ou un autre, et en cas de problème ne pas disparaître ou apparaître en double. Ce principe garantit que les données modifiées par des transactions valides seront toujours visibles dans un état stable, et évite nombre de problèmes fonctionnels comme techniques.

Cohérence :

Un état cohérent respecte les règles de validité définies dans le modèle, c’est-à-dire les contraintes définies dans le modèle : types, plages de valeurs admissibles, unicité, liens entre tables (clés étrangères), etc. Le non-respect de ces règles par l’applicatif entraîne une erreur et un rejet de la transaction.

Isolation :

Des transactions simultanées doivent agir comme si elles étaient seules sur la base. Surtout, elles ne voient pas les données non validées des autres transactions. Ainsi une transaction peut travailler sur un état stable et fixe, et durer assez longtemps sans risque de gêner les autres transactions.

Il existe plusieurs « niveaux d’isolation » pour définir précisément le comportement en cas de lectures ou écritures simultanées sur les mêmes données et pour arbitrer avec les contraintes de performances ; le niveau le plus contraignant exige que tout se passe comme si toutes les transactions se déroulaient successivement.

Durabilité :

Une fois une transaction validée par le serveur (typiquement : COMMIT ne retourne pas d’erreur, ce qui valide la cohérence et l’enregistrement physique), l’utilisateur doit avoir la garantie que la donnée ne sera pas perdue ; du moins jusqu’à ce qu’il décide de la modifier à nouveau. Cette garantie doit valoir même en cas d’événément catastrophique : plantage de la base, perte d’un disque… C’est donc au serveur de s’assurer autant que possible que les différents éléments (disque, système d’exploitation…) ont bien rempli leur office. C’est à l’humain d’arbitrer entre le niveau de criticité requis et les contraintes de performances et de ressources adéquates (et fiables) à fournir à la base de données.

NoSQL :

À l’inverse, les outils de la mouvance (« NoSQL », par exemple MongoDB ou Cassandra), ne fournissent pas les garanties ACID. C’est le cas de la plupart des bases non-relationnelles, qui reprennent le modèle BASE (Basically Available, Soft State, Eventually Consistent, soit succintement : disponibilité d’abord ; incohérence possible entre les réplicas ; cohérence… à terme, après un délai). Un intérêt est de débarasser le développeur de certaines lourdeurs apparentes liées à la modélisation assez stricte d’une base de données relationnelle. Cependant, la plupart des applications ont d’abord besoin des garanties de sécurité et cohérence qu’offrent un moteur transactionnel classique, et la décision d’utiliser un système ne les garantissant pas ne doit pas être prise à la légère ; sans parler d’autres critères comme la fragmentation du domaine par rapport au monde relationnel et son SQL (à peu près) standardisé. Avec le temps, les moteurs transactionnels ont acquis des fonctionnalités qui faisaient l’intérêt des bases NoSQL (en premier lieu la facilité de réplication et le stockage de JSON), et ces dernières ont tenté d’intégrer un peu plus de sécurité dans leur modèle.


MVCC

  • MultiVersion Concurrency Control
  • Le « noyau » de PostgreSQL
  • Garantit les propriétés ACID
  • Permet les accès concurrents sur la même table
    • une lecture ne bloque pas une écriture
    • une écriture ne bloque pas une lecture
    • une écriture ne bloque pas les autres écritures…
    • …sauf pour la mise à jour de la même ligne

MVCC (Multi Version Concurrency Control) est le mécanisme interne de PostgreSQL utilisé pour garantir la cohérence des données lorsque plusieurs processus accèdent simultanément à la même table.

MVCC maintient toutes les versions nécessaires de chaque ligne, ainsi chaque transaction voit une image figée de la base (appelée snapshot). Cette image correspond à l’état de la base lors du démarrage de la requête ou de la transaction, suivant le niveau d’isolation demandé par l’utilisateur à PostgreSQL pour la transaction.

MVCC fluidifie les mises à jour en évitant les blocages trop contraignants (verrous sur UPDATE) entre sessions et par conséquent de meilleures performances en contexte transactionnel.

C’est notamment MVCC qui permet d’exporter facilement une base à chaud et d’obtenir un export cohérent alors même que plusieurs utilisateurs sont potentiellement en train de modifier des données dans la base.

C’est la qualité de l’implémentation de ce système qui fait de PostgreSQL un des meilleurs SGBD au monde : chaque transaction travaille dans son image de la base, cohérent du début à la fin de ses opérations. Par ailleurs, les écrivains ne bloquent pas les lecteurs et les lecteurs ne bloquent pas les écrivains, contrairement aux SGBD s’appuyant sur des verrous de lignes. Cela assure de meilleures performances, moins de contention et un fonctionnement plus fluide des outils s’appuyant sur PostgreSQL.


Transactions

  • Une transaction = ensemble atomique d’opérations
  • « Tout ou rien »
  • BEGIN obligatoire pour grouper des modifications
  • COMMIT pour valider
    • y compris le DDL
  • Perte des modifications si :
    • ROLLBACK / perte de la connexion / arrêt (brutal ou non) du serveur
  • SAVEPOINT pour sauvegarde des modifications d’une transaction à un instant t
  • Pas de transactions imbriquées

L’exemple habituel et très connu des transactions est celui du virement d’une somme d’argent du compte de Bob vers le compte d’Alice. Le total du compte de Bob ne doit pas montrer qu’il a été débité de X euros tant que le compte d’Alice n’a pas été crédité de X euros. Nous souhaitons en fait que les deux opérations apparaissent aux yeux du reste du système comme une seule opération unitaire. D’où l’emploi d’une transaction explicite. En voici un exemple :

BEGIN;
UPDATE comptes SET solde=solde-200 WHERE proprietaire='Bob';
UPDATE comptes SET solde=solde+200 WHERE proprietaire='Alice';
COMMIT;

Contrairement à d’autres moteurs de bases de données, PostgreSQL accepte aussi les instructions DDL dans une transaction. En voici un exemple :

BEGIN;
CREATE TABLE capitaines (id serial, nom text, age integer);
INSERT INTO capitaines VALUES (1, 'Haddock', 35);
SELECT age FROM capitaines;
 age
---
  35
ROLLBACK;
SELECT age FROM capitaines;
ERROR:  relation "capitaines" does not exist
LINE 1: SELECT age FROM capitaines;
                        ^

Nous voyons que la table capitaines a existé à l’intérieur de la transaction. Mais puisque cette transaction a été annulée (ROLLBACK), la table n’a pas été créée au final.

Cela montre aussi le support du DDL transactionnel au sein de PostgreSQL : PostgreSQL n’effectue aucun COMMIT implicite sur des ordres DDL tels que CREATE TABLE, DROP TABLE ou TRUNCATE TABLE. De ce fait, ces ordres peuvent être annulés au sein d’une transaction.

Un point de sauvegarde est une marque spéciale à l’intérieur d’une transaction qui autorise l’annulation de toutes les commandes exécutées après son établissement, restaurant la transaction dans l’état où elle était au moment de l’établissement du point de sauvegarde.

BEGIN;
CREATE TABLE capitaines (id serial, nom text, age integer);
INSERT INTO capitaines VALUES (1, 'Haddock', 35);
SAVEPOINT insert_sp;
UPDATE capitaines SET age = 45 WHERE nom = 'Haddock';
ROLLBACK TO SAVEPOINT insert_sp;
COMMIT;
SELECT age FROM capitaines WHERE nom = 'Haddock';
 age
---
  35

Malgré le COMMIT après l’UPDATE, la mise à jour n’est pas prise en compte. En effet, le ROLLBACK TO SAVEPOINT a permis d’annuler cet UPDATE mais pas les opérations précédant le SAVEPOINT.

À partir de la version 12, il est possible de chaîner les transactions avec COMMIT AND CHAIN ou ROLLBACK AND CHAIN. Cela veut dire terminer une transaction et en démarrer une autre immédiatement après avec les mêmes propriétés (par exemple, le niveau d’isolation).


Niveaux d’isolation

  • Chaque transaction (et donc session) est isolée à un certain point
    • elle ne voit pas les opérations des autres
    • elle s’exécute indépendamment des autres
  • Nous pouvons spécifier le niveau d’isolation au démarrage d’une transaction
    • BEGIN ISOLATION LEVEL xxx;
  • Niveaux d’isolation supportés
    • read commited (défaut)
    • repeatable read
    • serializable

Chaque transaction, en plus d’être atomique, s’exécute séparément des autres. Le niveau de séparation demandé sera un compromis entre le besoin applicatif (pouvoir ignorer sans risque ce que font les autres transactions) et les contraintes imposées au niveau de PostgreSQL (performances, risque d’échec d’une transaction).

Le standard SQL spécifie quatre niveaux, mais PostgreSQL n’en supporte que trois (il n’y a pas de read uncommitted : les lignes non encore committées par les autres transactions sont toujours invisibles).


Fiabilité : journaux de transactions

  • Write Ahead Logs (WAL)
  • Chaque donnée est écrite 2 fois sur le disque !
  • Sécurité quasiment infaillible
  • Avantages :
    • WAL : écriture séquentielle
    • un seul sync sur le WAL
    • fichiers de données : en asynchrone
    • sauvegarde PITR et de la réplication fiables

Les journaux de transactions (appelés souvent WAL, autrefois XLOG) sont une garantie contre les pertes de données.

Il s’agit d’une technique standard de journalisation appliquée à toutes les transactions. Ainsi lors d’une modification de donnée, l’écriture au niveau du disque se fait en deux temps :

  • écriture immédiate dans le journal de transactions ;
  • écriture dans le fichier de données, plus tard, lors du prochain checkpoint.

Ainsi en cas de crash :

  • PostgreSQL redémarre ;
  • PostgreSQL vérifie s’il reste des données non intégrées aux fichiers de données dans les journaux (mode recovery) ;
  • si c’est le cas, ces données sont recopiées dans les fichiers de données afin de retrouver un état stable et cohérent.

Plus d’informations, lire cet article.

Les écritures dans le journal se font de façon séquentielle, donc sans grand déplacement de la tête d’écriture (sur un disque dur classique, c’est l’opération la plus coûteuse).

De plus, comme nous n’écrivons que dans un seul fichier de transactions, la synchronisation sur disque peut se faire sur ce seul fichier, si le système de fichiers le supporte.

L’écriture définitive dans les fichiers de données, asynchrone et généralement de manière lissée, permet là aussi de gagner du temps.

Mais les performances ne sont pas la seule raison des journaux de transactions. Ces journaux ont aussi permis l’apparition de nouvelles fonctionnalités très intéressantes, comme le PITR et la réplication physique, basés sur le rejeu des informations stockées dans ces journaux.


Sauvegardes

  • Sauvegarde des fichiers à froid
    • outils système
  • Import/Export logique
    • pg_dump, pg_dumpall, pg_restore
  • Sauvegarde physique à chaud
    • pg_basebackup
    • sauvegarde PITR

PostgreSQL supporte différentes solutions pour la sauvegarde.

La plus simple revient à sauvegarder à froid tous les fichiers des différents répertoires de données mais cela nécessite d’arrêter le serveur, ce qui occasionne une mise hors production plus ou moins longue, suivant la volumétrie à sauvegarder.

L’export logique se fait avec le serveur démarré. Plusieurs outils sont proposés : pg_dump pour sauvegarder une base, pg_dumpall pour sauvegarder toutes les bases. Suivant le format de l’export, l’import se fera avec les outils psql ou pg_restore. Les sauvegardes se font à chaud et sont cohérentes sans blocage de l’activité (seuls la suppression des tables et le changement de leur définition sont interdits).

Enfin, il est possible de sauvegarder les fichiers à chaud. Cela nécessite de mettre en place l’archivage des journaux de transactions. L’outil pg_basebackup est conseillé pour ce type de sauvegarde.

Il est à noter qu’il existe un grand nombre d’outils développés par la communauté pour faciliter encore plus la gestion des sauvegardes avec des fonctionnalités avancées comme le PITR (Point In Time Recovery) ou la gestion de la rétention, notamment pg_back (sauvegarde logique), pgBackRest ou barman (sauvegarde physique).


Réplication

  • Réplication physique
    • instance complète
    • même architecture
  • Réplication logique (PG 10+)
    • table par table / colonne par colonne avec ou sans filtre (PG 15)
    • voire opération par opération
  • Asynchrones ou synchrone
  • Asymétriques

PostgreSQL dispose de la réplication depuis de nombreuses années.

Le premier type de réplication intégrée est la réplication physique. Il n’y a pas de granularité, c’est forcément l’instance complète (toutes les bases de données), et au niveau des fichiers de données. Cette réplication est asymétrique : un seul serveur primaire effectue lectures comme écritures, et les serveurs secondaires n’acceptent que des lectures.

Le deuxième type de réplication est bien plus récent vu qu’il a été ajouté en version 10. Il s’agit d’une réplication logique, où les données elles-mêmes sont répliquées. Cette réplication est elle aussi asymétrique. Cependant, ceci se configure table par table (et non pas au niveau de l’instance comme pour la réplication physique). Avec la version 15, il devient possible de choisir quelles colonnes sont publiées et de filtre les lignes à publier.

La réplication logique n’est pas intéressante quand nous voulons un serveur sur lequel basculer en cas de problème sur le primaire. Dans ce cas, il vaut mieux utiliser la réplication physique. Par contre, c’est le bon type de réplication pour une réplication partielle ou pour une mise à jour de version majeure.

Dans les deux cas, les modifications sont transmises en asynchrone (avec un délai possible). Il est cependant possible de la configurer en synchrone pour tous les serveurs ou seulement certains.


Extensibilité

  • Extensions
    • CREATE EXTENSION monextension ;
    • nombreuses : contrib, packagées… selon provenance
    • notion de confiance (v13+)
    • dont langages de procédures stockées !
  • Système des hooks
  • Background workers

Faute de pouvoir intégrer toutes les fonctionnalités demandées dans PostgreSQL, ses développeurs se sont attachés à permettre à l’utilisateur d’étendre lui-même les fonctionnalités sans avoir à modifier le code principal.

Ils ont donc ajouté la possibilité de créer des extensions. Une extension contient un ensemble de types de données, de fonctions, d’opérateurs, etc. en un seul objet logique. Il suffit de créer ou de supprimer cet objet logique pour intégrer ou supprimer tous les objets qu’il contient. Cela facilite grandement l’installation et la désinstallation de nombreux objets. Les extensions peuvent être codées en différents langages, généralement en C ou en PL/SQL. Elles ont eu un grand succès.

La possibilité de développer des routines dans différents langages en est un exemple : perl, python, PHP, Ruby ou JavaScript sont disponibles. PL/pgSQL est lui-même une extension à proprement parler, toujours présente.

Autre exemple : la possibilité d’ajouter des types de données, des routines et des opérateurs a permis l’émergence de la couche spatiale de PostgreSQL (appelée PostGIS).

Les provenances, rôle et niveau de finition des extensions sont très variables. Certaines sont des utilitaires éprouvés fournis avec PostgreSQL (parmi les « contrib »). D’autres sont des utilitaires aussi complexes que PostGIS ou un langage de procédures stockées. Des éditeurs diffusent leur produit comme une extension plutôt que forker PostgreSQL (Citus, timescaledb…). Beaucoup d’extensions peuvent être installées très simplement depuis des paquets disponibles dans les dépôts habituels (de la distribution ou du PGDG), ou le site du concepteur. Certaines sont diffusées comme code source à compiler. Comme tout logiciel, il faut faire attention à en vérifier la source, la qualité, la réputation et la pérennité.

Une fois les binaires de l’extension en place sur le serveur, l’ordre CREATE EXTENSION suffit généralement dans la base cible, et les fonctionnalités sont immédiatement exploitables.

Les extensions sont habituellement installées par un administrateur (un utilisateur doté de l’attribut SUPERUSER). À partir de la version 13, certaines extensions sont déclarées de confiance trusted). Ces extensions peuvent être installées par un utilisateur standard (à condition qu’il dispose des droits de création dans la base et le ou les schémas concernés).

Les développeurs de PostgreSQL ont aussi ajouté des hooks pour accrocher du code à exécuter sur certains cas. Cela a permis entre autres de créer l’extension pg_stat_statements qui s’accroche au code de l’exécuteur de requêtes pour savoir quelles sont les requêtes exécutées et pour récupérer des statistiques sur ces requêtes.

Enfin, les background workers ont vu le jour. Ce sont des processus spécifiques lancés par le serveur PostgreSQL lors de son démarrage et stoppés lors de son arrêt. Cela a permis la création de PoWA (outil qui historise les statistiques sur les requêtes) et une amélioration très intéressante de pg_prewarm (sauvegarde du contenu du cache disque à l’arrêt de PostgreSQL, restauration du contenu au démarrage).

Des exemples d’extensions sont décrites dans nos modules Extensions PostgreSQL pour l’utilisateur, Extensions PostgreSQL pour la performance, Extensions PostgreSQL pour les DBA.


Sécurité

  • Fichier pg_hba.conf
  • Filtrage IP
  • Authentification interne (MD5, SCRAM-SHA-256)
  • Authentification externe (identd, LDAP, Kerberos…)
  • Support natif de SSL

Le filtrage des connexions se paramètre dans le fichier de configuration pg_hba.conf. Nous pouvons y définir quels utilisateurs (déclarés auprès de PostgreSQL) peuvent se connecter à quelles bases, et depuis quelles adresses IP.

L’authentification peut se baser sur des mots de passe chiffrés propres à PostgreSQL (md5 ou le plus récent et plus sécurisé scram-sha-256 en version 10), ou se baser sur une méthode externe (auprès de l’OS, ou notamment LDAP ou Kerberos qui couvre aussi Active Directory).

L’authentification et le chiffrement de la connexion par SSL sont couverts.


Objets SQL

  • Instances
  • Objets globaux :
    • Bases
    • Rôles
    • Tablespaces
  • Objets locaux :
    • Schémas
    • Tables
    • Vues
    • Index
    • Routines

Le but de cette partie est de passer en revue les différents objets logiques maniés par un moteur de bases de données PostgreSQL.

Nous allons donc aborder la notion d’instance, les différents objets globaux et les objets locaux. Tous ne seront pas vus, mais le but est de donner une idée globale des objets et des fonctionnalités de PostgreSQL.


Organisation logique

Organisation logique d’une instance

Il est déjà important de bien comprendre une distinction entre les objets. Une instance est un ensemble de bases de données, de rôles et de tablespaces. Ces objets sont appelés des objets globaux parce qu’ils sont disponibles quelque soit la base de données de connexion. Chaque base de données contient ensuite des objets qui lui sont propres. Ils sont spécifiques à cette base de données et accessibles uniquement lorsque l’utilisateur est connecté à la base qui les contient. Il est donc possible de voir les bases comme des conteneurs hermétiques en dehors des objets globaux.


Instances

  • Une instance
    • un répertoire de données
    • un port TCP
    • une configuration
    • plusieurs bases de données
  • Plusieurs instances possibles sur un serveur

Une instance est un ensemble de bases de données. Après avoir installé PostgreSQL, il est nécessaire de créer un répertoire de données contenant un certain nombre de répertoires et de fichiers qui permettront à PostgreSQL de fonctionner de façon fiable. Le contenu de ce répertoire est créé initialement par la commande initdb. Ce répertoire stocke ensuite tous les objets des bases de données de l’instance, ainsi que leur contenu.

Chaque instance a sa propre configuration. Il n’est possible de lancer qu’un seul postmaster par instance, et ce dernier acceptera les connexions à partir d’un port TCP spécifique.

Il est possible d’avoir plusieurs instances sur le même serveur, physique ou virtuel. Dans ce cas, chaque instance aura son répertoire de données dédié et son port TCP dédié. Ceci est particulièrement utile quand l’on souhaite disposer de plusieurs versions de PostgreSQL sur le même serveur (par exemple pour tester une application sur ces différentes versions).


Rôles

  • Utilisateurs / Groupes
    • Utilisateur : Permet de se connecter
  • Différents attributs et droits

Une instance contient un ensemble de rôles. Certains sont prédéfinis et permettent de disposer de droits particuliers (lecture de fichier avec pg_read_server_files, annulation d’une requête avec pg_signal_backend, etc). Cependant, la majorité est composée de rôles créés pour permettre la connexion des utilisateurs.

Chaque rôle créé peut être utilisé pour se connecter à n’importe quelle base de l’instance, à condition que ce rôle en ait le droit. Ceci se gère directement avec l’attribution du droit LOGIN au rôle, et avec la configuration du fichier d’accès pg_hba.conf.

Chaque rôle peut être propriétaire d’objets, auquel cas il a tous les droits sur ces objets. Pour les objets dont il n’est pas propriétaire, il peut se voir donner des droits, en lecture, écriture, exécution, etc par le propriétaire.

Nous parlons aussi d’utilisateurs et de groupes. Un utilisateur est un rôle qui a la possibilité de se connecter aux bases alors qu’un groupe ne le peut pas. Un groupe sert principalement à gérer plus simplement les droits d’accès aux objets.


Tablespaces

  • Répertoire physique contenant les fichiers de données de l’instance
  • Une base peut
    • se trouver sur un seul tablespace
    • être répartie sur plusieurs tablespaces
  • Permet de gérer l’espace disque et les performances
  • Pas de quota

Toutes les données des tables, vues matérialisées et index sont stockées dans le répertoire de données principal. Cependant, il est possible de stocker des données ailleurs que dans ce répertoire. Il faut pour cela créer un tablespace. Un tablespace est tout simplement la déclaration d’un autre répertoire de données utilisable par PostgreSQL pour y stocker des données :

CREATE TABLESPACE chaud LOCATION '/SSD/tbl/chaud';

Il est possible d’avoir un tablespace par défaut pour une base de données, auquel cas tous les objets logiques créés dans cette base seront enregistrés physiquement dans le répertoire lié à ce tablespace. Il est aussi possible de créer des objets en indiquant spécifiquement un tablespace, ou de les déplacer d’un tablespace à un autre. Un objet spécifique ne peut appartenir qu’à un seul tablespace (autrement dit, un index ne pourra pas être enregistré sur deux tablespaces). Cependant, pour les objets partitionnés, le choix du tablespace peut se faire partition par partition.

Le but des tablespaces est de fournir une solution à des problèmes d’espace disque ou de performances. Si la partition où est stocké le répertoire des données principal se remplit fortement, il est possible de créer un tablespace dans une autre partition et donc d’utiliser l’espace disque de cette partition. Si de nouveaux disques plus rapides sont à disposition, il est possible de placer les objets fréquemment utilisés sur le tablespace contenant les disques rapides. Si des disques SSD sont à disposition, il est très intéressant d’y placer les index, les fichiers de tri temporaires, des tables de travail…

Par contre, contrairement à d’autres moteurs de bases de données, PostgreSQL n’a pas de notion de quotas. Les tablespaces ne peuvent donc pas être utilisés pour contraindre l’espace disque utilisé par certaines applications ou certains rôles.


Bases

  • Conteneur hermétique
  • Un rôle ne se connecte pas à une instance
    • il se connecte forcément à une base
  • Une fois connecté, il ne voit que les objets de cette base
    • contournement : foreign data wrappers, dblink

Une base de données est un conteneur hermétique. En dehors des objets globaux, le rôle connecté à une base de données ne voit et ne peut interagir qu’avec les objets contenus dans cette base. De même, il ne voit pas les objets locaux des autres bases. Néanmoins, il est possible de lui donner le droit d’accéder à certains objets d’une autre base (de la même instance ou d’une autre instance) en utilisant les Foreign Data Wrappers (postgres_fdw) ou l’extension dblink.

Un rôle ne se connecte pas à l’instance. Il se connecte forcément à une base spécifique.


Schémas

  • Espace de noms
  • Sous-ensemble de la base
  • Non lié à un utilisateur
  • Résolution des objets : search_path
  • pg_catalog, information_schema
    • pour catalogues système (lecture seule !)

Les schémas sont des espaces de noms à l’intérieur d’une base de données permettant :

  • de grouper logiquement les objets d’une base de données ;
  • de séparer les utilisateurs entre eux ;
  • de contrôler plus efficacement les accès aux données ;
  • d’éviter les conflits de noms dans les grosses bases de données.

Un schéma n’a à priori aucun lien avec un utilisateur donné.

Un schéma est un espace logique sans lien avec les emplacements physiques des données (ne pas confondre avec les tablespaces).

Un utilisateur peut avoir accès à tous les schémas ou à un sous-ensemble, tout dépend des droits dont il dispose. Depuis la version 15, un nouvel utilisateur n’a le droit de créer d’objet nulle part. Dans les versions précédentes, il avait accès au schéma public de chaque base et pouvait y créer des objets.

Lorsque le schéma n’est pas indiqué explicitement pour les objets d’une requête, PostgreSQL recherche les objets dans les schémas listés par le paramètre search_path valable pour la session en cours .

Voici un exemple d’utilisation des schémas :

-- Création de deux schémas
CREATE SCHEMA s1;
CREATE SCHEMA s2;

-- Création d'une table sans spécification du schéma
CREATE TABLE t1 (id integer);

-- Comme le montre la méta-commande \d, la table est créée dans le schéma public
postgres=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | capitaines        | table    | postgres
 public | capitaines_id_seq | sequence | postgres
 public | t1                | table    | postgres
-- Ceci est dû à la configuration par défaut du paramètre search_path
-- modification du search_path
SET search_path TO s1;

-- création d'une nouvelle table sans spécification du schéma
CREATE TABLE t2 (id integer);

-- Cette fois, le schéma de la nouvelle table est s1
-- car la configuration du search_path est à s1
-- Nous pouvons aussi remarquer que les tables capitaines et s1
-- ne sont plus affichées
-- Ceci est dû au fait que le search_path ne contient que le schéma s1 et
-- n'affiche donc que les objets de ce schéma.
postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 s1     | t2   | table | postgres
-- Nouvelle modi