PostgreSQL

PostgreSQL n'est pas le seul SGBDR existant sous Linux : pendant longtemps, les alternatives possibles les plus connues étaient MySql, msql. Les temps ont changé : la renommée de Linux a fait que les plus grands éditeurs de bases de données ont décidé de porter leurs produits sous Linux : Oracle, Informix, Sybase,... Tous ces SGBDR sont à présent disponibles.

Nous présentons ici PostgreSQL car il s'agit d'un logiciel libre, moins lourd que les ténors que sont Oracle, Informix et Sybase, et qu'il dispose de toutes les fonctionnalités que l'on est en droit d'attendre d'un SGBDR moderne : il existe des frontaux graphiques pour la manipulation des tables sous le système X Window, des bibliothèques permettant de manipuler les bases à partir de programmes écrits en C/C++, en Perl, en Tcl... L'utilisation de Tk avec ces deux derniers langages permet de créer ses propres frontaux X.

PostgreSQL dispose d'une API ODBC permettant aux applications utilisant cette interface d'accéder aux bases de données PostgreSQL et d'une API JDBC permettant à des applications Java respectant cette norme d'y accéder.

Par contre, il n'existe pas, à ma connaissance, de générateurs de rapports ou d'interfaces utilisateurs aussi évolués que ceux dont disposent les produits «professionnels» comme Oracle, par exemple. Les frontaux graphiques sont soit dans des versions non encore finalisées (pgaccess), soit de simples fenêtres X permettant de saisir plus aisément des requêtes SQL (mpsql).

La dernière version de PostgreSQL, à l'heure où nous écrivons ces lignes, est la 6.4. Celle qui est la plus répandue sur les distributions Linux actuelles est la 6.3 et c'est elle que nous décrivons.

On notera aussi que PostgreSQL n'est en rien réservé à Linux : des versions existent pour les plates-formes AIX RS6000, BSDi x86, FreeBSD x86, NetBSD x86 Sparc et VAX, DGUX m68k, HPUX PA-Risc, IRIX MIPS, Digital Alpha, SCO x86, Solaris x86, SunOS Sparc, SVR4 m68k, Unixware x86 et NextStep x86.

Pour les autres plate-formes, la partie cliente (via ODBC et JDBC) reste possible.

La documentation de PostgreSQL est, par ailleurs, complète (mais en anglais). Le Database-HOWTO est traduit en français. Son principal intérêt est de présenter les différents frontaux, utilitaires et bibliothèques permettant d'étendre PostgreSQL : si vous voulez connaître les produits travaillant avec PostgreSQL, c'est là qu'il faut commencer par chercher...

Cet article ne se veut pas une traduction ou un résumé de la documentation existante sur PostgreSQL, même si, évidemment, il s'en inspire. Il s'agit plutôt de la description d'une installation sur une machine Linux et de sa configuration nécessaire et suffisante : en quelque sorte, c'est mon interprétation personnelle de la documentation officielle...

La création des utilisateurs, des bases de données ainsi que leur manipulation seront également décrites mais il n'est pas question ici de donner un cours SQL, ni d'expliquer comment attaquer une base de données PostgreSQL en C ou en Perl. Cela fera l'objet d'autres articles : chaque chose en son temps ;-)

Nous en profiterons pour indiquer comment étendre cette configuration monoposte à une configuration en réseau.

PostgreSQL utilise une architecture Client-Serveur, le produit est donc composé :

- d'un programme serveur : postmaster qui devra tourner sur la machine sur laquelle sont stockées les bases de données (le serveur de base de données). Ce programme est généralement lancé à l'initialisation de la machine serveur via les scripts de démarrage ;

- de programmes clients : ces programmes doivent tourner sur les machines désirant utiliser les services du serveur de bases de données. La distribution PostgreSQL standard dispose de clients permettant :

- d'effectuer des requêtes SQL (psql) ;

- de créer de nouvelles bases et d'en supprimer (createdb et destroydb) ;

- de créer de nouveaux utilisateurs et d'en supprimer (createuser et destroyuser ;

La distribution installe aussi les bibliothèques nécessaires ainsi qu'un client X (pgaccess) écrit en Tcl/Tk permettant de créer et d'interroger les tables d'une base de données.

Toutes les requêtes sont donc effectuées par les clients qui s'adressent au serveur, seul programme ayant véritablement accès aux bases de données. Celui-ci effectue les requêtes et renvoie les résultats aux clients. Le schéma de tout ceci est donc celui-ci :

Dans un premier temps, nous considèrerons l'utilisation de PostgreSQL sur une configuration monoposte : la même machine fait donc office de serveur et de client ce qui nécessitera l'installation de tous les composants sus-cités sur cette machine.

Nous utilisons une distribution Debian Slink et les emplacements des fichiers indiqués ici sont donc ceux de cette distribution. Toutefois, il n'y a aucun problème pour les autres distributions : la quasi-totalité de celles-ci utilisent des paquetages logiciels et, que ce soit les paquetages RPM ou les tarballs de la Slackware, le principe restera le même : il suffit de générer un listing du contenu du paquetage de PostgreSQL et de le sauvegarder dans un fichier : sous Debian, cela est effectué par la commande

$ dpkg -? contents postgresql_postgresql_6.3.2-15.deb > postgres.contents

Vous feriez la même chose pour un paquetage RPM en faisant :

$ rpm -qlp postgres*.rpm > postgres.contents

et avec un tarball en faisant :

$ tar tvzf postgres*.tgz > postgres.contents

Le contenu du fichier postgres.contents indique les emplacements où seront installés les différentes parties constituant PostgreSQL. Sur ma Debian, il s'agit de :

/etc/postgresql/postmaster.init

/etc/postgresql/postgresql.env

/etc/postgresql/pg_hba.conf

/etc/postgresql/pg_ident.conf

/etc/init.d/

/etc/init.d/postgresql

/etc/cron.d/

/etc/cron.d/postgresql

/usr/bin/pg_wrapper

/usr/bin/createdb -> pg_wrapper

/usr/bin/createuser -> pg_wrapper

/usr/bin/destroydb -> pg_wrapper

/usr/bin/destroyuser -> pg_wrapper

/usr/bin/pg_dump -> pg_wrapper

/usr/bin/psql -> pg_wrapper

/usr/doc/postgresql/...

/usr/lib/postgresql/bin/postgres

/usr/lib/postgresql/bin/postmaster -> postgres

/usr/lib/postgresql/bin/pg_id

/usr/lib/postgresql/bin/pg_version

/usr/lib/postgresql/bin/psql

/usr/lib/postgresql/bin/pg_dump

/usr/lib/postgresql/bin/pg_dumpall

/usr/lib/postgresql/bin/pg_upgrade

/usr/lib/postgresql/bin/pg_passwd

/usr/lib/postgresql/bin/cleardbdir

/usr/lib/postgresql/bin/createdb

/usr/lib/postgresql/bin/createuser

/usr/lib/postgresql/bin/destroydb

/usr/lib/postgresql/bin/destroyuser

/usr/lib/postgresql/bin/initdb

/usr/lib/postgresql/bin/initlocation

/usr/lib/postgresql/bin/ipcclean

/usr/lib/postgresql/bin/pgtclsh

/usr/lib/postgresql/bin/pgtksh

/usr/lib/postgresql/bin/do.maintenance

/usr/lib/postgresql/bin/postgresql-dump

/usr/lib/postgresql/dumpall/

/usr/lib/postgresql/dumpall/pg_dumpall

/usr/man/man1/...

/usr/man/man5/...

/usr/man/man7/...

/var/postgres/data/

On voit que le paquetage installe les binaires sous /usr/lib/postgresql/bin/ et /usr/bin/, les fichiers de configuration sous /etc/postgresql/, les pages man sous /usr/man/ et les données (en fait, les bases de données...) sous /var/postgres/data/.

On note aussi que c'est le script de démarrage postgresql, situé dans le répertoire /etc/init.d/ qui est responsable de l'intialisation du serveur (en utilisant les fichiers de configuration). Etant donnée sa taille, la documentation complète fait l'objet d'un paquetage séparé.

Vous noterez que certains binaires semblent être présents en double exemplaire : psql, par exemple, est à la fois un lien symbolique vers /usr/bin/pg_wrapper et un exécutable /usr/lib/postgresql/bin/psql... En fait, comme son nom l'indique, pg_wrapper est une enveloppe de sécurité pour les différentes commandes clientes. Lorsque vous faites psql, vous appelez en fait /usr/bin/pg_wrapper qui, à son tour, appelle /usr/lib/postgresql/bin/psql. Cela fait bien des détours, mais c'est le prix à payer pour avoir une sécurité sur votre système et, de toutes façons, c'est transparent pour l'utilisateur...

Vous noterez aussi que le paquetage installe une tâche cron dont le but est de lancer quotidiennement le script /usr/lib/postgresql/bin/do.maintenance chargé de faire le ménage dans les bases en les compactant.

Une fois que vous avez sauvegardé ces emplacements, vous pouvez procéder à l'installation proprement dite du paquetage : reportez-vous à la documentation de votre distribution et comparez votre liste à la mienne pour pouvoir faire les éventuelles translations. Les fichiers que nous venons de lister sont maintenant à leurs emplacements sur votre système...

Le premier lancement

Pour utiliser PostgreSQL, le serveur postmaster (ou postgresql, ce qui est la même chose) doit avoir été préalablement lancé. Avec Debian, l'installation du paquetage se charge du premier lancement (les autres se feront via le script /etc/init.d/postgresql).

Pour vérifier que le serveur tourne, il suffit d'exécuter la commande :

$ ps aux | grep [p]ostgres

qui doit répondre :

postgres ... /usr/lib/postgresql/bin/postmaster -b ...

Si vous n'avez pas cette ligne, cela signifie que le serveur n'a pas été lancé : ce n'est pas grave, on va le faire «à la main» pour cette fois-ci.

- connectez-vous sous le compte root via un su ;

- lorsque vous êtes sous root connectez-vous sous le compte postgres via un su postgres (sans mot de passe) ;

- faites la commande source /etc/postgresql/postgresql.env afin de positionner les chemins nécessaires (adaptez cette dernière commande à votre système) ;

- faites la commande /usr/lib/postgresql/bin/postmaster -i & (notez le &...).

- vérifiez par un ps aux | grep [p]ostgres que le serveur fonctionne.

Lancements automatiques

Bien sûr, il n'y aura plus besoin de renouveler toutes ces opérations après chaque redémarrage de votre machine si vous avez vérifié que celles-ci sont automatiquement réalisées par les scripts de démarrage. Encore une fois, l'installation a dû s'occuper de tout cela, mais autant vérifier...

Toutes les opérations de modification décrites ci-dessous doivent se faire sous le compte root.

On a vu que le script /etc/init.d/postgresql est responsable du démarrage du serveur mais il faut maintenant vérifier que ce script est bien appelé au démarrage de votre système. Pour ce faire, sur une Debian, allez dans le répertoire /etc/rcn.d où n est le runlevel de démarrage et vérifiez la présence du lien S20postgresql -> ../init.d/postgresql.

Pendant que vous y êtes, vérifiez la présence du lien K20postgresql dans /etc/rc6.d et /etc/rc0.d afin que le serveur soit correctement arrêté lors de l'arrêt du système.

Si vous utilisez une autre distribution, sauf une Slackware, le principe reste le même aux emplacements prêts. Les utilisateurs de Red Hat peuvent utiliser l'outil tksysv pour configurer sous X le lancement/arrêt du serveur. Les utilisateurs d'une slackware devront placer un appel explicite au script de démarrage dans le fichier rc.local, par exemple.

Si le lancement du serveur échoue, vérifiez la présence du répertoire /var/postgres/data qui doit exister et appartenir à l'utilisateur et au groupe postgres. Créez-le à partir du compte postgres si nécessaire.

Si ce répertoire existe, il doit contenir au moins une base de données : /var/postgres/data/base/template1/. L'installation l'aura normalement créée, mais si ce n'est pas le cas, toujours sous le compte postgres, créez-la avec la commande initdb.

Essayez de relancer le serveur, maintenant, ça devrait fonctionner...

Si cela ne marche toujours pas, recommencez : vous ne pouvez rien faire de ce qui suit tant que le serveur ne marche pas !

Comme tout SGBR qui se respecte, PostgreSQL différencie ses utilisateurs selon les droits de ceux-ci.

Fondamentalement, il y a deux classes d'utilisateurs :

- l'administrateur de la base de données, ou DBA (DataBase Administrator) ;

- les autres utilisateurs.

L'administrateur de la base de données

Initialement, cet utilisateur privilégié s'appelle postgres. Vous noterez que, pour des raisons de sécurité, le DBA ne doit pas être root.

Le DBA est responsable de la gestion du SGBDR, de sa maintenance et de sa sécurité. C'est lui qui a le pouvoir de créer d'autres utilisateurs en leur transmettant éventuellement une partie de ses pouvoirs.

Par défaut, l'utilisateur postgres est un compte fermé : on ne peut s'y connecter directement car son entrée dans /etc/passwd (ou dans /etc/shadow) est de la forme :

postgres:*:31:32:postgres:/var/postgres:/bin/sh

Pour devenir DBA, la seule solution consiste donc à se connecter sous root puis de passer sous le compte postgres via la commande su postgres.

Pour pouvoir se connecter directement sous ce compte, il suffit de le déverrouiller, sous le compte root, en lui donnant un mot de passe.

La création et la suppression des utilisateurs

Pour créer les premiers utilisateurs, le DBA utilisera la commande createuser de la façon suivante :

$ createuser dupont

Enter user's postgres ID ->

La première question constitue une anomalie de fonctionnement (elle est notée comme telle et sera probablement levée dans les versions futures) car elle impose que l'utilisateur créé ait le même identificateur que son UID système, ce qui est une contrainte inutile. Par conséquent, avant de créer un nouvel utilisateur, il faudra avoir préalablement noté son UID avec la commande :

$ id dupont

uid=1001(dupont) gid=100(users)

C'est ce numéro qu'il faudra alors fournir à createuser.

Puis, il vous sera demandé si cet utilisateur sera lui-même autorisé à créer des bases de données et s'il pourra à son tour créer d'autres utilisateurs :

$ createuser dupont

Enter user's postgres ID -> 1001

Is user «dupont» allowed to create databases (y/n) y

Is user «dupont» allowed to add users? (y/n) n

createuser: dupont was successfully added

Personnellement, je ne vous conseille pas de donner à d'autres utilisateurs que le DBA la possibilité de créer d'autres utilisateurs, mais c'est vous le DBA...

La dernière ligne vous confirmera la réussite de cette création.

La suppression d'un utilisateur se fait grâce à la commande destroyuser (vous comprenez pourquoi root ne doit pas être DBA ? Imaginez que vous fassiez tout cela sous root et que vous confondiez destroyuser avec userdel...)

destroyuser dupont

destroyuser: postgres cannot delete users.

destroyuser: delete of user dupont was successful.

Vous noterez la première ligne qui semble curieuse... ignorez-la.

Connexion à une base de données

Toujours sous le compte postgres, faites :

$ psql

ce qui aura pour effet d'appeler le client SQL de PostgreSQL et de vous connecter à la base par défaut : template1. Vous devez obtenir l'écran suivant :

Welcome to \? the POSTGRESQL interactive sql monitor:

Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands

type \q to quit

type or \g terminate with semicolon to execute query

You are currently connected to the database: template1

template1=>

La dernière ligne (template1=>) est le message d'invite de psql qui vous indique que vous êtes bien connecté à la base template1.

Quittez psql en tapant \g.

Connectez-vous maintenant sous le compte dupont (ou l'un de ceux que vous avec créés avec la commande createuser) et refaites la même chose afin de vérifier que les utilisateurs déclarés peuvent bien se connecter au SGBDR.

Création du base de données

Toujours sous le compte dupont, faites la commande suivante :

$ createdb testpg

Si la commande ne produit aucun message, c'est que la base testpg a bien été créée. Si un message vous indique que la base n'a pas pu être créée, c'est que vous avez oublié d'autoriser dupont à créer des bases... Détruisez-le (sous le compte postgres) par la commande destroyuser dupont et recréez-le en lui donnant ce droit...

Admettons que la création ait réussi : connectons-nous à celle-ci en indiquant à psql qu'il doit utiliser cette base plutôt que celle par défaut :

$ psql testpg

Welcome to the POSTGRESQL interactive sql monitor:

Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands

type \q to quit

type or \g erminate with semicolon to execute query

You are currently connected to the database: testpg

testpg=>

Bravo ! Nous allons pouvoir passer aux choses sérieuses...

Un mot auparavant : la base testpg ayant été créée par dupont (a qui le DBA a donné le pouvoir de création), elle pourra n'être détruite que par lui et le DBA par la commande :

$ destroydb testpg

Attention : la destruction d'une base entraîne celle de toutes les tables, et donc de toutes les données de celles-ci... Cette commande est à utiliser avec moult précautions car, hormis les sauvegardes, il n'y a pas de retour en arrière possible !

psql reconnaît deux types de commandes :

- les commandes psql, qui sont préfixées par \ (antislash) ;

- les requêtes SQL qui peuvent s'étendre sur plusieurs lignes et qui se terminent par ; (point-virgule).

Dans cet article, nous nous bornerons à créer une simple table. Les articles suivants traiterons en détail des spécificités SQL de PostgreSQL.

Création de la table utilisateur

Nous supposons que l'utilisateur dupont est toujours connecté à la base testpg... écrivez la requête suivante à l'invite de psql :

create table utilisateur (num_user char(4) not null primary key, <Entrée>

nom_user char(20) not null, prenom_user char(20) not null, <Entrée>

date_naissance date, sys_used char(20), depuis date) ; <Entrée>

Les signes <Entrée> signifient «Appui sur la touche Entrée». Vous avez noter comment l'invité est modifiée ?

Si vous ne vous êtes pas trompé, vous devez obtenir le message suivant après la dernière validation :

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index utilisateur_pkey for table utilisateur

CREATE

testpg=>

vous indiquant que la table et son index des clés on bien été créés.

Utilisation des commandes psql

Comme indiqué, vous pouvez faire \ ? pour obtenir la liste de toutes les commandes disponibles... Nous ne verrons ici que les principales et je compte sur vous pour tester les autres d'ici la prochaine fois.

Faites \dt (display tables) pour voir les tables créées par les utilisateurs de la base testpg :

testpg => \dt

Database = testpg

Owner	Relation	Type
dupont	utilisateur	table

testpg=>

vous indique qu'il n'y a qu'une seule table et qu'elle a été créée par l'utilisateur dupont (sous PostgreSQL, tout utilisateur créé peut créer ses tables dans n'importe quelle base, ce qui ne veut pas dire qu'il peut faire n'importe quoi avec celles des autres).

Faites \ds (display system) pour voir toutes les tables de la base (nous n'affichons pas ici le résultat produit car il est plutôt long). Vous remarquez que toutes ces tables et index appartiennent au DBA et que leurs noms commencent par pg : ce sont les tables systèmes permettant de gérer la base testpg.

Faites \z pour voir les droits apposés sur les tables utilisateurs :

testpg=> \z

Database = testpg

Relation utilisateur utilisateur_pkey

Grant/Revoke Permissions

testpg=>

Pour les deux tables créées (la table et son index), aucune permission n'est positionnée ce qui signifie que seul leur créateur (et le DBA) y ont accès. Nous verrons la prochaine fois comment accorder certains droits sur certaines tables à certains utilisateurs.

Faites \1 pour voir toutes les bases installées sur le serveur :

testpg=>\1 datname	datdba	datpath
template1                                     testpg                 (2 rows)	31 1001	template1 testpg

   testpg=>

Ici, nous n'avons que deux bases : template1 appartenant à postgres (uid 31), et testpg appartenant à dupont (uid 1001).

Vous pouvez passez d'une base à l'autre grâce à la commande \ connect

testpg=> template1

connecting to new database: template1

template1=>

vous permet d'être maintenant connecté à la base template1.

Enfin, faites \q pour quitter psql : nous continuerons la semaine prochaine en présentant les différents frontaux graphiques et en travaillant sur les bases.

En attendant, trouvez-vous une bonne doc ou un bon bouquin sur SQL et essayez ! n