memo postgresql.md

Mémo d'introduction à PostgreSQL pour les nuls

Dans un premier temps, on part du principe que l'utilisateur travaille en local, i.e. gère des bases de données (BDDs) locales sur une machine où il a tout contrôle. Les points à adapter dans le cas d'une base de donnée distante, ou d'une utilisation sans droits d'admin, sont traités dans la section correspondante.

La doc de PostgreSQL, admirablement bien faite, lisible et accessible : https://www.postgresql.org/docs/current/static/tutorial.html

Les bases

On décrit dans cette section les quelques notions indispensables pour ne pas raconter n'importe quoi, et comprendre comment on peut gérer une BDD locale.

Tout d'abord, installer postgresql (version 9.5 ou 10 en 2018-10), via apt/pacman/brew/le package manager de votre choix.

sudo apt install postgresql

Cela fait des choses un peu bizarres, c'est normal ; voir la section sur l'utilisateur "postgres".

Clients et serveurs PostgreSQL

On peut voir PostgreSQL comme un protocole, un peu comme HTTP dans le sens suivant : un client envoie une requête PostgreSQL à un serveur, et celui-ci répond en envoyant une réponse PostgreSQL. (La différence avec HTTP est qu'il y a une connection persistante : avant de pouvoir échanger des requêtes et des réponses, le client doit d'abord se connecter.)

En installant postgresql, on installe à la fois un serveur PostgreSQL, et un client minimaliste, psql.

Clients

Toutes les applications permettant de se connecter à un serveur, d'envoyer des requêtes et de recevoir des réponses sont appelées des clients, notamment psql. Il existe d'autres types de client, par exemple pgAdmin, une application avec GUI (interface graphique). Concrètement, pgAdmin et psql ont le même rôle malgré des apparences très différentes : envoyer des requêtes, recevoir des réponses.

Serveurs

Ce qu'on appelle le serveur PostgreSQL, dans le cas où on travaille en local, est le process "postgres" (avec son pid, son path etc.) (voir ps aux | grep sql). Donc par exemple, sudo service postgres restart, c'est littéralement dire à System V "redémarre le serveur PostgreSQL". C'est le serveur qui exécute et répond aux requêtes.

Base de données

Un serveur est un gestionnaire de base de données. C'est pourquoi, par exemple, quand on utilise un client PostgreSQL, il faut préciser à quelle base de donnée on souhaite se connecter.

L'utilisateur postgres

Constater (si tout s'est bien passé) que l'installation a eu l'effet suivant :

• Au cours de l'installation, un utilisateur Unix "postgres" devrait avoir été créé.
• C'est cet utilisateur qui aura tous les droits sur tout : administration des droits des autres utilisateurs sur les BDD, droits admin sur toutes les BDD...
  • En fait, c'est le rôle "postgres" associé à l'utilisateur Unix "postgres" qui aura les droits sur tout -- voir section suivante.
• Si cet utilisateur n'a pas été créé... regarder sur internet.

"Utilisateur Unix" vs "rôles PostgreSQL"

D'après la doc de postgresql.org :

Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required

Every connection to the database server is made using the name of some particular role, and this role determines the initial access privileges for commands issued in that connection. The role name to use for a particular database connection is indicated by the client that is initiating the connection request in an application-specific fashion. For example, the psql program uses the -U command line option to indicate the role to connect as.

Many applications assume the name of the current operating system user by default (including createuser and psql). Therefore it is often convenient to maintain a naming correspondence between roles and operating system users.

Administrer et manipuler des BDDs avec psql

On distingue deux types de console : la console usuelle Unix (bash par exemple), et le terminal de postgresql.

• Le terminal de postgresql s'appelle "psql", on peut le lancer depuis la console Unix par la commande psql. Il sert à faire tout : des SELECT, des INSERT etc., mais aussi CREATE ROLE, ALTER ROLE... Il attend en input des commandes SQL, mais interprète aussi des commandes de la forme \l, \dt, \c... pour faire des choses particulières : "list all databases", "list tables", "connect to this database"...
• En plus de psql, il existe d'autres commandes bash concernant PostgreSQl, comme createuser, createdb etc. On peut les voir comme des wraparounds de psql, qui permettent simplement d'éviter d'avoir à se connecter à un terminal psql pour faire certaines opérations communes.

Connection au serveur avec psql

Pour se connecter à un terminal "psql" en tant que le rôle "postgres", plusieurs possibilités, selon le mode d'authentification choisi :

• si on utilise "md5 authentication" : psql -U postgres puis rentrer le mot de passe du rôle "postgres". Ceci fonctionne quel que soit l'utilisateur Unix exécutant la commande.
• si on utilise "peer authentication" : il faut être connecté en tant que l'utilisateur Unix "postgres" (par exemple, sudo -u postgres -s pour ouvrir un shell en tant que "postgres"), puis tout simplement psql. C'est le système d'exploitation qui vous authentifie auprès du serveur PostgreSQL.
  • C'est le mode par défaut (probablement, en tout cas pour moi ce l'était).

On choisit le mode d'authentification de psql en modifiant pg_hba.conf (voir solution ici).

Quelques petites remarques :

• sur MD5 : comme vous le savez, le hashage MD5 est complètement craqué (en 2018-10), donc il ne faut surtout pas l'utiliser pour stocker des mdp encryptés -- mais pas d'inquiétude, ce n'est pas le cas. Ce que signifie cette méthode est que le mdp est hashé avant d'être envoyé du client vers le serveur. C'est utile lorsque le serveur est sur une machine distante, car alors le mdp passe sur le réseau et risque d'être sniffé.
• sur "peer authentication" : dans ce scénario, c'est votre système d'exploitation (OS) qui garantit votre authentification auprès du serveur PostgreSQL. Bien sûr, ceci ne marche que si le serveur peut faire confiance à votre OS, donc que si vous utilisez un serveur stocké sur votre propre machine.
• pour plus d'information, voir la section "authentification" de la doc.

Exemples de connection psql

Pour se connecter à une BDD avec psql et accéder au terminal psql :

• Scénario basique :

john@pc-de-john:~$ psql ma_bdd 
# se connecter à la BDD "ma_bdd" en tant que le rôle PostgreSQL "john"

• Se connecter en tant qu'un rôle différent de son identité en tant qu'utilisateur Unix :

john@pc-de-john:~$ psql ma_bdd -U postgres
# -U: se connecter à la BDD "ma_bdd" en tant que le rôle PostgreSQL "postgres"

john@pc-de-john:~$ psql ma_bdd -U pika
# ... en tant que le rôle PostgreSQL "pika"

• Si on utilise "peer authentication" (il faut l'avoir spécifié dans le pg_hba.conf !) :

john@pc-de-john:~$ sudo -u postgres -s
# -u: se connecter en tant que *l'utilisateur Unix* "postgres"
# -s: ouvrir un shell (interactif, non-login)
# Entrer le mdp de john
postgres@pc-de-john:~$ psql ma_bdd
# Si tout s'est bien passé, vous êtes maintenant dans un terminal psql, connecté à ma_bdd en tant que le *rôle* postgres :)

john@pc-de-john:~$ sudo -u pika -s
sudo: unknown user: pika
sudo: unable to initialize policy plugin
# bien sûr, pour les *rôles* ne correspondant pas à des *utilisateurs Unix*, il ne faut pas essayer de se connecter avec le mode "peer authentication"...

• Petit piège à éviter : quand on ne spécifie pas la BDD cible, psql essaie de se connecter à une BDD du même nom que le rôle

john@pc-de-john:~$ psql -U pika
# pas de BDD spécifiée: se connecter à la BDD "pika" en tant que le rôle PostgreSQL "pika"
psql: FATAL:  database "pika" does not exist
john@pc-de-john:~$ psql ma_bdd -U pika
# se connecter à ma_bdd en tant que "pika"

# réussite :D
Password for user pika:
psql (9.5.14)
Type "help" for help.

ma_bdd=>

Commandes utiles pour psql

Pour sortir de psql, entrer \q. (^D marche aussi.)

Commandes utiles de psql (voir aussi ce cheat sheet postgresql pas trop mal) (sinon il y en a plein d'autres sur internet). ) :

• \q: Quit/Exit
• \l: List databases
• \c __database__: Connect to a database
• \d __table__: Show table definition including triggers, i.e. describes table (Use \d+ to show even more stuff)
• \dt *.*: List tables in current database (\dt *.* to list tables from all schemas)
• \dn: List schemas (je recommande ne pas y toucher -- voir section suivante)
• \x: Pretty-format query results instead of the not-so-useful ASCII tables

User Related:

• \du: List users
• \du __username__: List a username if present.

"Schémas PostgreSQL"

Le/la lecteur(rice) attentif(ve) aura remarqué que certaines de ces commandes parlent de schemas. C'est une fonctionnalité avancée à laquelle je recommande de ne pas toucher du tout, à notre niveau. Pour les curieux, voir les explications de la doc.)

Créer un nouvel rôle

Il est utile de s'habituer à travailler "en tant que" un utilisateur (en l'ocurrence un rôle) qui n'a pas tous les droits sur tout, parce que c'est ce qui se passe dans la vraie vie.

• https://www.postgresql.org/docs/current/static/database-roles.html
• https://www.postgresql.org/docs/current/static/role-attributes.html

Il est donc recommandé de faire soit un createuser dans un terminal bash, soit un CREATE ROLE rolename WITH LOGIN PASSWORD 'rolepw'; dans un terminal psql, avant de créer des BDDs. Bien penser à donner les permissions idoines à ce nouveau rôle (typiquement, se connecter au serveur avec un mot de passe : "LOGIN" et "PASSWORD", créer des bases de données : "CREATEDB"...). Si on a oublié, on peut toujours faire ALTER ROLE rolename WITH ....

Une fois un nouveau rôle créé, il suffit de faire comme quand on est connecté en tant que postgres. Petite subtilité : à moins que vous vouliez travailler avec un rôle qui matche exactement votre identité en tant qu'utilisateur Unix, il ne faut pas utiliser le mode d'authentification "peer". Il faut donc potentiellement modifier pg_hba.conf pour spécifier au serveur PostgreSQL quel sera le mode d'authentification utilisé.

Créer une nouvelle base de données

Comme pour créer un nouveau rôle, deux possibilités : createdb dans un terminal bash, ou CREATE DATABASE db_name dans un terminal psql. Il est possible, mais pas nécessaire, de rajouter des options, qui seront modifiables après la création avec ALTER DATBASE.

En savoir plus sur PostgreSQL

Vous êtes prêt à lire le chapitre 1 de la documentation : https://www.postgresql.org/docs/current/static/tutorial-arch.html

A noter qu'en fait, seul le chapitre 1 (Tutorial) est vraiment à lire. Les autres chapitres sont présents pour référence (c'est une documentation après tout).

Table des matières de la documentation:

Preface I. Tutorial 1. Getting Started 2. The SQL Language 3. Advanced Features II. The SQL Language III. Server Administration IV. Client Interfaces V. Server Programming VI. Reference VII. Internals VIII. Appendixes Bibliography Index

Et si on travaille sur une BDD distante ?

Exactement le même principe, sauf qu'on n'a plus la possibilité d'être connecté en tant que (rôle de) l'administrateur principal postgres. On doit travailler en tant qu'un rôle spécifique au projet concerné (ce qu'il est recommandé de faire même en travaillant en local d'ailleurs).

De plus, le choix du mode d'authentification (pg_hba.conf, sur le serveur distant) doit être par mot de passe.

A noter que, pour le BR, il sera très rare de travailler sur une BDD distante. En effet,

• pour les applications web, elles sont le plus souvent hébergées sur le même serveur où est hébergé le serveur PostgreSQL.
• pour administrer une BDD, le BRman se connecte en ssh au serveur pour faire ses manips, donc les requêtes PostgresQL sont effectuées à partir de sa session i.e. déjà sur le serveur.

Et maintenant ?

Vous savez à présent comment créer une base de données PostgreSQL et faire les manips de base. Vous devriez avoir, ou savoir comment créer, un utilisateur sigma_dev et une bdd vide sigma (si vous travaillez sur le projet sigma par exemple).

Maintenant il faut mettre des choses dans cette bdd... L'option hardcore/débile consiste à tout faire à la main par des commandes SQL dans le terminal psql : créer des tables, et spécifier les colonnes qu'elles doivent avoir, et spécifier les paramètres des colonnes (not nullable, default, autoincrement...), puis INSERT des entrées aussi à la main.

Certains outils permettent de le faire de façon intelligente : ils permettent

• de spécifier, dans un fichier, les tables (et leurs colonnes, et leurs paramètres) que vous souhaitez. Ce fichier s'appelle "fichier migration". On dit qu'on choisit le "schéma de la base de données".
• de spécifier, dans un autre fichier, des entrées initiales pour votre bdd. Ce fichier s'appelle "fichier seed".

Par exemple, c'est ce que permet de faire Knex.js, utilisé par le projet sigma notamment. Mais ces deux notions sont plus générales. Par exemple, Symfony (le framework PHP) sait construire des fichiers migrations (i.e. choisir le schéma de votre bdd) automatiquement, en lisant les paramètres spécifiés dans les annotations de vos Entity.php. Django fait aussi un truc similaire.

Un memo Knex.js est disponible... euh... quelque part, il devrait s'appeler "memo knexjs".