PostgreSQL est un puissant système de gestion de base de données. Il est réputé pour sa fiabilité, son intégrité des données, et son exactitude. Il fonctionne sur tous les principaux systèmes d’exploitation, y compris Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS, Solaris, Tru64) et Windows. Il est entièrement compatible ACID, a un support complet pour les clés étrangères, jointures, vues, triggers et des procédures stockées (en plusieurs langues).

Voici un tableau récapitulatif des limites de PostgreSQL :

Limite Valeur
Taille maximale de base de données Illimitée
Taille maximale de la table 32 TB
Taille maximale de ligne 1.6 To
Taille maximale du champ 1 GB
Nombre maximal de lignes par table Illimitée
Nombre maximum de colonnes par table 250 – 1600 selon les types de colonnes
Index maximum par table Illimitée

Source : https://www.postgresql.org/about/

Ce tutoriel explique comment installer et configurer un serveur PostgreSQL 9.1.24 sous Debian 7.7.0. Si vous utilisez une version de Debian ou une distribution différente il pourrait y avoir quelques divergences de paramétrage.

Toutes les manipulations à venir seront à effectuer en tant que super-utilisateur.


Installation de PostgreSQL

Mettre à jour la distribution

Exécutez la commande suivante :

apt-get update -y && apt-get upgrade -y

Cette commande permet de mettre à jour la liste des paquets de Debian et de mettre à jour les paquets installés sur votre système Debian.

Installer PostgreSQL

Exécutez la commande suivante :

apt-get install postgresql postgresql-client postgresql-contrib

Si vous souhaitez également pouvoir administrer votre serveur PostgreSQL depuis une interface graphique, alors vous pouvez installer phppgadmin :

apt-get install phppgadmin

A l’installation de PostgreSQL, un utilisateur à la fois du système et de PostgreSQL, nommé postgres, est créé (sans mot de passe) avec sa propre base de données portant son nom. Cet utilisateur est également administrateur de PostgreSQL ;

Vous pouvez déjà accéder à PostgreSQL en étant connecté en tant que postgres via les commandes suivantes :

su - postgres
psql

Pour quitter PostgreSQL utilisez la commande suivante :

\q

Configuration de PostgreSQL

Ajouter un mot de passe à postgres

Comme l’utilisateur postgres est administrateur de PostgreSQL, il est préférable de sécuriser son utilisation par à un mot de passe.

Connectez-vous à l’utilisateur postgres :

su - postgres

Connectez-vous à PostgreSQL :

psql

Donnez un mot de passe à l’utilisateur à l’aide de la commande suivante :

\password postgres

Syntaxe : \password nomutilisateur

Installer le pack d’administration [Facultatif]

Le pack d’administration de PostgreSQL fournit un certain nombre de fonctions de soutien à pgadmin, qui l’outil d’administration graphique de PostgreSQL, ainsi que d’autres outils d’administration et de gestion qui fournissent des fonctionnalités supplémentaires. L’ utilisation de toutes ces fonctions est restreinte à l’administrateur.

Créez l’extension adminpack :

CREATE EXTENSION adminpack;

Syntaxe : CREATE EXTENSION nomextension

Quittez PostgreSQL :

\q

Créer un utilisateur administrateur distant

L’utilisateur postgres est déjà super-utilisateur et est utilisé pour gérer la base de données administrative de PostgreSQL. Néanmoins, par défaut il n’est que utilisable localement et il le restera : nous allons créer un autre utilisateur super-utilisateur pour cela.

Quittez PostgreSQL si ce n’est pas encore fait :

\q

Déconnectez vous de l’utilisateur postgres :

exit

Créez un utilisateur systèmes (postsql par exemple) :

adduser nomutilisateur

Connectez vous à l’utilisateur postgres :

su - postgres

Ajoutez l’utilisateur système en tant qu’utilisateur PostgreSQL :

createuser

Renseignez le rôle (nom d’utilisateur) ;

Répondez positivement à la question qui suit  pour qu’il soit super-utilisateur ;

Connectez-vous à PostgreSQL :

 psql

Donnez un mot de passe à l’utilisateur que vous venez de créez :

\password nomutilisateur

Quittez PostgreSQL :

\q

Déconnectez vous de l’utilisateur postgres :

exit

Voici quelques commandes PostgreSQL supplémentaires qui vous seront utiles (à utiliser avec un utilisateur ayant les droits pour les exécuter) :

Supprimer un utilisateur :

dropuser nomutilisateur

Créer une base de données :

createdb -O utilisateurpropriétaire nombasededonnées

Supprimer une base de données :

dropdb nombasededonnées

Configurer l’authentification MD5 de PostgreSQL

Par défaut, PosgreSQL utilise la méthode d’authentification peer . Celle-ci utilise le nom de utilisateur voulant se connecter à PostgreSQL comme nom d’utilisateur d’une base de données et n’autorise la connexion que s’il en existe une portant son nom. Cette méthode n’est supportée que pour les connexions locales et n’exige pas de mot de passe.

Vous l’aurez compris, cette méthode n’est pas appropriée pour être utilisée dans une infrastructure et nous allons donc utiliser l’authentification md5. La méthode d’authentification md5 utilise un mot de passe pour se connecter à PostgreSQL et n’est pas limitée aux connexions locales.

La configuration se passe dans le fichier pg_hba.conf situé dans le répertoire /etc/postgresql/9.1/main/ qui est celui qui contrôle l’authentification des clients.

Éditez le fichier pg_hba.conf :

nano /etc/postgresql/9.1/main/pg_hba.conf

Repérez la ligne suivante :

local   all             all                                     peer

Remplacez-là par la ligne suivante :

local   all             all                                     md5

Enregistrez les modifications ;

Redémarrez le service postgresql :

service postgresql restart

Configuration de phppgadmin [Facultatif]

Configurer l’accès distant

Par défaut, phppgadmin est accessible seulement à l’adresse http://localhost/phppgadmin. Si vous installé votre serveur sur une machine sans interface graphique ce sera compliqué d’utiliser l’interface graphique en local. Par conséquent, nous allons configurer le fichier  de configuration apache de phppgadmin, situé dans le répertoire /etc/apache2/conf.d/, pour autoriser l’accès distant.

Éditez le fichier phppgadmin :

nano /etc/apache2/conf.d/phppgadmin

Repérez et dé-commentez la ligne suivante :

# allow from all

Enregistrez les modifications ;

Redémarrez le service apache2 :

service apache2 restart

Vous devriez avoir accès à phppgadmin depuis l’adresse http://AdresseIP/phppgadmin.

Autoriser les connexions à phppgadmin

Par défaut, phppgadmin interdit les connexions utilisateurs depuis un poste distant. Nous allons devoir les autoriser dans son fichier config.inc.php, situé dans le répertoire/usr/share/phppgadmin/conf/, qui gère les connexion à PostgreSQL.

Éditez le fichier config.inc.php :

nano /usr/share/phppgadmin/conf/config.inc.php

Repérez la ligne suivante :

$conf['servers'][0]['host'] = 'localhost';

Supprimez la valeur localhost ;

En laissant la variable, tous les hôtes peuvent accéder à phppgadmin ;

Repérez la ligne suivante :

 $conf['extra_login_security'] = true;

Remplacez sa valeur true par false ;

En plaçant la valeur sur false, nous pourrons nous connecter avec les utilisateur de PostgreSQL ;

Enregistrez les modifications ;

Redémarrez les services postgresql et apache2 :

service postgresql restart && service apache2 restart

Vous pouvez maintenant vous connectez à phppgadmin à l’aide des utilisateurs PostgreSQL (sauf postgres).


Ce tutoriel est terminé. Une suite est prévue et portera sur la réplication de données entre 2 serveurs PostgreSQL.

Si vous avez des problèmes, où que vous trouvez que certains points sont mal expliqués (ou faux), n’hésitez pas à m’avertir.