PostgreSQL

PostgreSQL, souvent connu simplement sous le nom de Postgres, est un système de gestion de base de données relationnelle open source. PostgreSQL version 9.6 est à ce jour la dernière version de PostgreSQL disponible dans les dépôts Debian.

Installation de PostgreSQL

Commencez par mettre à jour l’index de paquet local :

# apt update

Installation des paquets PostgreSQL et PostgreSQL-contrib qui fournit des fonctionnalités supplémentaires.

# apt install postgresql postgresql-contrib

Une fois l’installation terminée, le service PostgreSQL démarrera automatiquement. Pour vérifier l’installation il est possible d’afficher la version installée.

# psql --version    
psql (PostgreSQL) 9.6.11

Rôles et méthodes d’authentification

PostgreSQL gère les autorisations d’accès à la base de données en utilisant le concept de rôles. Un rôle peut représenter un utilisateur ou un groupe d’utilisateurs de base de données. Les méthodes d’authentification les plus couramment utilisées sont :

• Trust - connexion sans mot de passe
• Password - connexion avec mot de passe (scram-sha-256, md5, texte clair)
• Ident - pour les connexions TCP/IP
• Peer - pour les connexions TCP/IP locales

L’authentification du client PostgreSQL est définie dans le fichier de configuration nommé pg_hba.conf. Par défaut PostgreSQL est configuré sur “autoriser les connexions locales”.

L’utilisateur postgres est créé automatiquement, c’est le super-utilisateur de PostgreSQL et pour se connecter à la base il faut d’abord basculer sur ce dernier et ensuite accéder à l’invite PostgreSQL à l’aide de l’utilitaire psql :

# su - postgrespsql    

Puis ensuite lancer la commande psql :

$ psql (9.6.11)
Type "help" for help.

postgres=# 

Création de rôles

Création d’une database et d’un user

Se connecter avec PostGre :

# su - postgres
postgres=# CREATE USER john WITH PASSWORD '4EnPommes!';
CREATE ROLE

Normalement l’utilisateur à changé :

# psql 
postgres=# CREATE DATABASE elephant;
CREATE DATABASE

Remplir sa SBDD

Se reconnecter à sa BDD :

# psql -d elephant
postgres=# CREATE TABLE users (nom text NOT NULL,age int NOT NULL);
CREATE TABLE
postgres=# INSERT INTO users (nom, age) VALUES ('Ganesh', 108),('Tantor', 82),('Babar', 32),('Murderous Mary', 625);
INSERT 0 4

Autoriser un user à se connecter :

elephant=# alter table users owner to john;
ALTER TABLE

Connexion depuis un hôte distant

Par exemple avec un framework comme PhpStorm.

Il faut modifier 2 fichiers, le premier /etc/postgresql/9.6/main/postgresql.conf en changeant l’adresse découte :

# - Connection Settings -
listen_addresses ='*'

Le second fichier à modifier est /etc/postgresql/9.6/main/pg_hba.conf en modifiant :

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

Et relancer/recharger le tout :

# systemctl restart|reload postgresql.service 

Vérification :

# netstat -anp | grep 5432
tcp      0       0   0.0.0.0:5432    0.0.0.0:*     LISTEN    12045/postgres      
tcp6     0       0   :::5432         :::*          LISTEN    12045/postgres      
unix     2   [ACC]   STREAM          LISTENING     46167734  12045/postgres  /var/run/postgresql/.s.PGSQL.5432

Documentation

https://serverfault.com/questions/110154/whats-the-default-superuser-username-password-for-postgres-after-a-new-install#325596
https://pgsnake.blogspot.com/2010/07/postgresql-passwords-and-installers.html
https://digitalisation/community/tutorials/how-to-install-and-use-postgresql-9-4-on-debian-8