Prise en main de PostgreSQL

Gestion des droits d'accès

PostgreSQL permet d'utiliser différentes méthodes d'accès à ses bases de données. Pour résumer, il est possible de s'authentifier :

• selon le nom d'utilisateur du compte système utilisé : c'est à dire qu'une utilisatrice du sytème d'identifiant alice peut disposer de droits d'accès à certaines bases de données sans avoir à s'authentifier à nouveau (on considère alors que si l'utilisatrice alice peut utiliser ce compte, c'est qu'elle a déjà été authentifiée). Cette méthode peut aussi s'appuyer sur un serveur identd si la connexion est distante ;
• en utilisant un mot de passe défini dans les tables systèmes du serveur PostgreSQL. Dans ce cas, c'est le serveur PostgreSQL qui gère sa base d'utilisateur et vérifie le mot de passe.

On peut choisir parmi ces différentes méthodes en modifiant le fichier pg_hba.conf conservé dans le répertoire de configuration du serveur et contrôlant l'authentification du client (HBA signifiant « Host-Based Authentication », authentification basée sur l'hôte).

Les lignes de configuration de ce fichier texte pg_hba.conf comportent au moins 6 colonnes, intitulées TYPE, DATABASE, USER, IP-ADDRESS, IP-MASK, METHOD. Une dernière colonne facultative OPTION peut être présente :

• TYPE précise le type d'accès utilisé par le client. Elle peut valoir :
  • local : accès depuis le système local,
  • host : accès depuis un système distant via TCP/IP,
  • hostssl : comme host, mais obligatoirement avec une couche SSL,
  • hostnossl : comme host, mais obligatoirement sans couche SSL ;
• DATABASE peut valoir :
  • all : pour représenter toute base de données,
  • sameuser : représente la base de données ayant le même nom que le compte utilisateur,
  • samegroup : la base de données ayant le même nom que le groupe,
  • un nom de base de données,
  • une liste de noms de bases séparés par une virgule ;
• USER peut valoir :
  • all pour représenter n'importe quel utilisateur,
  • un nom d'utilisateur particulier,
  • un nom de groupe d'utilisateurs s'il est préfixé de « + »,
  • un fichier qui sera inclus si le nom est préfixé par « @ » ;
• IP_ADDRESS, valable uniquement si le type vaut host, hostssl, ou hostnossl, est l'adresse IP de la machine distante, éventuellement complétée par un masque CIDR (sous forme d'entier).
• IP_MASK est le masque (en notation pointée pour IPv4 ou IPv6) complétant l'adresse IP de la colonne précédente. Cette colonne sera omise si le masque a été précisé sour forme CIDR avec l'adresse IP à la colonne précédente ;
• METHOD décrit la méthode d'authentification. Elle peut prendre de nombreuses valeurs (voir auth-method dans le chapitre « Le fichier pg_hba.conf » de la documentation de PostgreSQL). Les méthodes les plus courantes sont :
  • trust : pour réaliser l'authentification de façon inconditionnelle,
  • reject : pour, à l'inverse, toujours la refuser, ce qui permet par exemple de refuser l'accès à certaines machines,
  • scram-sha-256, md5, ou password : réalisent une authentification de l'utilisateur dont le mot de passe est conservé sous forme chiffrée dans la table interne pg_shadow de PostgreSQL. Attention, l'option password est déconseillée car le mot de passe circule en clair sur le réseau,
  • peer : cette méthod ne fonctionne que pour les connexions locales. Elle obtient le nom de l'utilisateur depuis le système local,
  • ident : pour les connexions réseau, cette médhode contacte le serveur d'identification du poste client. Si la connexion est locale, elle sera remplacée par la méthode peer,
  • pam : effectue l'authentification en se basant sur les modules PAM (Pluggable Authentification Modules) du système d'exploitation⋅ ;
• la dernière colonne peut fournir une option contrôlant le comportement de la méthode d'authentification. Elle est surtout utilisé par la méthode ident avec laquelle on peut préciser une option du type map=<nom> où <nom> est la première colonne d'une entrée du fichier pg_ident.conf (conservé dans le même répertoire que pg_hba.conf) qui fait correspondre un nom renvoyé par ident avec le nom d'un utilisateur PostgreSQL. (pour plus de détails, voir la documentation PostgreSQL sur les correspondances d'utilisateurs.)

Une fois un utilisateur connecté, on peut affiner ses droits sur les différents objets (bases de données, schémas et tables) à l'aide des commandes SQL standard GRANT et REVOKE.

Accès par défaut après l'installation

La configuration d'origine du serveur PostgreSQL installé « manuellement » donne par défaut un accès inconditionnel à tous les utilisateurs du système local. Ce comportement peut être dangeureux, il est donc recommandé de le modifier rapidement après installation.

Sous Debian la configuration par défaut après installation du paquet est d'autoriser les connections locales de l'utilisateur système postgres en tant que postgres sous PostgreSQL, où il est administrateur. Cette possibilité est d'ailleurs utilisée par les scripts de maintenance, il est donc nécessaire de la conserver.

Création et suppression d'un utilisateur

La création d'un nouvel utilisateur dans PostgreSQL peut se faire à l'aide de la commande createuser fournie avec PostgreSQL. On la trouve dans le répertoire bin de la distribution d'origine, ou, sous Debian, dans /usr/bin/createuser. Pour créer un utilisateur depuis un shell Unix, on pourra donc utiliser :

createuser myuser

Cette commande ne peut être lancée que par un utilisateur pouvant se connecter en tant qu'administrateur PostgreSQL, c'est un dire qui peut créer d'autres utilisateurs.

Les options suivantes du script createuser peuvent être utiles :

• l'option -U <user> permet de préciser le nom de l'utilisateur PostgreSQL s'il est différent de l'utilisateur système ;
• -a permet de donner les droits de création d'utilisateur à l'utilisateur qui sera créé ;
• -d donne à ce nouvel utilisateur le droit de créer des bases de données  ;
• -P demande au script de demander la saisie d'un mot de passe pour le nouvel utilisateur (il est facultatif puisque certaines méthodes d'authentification n'utilisent pas de mot de passe).

Si on peut déjà se connecter avec des droits suffisants à l'aide de pqsl (voir ci-dessous pour son utilisation), il est aussi possible d'utiliser la commande SQL :

CREATE USER myuser;

On peut préciser un mot de passe pour une éventuelle authentification basée sur un mot de passe :

CREATE USER myuser WITH PASSWORD 'lemotdepasse';

La forme suivante lui donne aussi les droits de création d'une nouvelle base et d'un nouvel utilisateur :

CREATE USER myuser WITH CREATEDB CREATEUSER;

Le changement du mot de passe d'un utilisateur s'effectuer avec la commande SQL ALTER USER :

ALTER USER myuser WITH PASSWORD 'mdpbidon';

La suppression d'un utilisateur s'effectue via la commande dropuser :

dropuser myuser

La commande SQL DROP USER peut aussi être utilisée à cet effet :

DROP USER myuser;

Création et suppression d'une base de données

La création d'une base de données peut se faire à l'aide de la commande createdb fournie avec PostgreSQL. On la trouve dans le répertoire bin de la distribution d'origine, ou, sous debian, dans /usr/bin/createdb. On peut ainsi créer une base depuis un shell Unix :

createdb mydatabase

L'option -O permet de préciser le propriétaire de la nouvelle base (l'utilisateur doit bien sûr avoir été créé à l'aide des commandes données dans la section précédente) :

createdb -O myuser mydatabase

Si on peut se connecter au serveur PostgreSQL avec des droits suffisants, il est aussi possible d'utiliser la commande SQL suivante pour créer une base mydatabase :

CREATE DATABASE mydatabase;

Pour préciser le propriétaire de la base, on utilisera quelque chose comme ceci :

CREATE DATABASE mydatabase WITH OWNER myuser;

La suppression d'une base de données s'effectue via la commande dropdb :

dropdb mydatabase

Ou, depuis PostgreSQL, grâce à la commande SQL DROP DATABASE :

DROP DATABASE mydatabase;

Connexion à la base de données

On peut bien entendu utiliser différents types de clients pour se connecter à la base de données, mais je n'aborderai ici que le client en ligne de commande fourni avec le serveur, psql.

Pour se connecter, le client psql demande qu'on lui précise le nom d'une base de données (sinon il tente de se connecter à une base de donnée du même nom que l'utilisateur PostgreSQL qu'on utilise). Il faut donc en créer une d'abord. On peut toutefois aussi utiliser la base créée automatiquement à l'installation des paquets Debian et nommée template1.

psql template1

La connexion sera autorisée uniquement si le fichier pg_hba.conf est configuré pour permettre à l'utilisateur système que vous utilisez de se connecter à cette base. Si l'on veut se connecter en utilisant un autre utilisateur PostgreSQL, il faut utiliser l'option -U utilisateur (il faut là aussi que le fichier pg_hda.conf permette cette connexion).

Une fois connecté, on peut envoyer des commandes spécifiques à PostgreSQL et au client psql. Elles commencent toutes par une barre oblique inversée « \ ». La commande \? permet d'afficher l'ensemble des commandes disponibles avec une description de leur rôle.

Voici quelques unes des commandes les plus utiles (dans lesquelles [MODELE] est un motif facultatif limitant les objets à afficher) :

\l             affiche toutes les bases de données (ajoutez «+» pour plus de détails)
\d [NOM]       décrit la table, l'index, la séquence ou la vue
\d{t|i|s|v|S} [MODELE] (ajoutez "+" pour plus de détails)
                affiche les tables/index/séquences/vues/tables système
\du [MODELE]   affiche la liste des utilisateurs
\dp [MODELE]   affiche la liste des privilèges d'accès aux tables
\dn [MODELE]   affiche la liste des schémas
\dp [MODELE]   affiche la liste des privilèges d'accès aux tables
\z [MODELE]    identique à \dp
\c[onnect] [NOM_BASE|- [NOM_UTILISATEUR]]
               connecte à une autre base de données (actuellement «template1»)

Les schémas dans PostgreSQL

PostgreSQL possède de nombreuses fonctionnalités qui en font un serveur de base de données très complet, comme par exemple le support poussé des sous-requêtes, celui des clefs étrangères, des vues, des transactions, des fonctions déclenchées (triggers), des languages procéduraux, etc.

Les schémas représentent également une fonctionnalité bien pratique mais pas toujours très connue des utilisateurs plus habitués à MySQL, qui ne la propose pas (ou peut-être seulement dans ses versions les plus récentes).

Un schéma est un espace qui permet, dans une même base de données, de classer les tables en différents espaces de nommage. On désignera alors une table matable créée dans le schéma monschema en utilisant la notation monschema.matable.

On peut également demander au client de ne considérer que les objets présents dans une liste de schémas, un peu à la façon du PATH des shells unix lorsqu'ils recherche un exécutable. Cette liste de schéma à rechercher est conservée dans la variable search_path.

Par défaut, cette variable vaut « $user,public ». Cela signifie que le client va rechercher les objets tout d'abord dans le schéma qui porte le même nom que l'utilisateur, s'il existe, et ensuite dans le schéma public qui regroupe tous les objets qui n'ont pas été créés dans un schéma particulier.

template1=# show search_path;
 search_path  
--------------
 $user,public
(1 ligne)
template1=# set search_path=monschema;

Il est possible d'utiliser le fichier $HOME/.psqlrc pour initialiser cette variable au lancement du client postgresql en y plaçant la commande adéquate. On peut aussi changer le search_path utilisé par défaut quand on se connecte à une base de donnée :

alter database template1 set search_path=monschema;