====== PostgREST ======

[[https://postgrest.org/en/v6.0/|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 [[https://g-ernaelsten.developpez.com/tutoriels/PostgREST/|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.\\
{{https://postgrest.org/en/v7.0.0/_images/tut0-request-flow.png |}}

À 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//)

===== Quelques bases =====
  * Schéma de principe de l'architecture (EN) : https://postgrest.org/en/stable/explanations/architecture.html
  * Schéma d'isolation préconisé (EN) : https://postgrest.org/en/stable/explanations/schema_isolation.html 
  * Lien (EN) sur la page d'installation : https://postgrest.org/en/stable/explanations/install.html
===== Installer PostgreSQL =====
Pour ce tuto, nous allons instancier un conteneur [[informatique:devops:docker|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 <code bash>sudo systemctl stop postgresql</code>

Voici la ligne de commande pour instancier et faire tourner le conteneur postgresql :\\
<code bash>
docker run --name tutorial -p 5433:5432 \
           -e POSTGRES_PASSWORD=mysecretpassword \
           -d postgres:17-alpine
</code>

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.

<note tip>Si vous avez des données géographiques, il faut PostGIS… remplacer en fin de ligne de commande ci-dessus //postgres// par //postgis/postgis:13-3.5-alpine// ou //postgis/postgis:17-3.5-alpine// les version 14, 15, 16 existent aussi, cf. [[https://hub.docker.com/r/postgis/postgis/tags?page=1&ordering=last_updated|page postgis sur dockerhub]]\\
On peut préférer aussi l'image //postgres:17-alpine// au lieu de la //postgres//, cette dernière est basée sur debian
</note>
<note important>Si vous coupez/redémarrez votre PC, il faudra redémarrer le conteneur : <code bash>docker container start tutorial</code></note>
===== 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/v12.2.3/postgrest-v12.2.3-linux-static-x64.tar.xz]]. Vous obtenez un binaire compressé .tar.xy, décompressez-le :
<code bash>tar xJf postgres-v7.0.1-linux-x64-static.tar.xz</code> 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.

<note important>PostgREST requière la librairie libpq qui peut-être installé via
<code bash>sudo apt-get install libpq-dev</code></note>

===== Création de la base de données =====
Connectez-vous à la ligne de commande du conteneur:
<code bash>docker exec -it tutorial psql -U postgres
# qui doit retourner :
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.

postgres=#
</code>

Pour commencer, on crée un schéma (dans la base de données crée par défaut postgres) :
<code sql>
-- 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');
</code>

<note important>Si vous avez des dates (created_at, updated_at…) il est utile de définir le fuseau horaire : 
<code sql>
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</code>
</note>

[FACULTATIF] Si on a utilisé une image docker PostGIS, on peut ajouter une colonne geometry :
<code sql>
-- 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);
</code>

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 :
<code sql>
create role web_anon nologin;

grant usage on schema location to web_anon;
grant select on location.town to web_anon;
</code>
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// :
<code sql>
create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;
</code>

Vous pouvez quitter le ligne de commande //psql// du conteneur :
<code bash>\q</code>

<note tip>Il est possible d'utiliser un outil de gestion graphique, comme [[https://doc.ubuntu-fr.org/dbeaver|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</note>
===== 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 :
<file - tutorial.conf>
db-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres"
db-schema = "location"
db-anon-role = "web_anon"
</file>

Pour lancer le serveur :
<code bash>./postgrest tutorial.conf
# qui doit retourner :
Attempting to connect to the database...
Listening on port 3000
Connection successful
</code>

<note important>Si vous coupez/redémarrez votre PC, il faudra évidemment redéfinir la variable d'environnement $TOKEN (export…) et redémarrer postgrest</note>

Le serveur PostgREST est fonctionnel, vous pouvez lancer, soit dans votre navigateur <code>http://localhost:3000/town</code> ou en ligne de commande :
<code bash>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]}}]
</code>

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 :
<code bash>
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"
}
</code>

<note tip>Pour tester les requêtes on peut utiliser [[https://curl.se|curl]], et éventuellement faire un pipe sur //json query// (jq) pour que soit bien plus lisible le JSON retourné.\\ Il y a aussi l'utilitaire [[https://www.usebruno.com/|bruno]], [[https://insomnia.rest/download|Insomnia]], et également Postman qui pousse plutôt la solution web, mais propose également un [[https://learning.postman.com/docs/getting-started/installation-and-updates/#installing-postman-on-linux|client lourd]], la [[https://github.com/usebruno/bruno|version gratuite de Bruno]] est Open Source !</note>
===== Utilisateur autorisé en écriture =====

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

==== Création utilisateur et AFFECTATION DES DROITS ====
<code sql>
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 !!
</code>
<note warning>Lors du déploiement, les droits seront à définir sur toutes les tables, vues et fonctions utiles :
<code bash>
GRANT SELECT ON ALL TABLES IN SCHEMA location TO sig_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA location TO sig_user;
</code></note>
==== Création du jeton (token) pour sécuriser ====
<note>pour mieux comprendre les JWT, voici un tuto de 11mn : https://grafikart.fr/tutoriels/json-web-token-presentation-958\\
La première partie du token correspond à l'en-tête en JSON, c'est à dire //{"alg": "HS256","typ": "JWT"}//, la seconde le //payload//, dans notre cas //{"role":"sig_user"}//, et la troisième est la signature du token, définit à partir des 2 premières parties et du mot de passe.\\
Voir aussi, pour aller plus loin : https://www.rfc-editor.org/rfc/rfc7519</note>
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 :
<code bash>
# 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
</code>

Mettre à jour le fichier //tutorial.conf// en y ajoutant ce mot de passe:
<file - tutorial.conf>
# PASSWORD MUST BE AT LEAST 32 CHARS LONG
# add this line to tutorial.conf:

jwt-secret = "<the password you made>"
</file>
<note tip>Si on crée le serveur postgREST via un conteneur Docker, au lieu de ce fichier, on précise ce mot de passe via la clé //PGRST_JWT_SECRET// dans le docker-compose.yaml</note>
==== Création du jeton JSON Web Token (JWT) ====
Plus d'informations sur les [[wpfr>JSON_Web_Token]]\\
Le tutoriel de [[https://grafikart.fr/tutoriels/json-web-token-presentation-958|grafikart]]\\
Rendez-vous sur [[https://jwt.io/#debugger-io|jwt.io]] puis suivez-les instructions comme sur le schéma:\\
{{https://postgrest.org/en/v7.0.0/_images/tut1-jwt-io.png}}

À quelques adaptations près :
  - À la place du //HEADER//, il faut avoir le JSON : {"typ":"JWT","alg":"HS256"}
  - Saisir le texte //PAYLOAD// JSON : "role": "sig_user"
  - À la place de //secret//, saisissez le mot de passe créé au § précédent.
  - 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 :
<code bash>
# mémoriser le token
export TOKEN="eyJhbGciOiJIUzI1NiJ9.eyJyb2xlIjoic2lnX3VzZXIifQ.Q5gbPnMVYUwSxdjFzU3aMm2RVUAXN5dyfCc8__OVFaI"
</code>
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// :
<code bash>
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"
</code>

===== 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 :
<file 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();
</file>
source : [[https://weichie.com/blog/curl-api-calls-with-php/]]
<note tip>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  </note>

===== Dockeriser le tout =====

[[:informatique:api-rest:postgrest-dockeriser|dockeriser postgresql/postgis/postgrest]]

===== Pour aller plus loin, jointures… =====
[[:informatique:api-rest:postgrest-jointure-postgis|Jointure et insertion de lieux avec coordonnées géographique (geometry)]]