PostgREST

Traduction réduite et adapté des tutoriels https://postgrest.org/en/stable/tutorials/tut0.html et https://postgrest.org/en/stable/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)

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.

docker container start tutorial

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.

sudo apt-get install libpq-dev

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');

ALTER DATABASE nom_de_la_base SET timezone TO 'Europe/Paris';
# la commande ci-dessous ne le fait que pour la SESSION en cours :
SET TIME zone 'Europe/Paris'; # ou 'CET' = Centre Europe
# les commandes suivantes permettent de vérifier avant/après :
SHOW timezone; # par défaut -> UTC
SELECT NOW(); # UTC : 2023-10-11 08:12:13.955544+00 et en CET (en été) : 2023-10-11 10:12:13.955544+02

[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 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

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"
}

Dans la partie précédente, nous avons juste défini un utilisateur anonyme limité en lecture.
Pour écrire, il faut définir 2 niveaux de protection, le premier est un utilisateur sig_user avec des droits d'accès en écriture sur la base PostgreSQL, le second est un token JWT d'accès à l'API PostgREST. Au final, l’instanciation du conteneur PostgREST recevra donc 2 mots de passe distinct, celui de l'utilisateur de la base (ou plus exactement du schéma), et celui de token.

Cette partie s'inspire très largement du tuto https://postgrest.org/en/v7.0.0/tutorials/tut1.html

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; # <------------- outre la table, il faut aussi les droits sur la séquence !!

GRANT SELECT ON ALL TABLES IN SCHEMA location TO sig_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA location TO sig_user;

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. Comme les clients ne connaissent pas le mot de passe, ils ne peuvent pas altérer le contenu de leurs jetons. PostgREST détectera les jetons contrefaits et les rejettera.

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>"

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.

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"

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/

dockeriser postgresql/postgis/postgrest

Jointure et insertion de lieux avec coordonnées géographique (geometry)