Outils pour utilisateurs

Outils du site


informatique:api-rest:postgrest

PostgREST

PostgREST

Traduction réduite et adapté des tutoriels https://postgrest.org/en/v6.0/tutorials/tut0.html et https://postgrest.org/en/v7.0.0/tutorials/tut1.html (voir aussi la série sur https://gis-ops.com/postgrest-tutorial-installation-and-setup/ et https://gis-ops.com/postgrest-postgis-api-tutorial-geospatial-api-in-5-minutes/)
Voir aussi tuto sur developpez.com qui parle aussi de l'accès en PHP depuis Symfony/Laravel…

PostgREST est un serveur web autonome qui offre une solution d'API RESTful sur une base de données PostgreSQL.

À la fin de ce tutoriel, vous aurez une base de donnée PostgreSQL fonctionnelle, un serveur PostgREST, et une API sur les villes d'une base de données de lieu (location)

Installer PostgreSQL

Pour ce tuto, nous allons instancier un conteneur docker (docker doit donc être installé sur votre machine que l'on nommera désormais l'hôte). Si vous avez déjà un serveur PostgreSQL sur votre machine, vous pouvez l'utiliser mais si vous suivez l'utilisation du conteneur docker, arrêtez votre serveur

sudo systemctl stop postgresql

Voici la ligne de commande pour instancier et faire tourner le conteneur postgresql :

docker run --name tutorial -p 5433:5432 \
           -e POSTGRES_PASSWORD=mysecretpassword \
           -d postgres:13-alpine

Cela va faire tourner une instance docker et exposer le port 5433 sur l'hôte depuis lequel on pourra accéder au serveur PostgreSQL.

Si vous avez des données géographiques, il faut PostGIS… remplacer en fin de ligne de commande ci-dessus postgres par postgis/postgis:12-3.0-alpine ou postgis/postgis:13-3.1-alpine, cf. page postgis sur dockerhub
On peut préférer aussi l'image postgres:13-alpine au lieu de la postgres, cette dernière est basée sur debian
Si vous coupez/redémarrez votre PC, il faudra redémarrer le conteneur :
docker container start tutorial

Installer PostgREST

PostgREST est diffusé comme un simple programme binaire, avec les versions compilées pour les principales distributions. Le lien pour y accéder est : https://github.com/PostgREST/postgrest/releases/latest. Pour Ubuntu : https://github.com/PostgREST/postgrest/releases/download/v7.0.1/postgrest-v7.0.1-linux-x64-static.tar.xz. Vous obtenez un binaire compressé .tar.xy, décompressez-le :

tar xJf postgres-v7.0.1-linux-x64-static.tar.xz

qui va extraire le seul fichier postgrest. Il peut être exécuté là où il est, ou le déplacer dans /usr/local/bin pour pouvoir l'exécuter de n'importe quel répertoire de l'hôte.

PostgREST requière la librairie libpq qui peut-être installé via
sudo apt-get install libpq-dev

Création de la base de données

Connectez-vous à la ligne de commande du conteneur:

docker exec -it tutorial psql -U postgres
# qui doit retourner :
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.
 
postgres=#

Pour commencer, on crée un schéma (dans la base de données crée par défaut postgres) :

-- si on a créé une base de données, il faut d'abord la sélectionner : \c nom_de_la_base
CREATE schema location;
 
-- puis créons la table town (ville)
 
CREATE TABLE location.town (
  town_id serial PRIMARY KEY,
  town_name VARCHAR(256) NOT NULL
 );
 
-- et insérer quelques entrées :
 
INSERT INTO location.town (town_name) VALUES
  ('Paris'), ('Lyon'), ('Grenoble'), ('Brest');

[FACULTATIF] Si on a utilisé une image docker PostGIS, on peut ajouter une colonne geometry :

-- au standard pseudo-mercator (comme OpenStreetMap) EPSG 3857
ALTER TABLE location.town ADD COLUMN geom geometry(Geometry,3857);
ALTER TABLE location.town ADD CHECK (st_ndims(geom) = 2);
ALTER TABLE location.town ADD CHECK (geometrytype(geom) = 'POINT'::text);
ALTER TABLE location.town ADD CHECK (st_srid(geom) = 3857);
-- et pour insérer un point (au besoin, pour changer de coordonnées : https://epsg.io/transform ) :
-- url complète de transformation WGS84 -> EPSG 3857 : https://epsg.io/transform#s_srs=4326&t_srs=3857&x=4.8336000&y=45.7575000
INSERT  INTO "location".town (town_name, geom) VALUES ('Paris', ST_GeomFromText('POINT(260420.82 6250767.38)', 3857));
INSERT  INTO "location".town (town_name, geom) VALUES ('La Rochelle', ST_GeomFromText('POINT(-129954.93 5806837.36)', 3857));
 
-- ou en conservant les coordonnées au format géographique WGS84 :
ALTER TABLE location.town ADD COLUMN geom geometry(Geometry,4326);

Il nous faut maintenant créer un rôle anonyme pour les requêtes web. Quand la requête arrive au serveur, PostgREST basculera sur ce rôle pour lancer la requête sur la base :

CREATE ROLE web_anon nologin;
 
GRANT usage ON schema location TO web_anon;
GRANT SELECT ON location.town TO web_anon;

Le rôle web_anon a la permission d'accéder à la table town de la base location

Nous créons aussi le rôle authenticator pour éviter d'utiliser postgres :

CREATE ROLE authenticator noinherit login password 'mysecretpassword';
GRANT web_anon TO authenticator;

Vous pouvez quitter le ligne de commande psql du conteneur :

\q
Il est possible d'utiliser un outil de gestion graphique, comme dbeaver.
Pour la connexion, si on a suivi ce tuto, on a :
Host: localhost Port: 5433
Database: postgres
Nom d'utilitateur: postgres
Mot de passe: mysecretpassword

Démarrer le serveur PostgREST

Il faut tout d'abord créer un fichier de configuration minimal, d'accès à la base de données :

tutorial.conf
db-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres"
db-schema = "location"
db-anon-role = "web_anon"

Pour lancer le serveur :

./postgrest tutorial.conf
# qui doit retourner :
Attempting to connect to the database...
Listening on port 3000
Connection successful
Si vous coupez/redémarrez votre PC, il faudra évidemment redéfinir la variable d'environnement $TOKEN (export…) et redémarrer postgrest

Le serveur PostgREST est fonctionnel, vous pouvez lancer, soit dans votre navigateur

http://localhost:3000/town

ou en ligne de commande :

curl http://localhost:3000/town
# qui doit retourner
[{"town_id":1,"town_name":"Paris"}, 
 {"town_id":2,"town_name":"Lyon"}, 
 {"town_id":3,"town_name":"Grenoble"},
 {"town_id":4,"town_name":"Brest"},
# et si on a ajouté la colonne geom, on peut avoir en plus :
 {"town_id":5,"town_name":"La Rochelle","geom":{"type":"Point","coordinates":[-129954.93,5806837.36]}}]

Avec les permissions donné à notre rôle anonyme, seule des accès en lecture sur la table town sont autorisés.
Si nous essayons une requête d'ajout :

curl http://localhost:3000/town -X POST \
    -H "Content-Type: application/json" \
    -d '{"town": "Rennes"}'
# ça retourne l'erreur 401 Unauthorized
{
  "hint": null,
  "details": null,
  "code": "42501",
  "message": "permission denied for relation todos"
}
Pour tester les requêtes curl Il y a aussi l'utilitaire Insomnia, et également Postman qui pousse plutôt la solution web, mais propose également un client lourd, malheureusement, ni l'un ni l'autre ne sont libres ou Open Source !

Utilisateur autorisé en écriture

Dans la partie précédente, nous avons juste défini un utilisateur anonyme limité en lecture. Nous allons ajouter un utilisateur sig_user identifié pour des accès en écriture sur la base via l'API PostgRest. Cette partie s'inspire très largement du tuto https://postgrest.org/en/v7.0.0/tutorials/tut1.html

Création utilisateur

CREATE ROLE sig_user nologin;
GRANT sig_user TO authenticator;
 
GRANT usage ON schema location TO sig_user;
GRANT ALL ON location.town TO sig_user;
GRANT usage, SELECT ON SEQUENCE location.town_town_id_seq TO sig_user;
Lors du déploiement, les droits seront à définir sur toutes les tables, vues et fonctions :
GRANT SELECT ON ALL TABLES IN SCHEMA location TO sig_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA location TO sig_user;

Création du jeton (token) pour sécuriser

L'authentification du client avec l'API utilise les jetons web JSON (JSON Web Tokens). Ce sont des objets JSON chiffré utilisant un mot de passe connu seulement par nous et le serveur.

Création d'un mot de passe de 32 caractères:
On peut soit le définir soit même ou avec un générateur (le séquestre KeepassXC par exemple), ou en ligne de commande sous linux :

# Allow "tr" to process non-utf8 byte sequences
export LC_CTYPE=C
 
# read random bytes and keep only alphanumerics
< /dev/urandom tr -dc A-Za-z0-9 | head -c32
 
# doit vous retourner une chaîne du type :
IKrlYRigEgAq2re4oRf0TBsybzIacM3H

Mettre à jour le fichier tutorial.conf en y ajoutant ce mot de passe:

tutorial.conf
# PASSWORD MUST BE AT LEAST 32 CHARS LONG
# add this line to tutorial.conf:

jwt-secret = "<the password you made>"

Création du jeton JSON Web Token (JWT)

Plus d'informations sur les JWT
Rendez-vous sur jwt.io puis suivez-les instructions comme sur le schéma:

À quelques adaptations près :

  1. À la place du HEADER, il faut avoir le JSON : {“typ”:“JWT”,“alg”:“HS256”}
  2. Saisir le texte PAYLOAD JSON : “role”: “sig_user”
  3. À la place de secret, saisissez le mot de passe créé au § précédent.
  4. Copier le jeton

Ne pas oublier que le rôle et le mot de passe doivent appartenir à l'utilisateur qui aura accès a la base de données. Si l'utilisateur ne possède pas le rôle ou le mot de passe utilisé lors de la création du TOKEN, l'accès à la base de données lui sera refusé.

En ligne de commande mettre en place le token sur le ou les serveurs qui doivent écrire :

# mémoriser le token
export TOKEN="eyJhbGciOiJIUzI1NiJ9.eyJyb2xlIjoic2lnX3VzZXIifQ.Q5gbPnMVYUwSxdjFzU3aMm2RVUAXN5dyfCc8__OVFaI"

Pour un serveur en production, il faut penser à le rendre persistant (définit en cas de redémarrage) et à le mémoriser dans un séquestre pour pouvoir le réinstaller en cas de problème avec le serveur.

Écrire (CREATE/UPDATE), supprime (DELETE) via l'API

On peut maintenant envoyer la requête suivante pour ajouter la ville de Bordeaux :

curl http://localhost:3000/town -X POST \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -d '{"town_name": "Bordeaux"}'
    -
# supprimer la ville ayant pour id 3 :
curl "http://localhost:3000/town?town_id=eq.3" \
    -X DELETE \
    -H "Prefer: return=representation" \
    -H "Authorization: Bearer $TOKEN"

cUrl dans PHP

Maintenant que notre serveur est en place et que nous avons compris les requêtes avec curl, reste à les implémenter en PHP :

- curl_postgrest.php
<?php
 
$TOKEN="eyJhbGciOiJIUzI1NiJ9.eyJyb2xlIjoic2lnX3VzZXIifQ.Q5gbPnMVYUwSxdjFzU3aMm2RVUAXN5dyfCc8__OVFaI";
$POSTGIS_ACTIVATED = false; // switch to true if you use postgis
 
function getTowns () {
	// Récupération des villes via l'api PostgREST
	$ch = curl_init(); 
	curl_setopt($ch, CURLOPT_URL, "localhost:3000/town");
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	$json_output = curl_exec($ch);
	curl_close($ch);
	$output = json_decode($json_output);
	var_dump($output);
}
 
getTowns();
// écriture dans la base via l'api PostgREST
$town_name = "Strasbourg";
if (!$POSTGIS_ACTIVATED)
	$data = array("town_name" => $town_name);
else {
	$town_geometry = array("type" => "Point", "coordinates" => array(862521.23, 6204397.47)); // exemple lors de la lecture : {"type":"Point","coordinates":[-129954.93,5806837.36]}}
	$data = array("town_name" => $town_name, "geom" => $town_geometry);
}
 
$ch = curl_init(); 
curl_setopt($ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
	"Authorization: Bearer $TOKEN",
	"Content-Type: application/json",
));
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
curl_setopt($ch, CURLOPT_URL, 'localhost:3000/town');
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$output = curl_exec($ch);
var_dump($output); echo "<br>";
curl_close($ch);
 
echo "<br>";
getTowns();

source : https://weichie.com/blog/curl-api-calls-with-php/

Avec DBeaver, si on utilise PostGIS, lors de la visualisation des données d'une table contenant des données géométriques, il y trois onglet verticaux dont un nommé SPATIAL permettant de visualiser les points sur le fond OpenStreetMap

Dockeriser le tout

Pour aller plus loin, jointures…

informatique/api-rest/postgrest.txt · Dernière modification : 2022/07/25 09:48 de bertrand