Wonder what values are set or not inside your new.column_name and old.column_name when you are calling PostgREST with a PATCH request? This article is for you!
Create a private schema for our sample app, we will expose the public schema for our PostgREST API:
create schema private;
A city table:
create table private.city (
city__id integer not null primary key ,
name text not null,
countrycode character(3) not null,
district text not null,
population integer not null
);
Now let's expose this city private table through PostgREST as a public API (public schema) so we stay clean regarding the Separation of Concerns principle:
create view public.cities as
select city__id as id, name, countrycode, district, population
from private.city;
Let's add support for the PATCH HTTP verb on our newly created /cities REST endpoint.
create or replace function private.update_city() returns trigger as
$$
begin
raise exception 'new.name = %, old.name=%, new.countrycode = %, old.countrycode = %', new.name, old.name, new.countrycode, old.countrycode;
return new;
end;
$$ security definer language plpgsql;
create trigger city_update
instead of update
on public.cities
for each row
execute procedure private.update_city();
Now our function private.update_city() will be called for each rows submitted through PATCH /cities HTTP request. As you can see from update_city function body we print the before/after values of name and countrycode columns in PATCH requests.
What's the value of new.countrycode if I don't specify countrycode property in PATCH request body?
As you can see, the property (column in fact) countrycode was not specified in the PATCH request body but it still defined with its current value in new.countrycode and old.countrycode just like we expected it to be.
What's the value of new.name if I set name as a null value in PATCH request body?
Row level security is an awesome feature that let you control how your database (PostgreSQL in my case) manage access to each row of a table based on some policies declared upfront. It's also really useful when you expose your database through a REST API with a gateway like PostgREST. I already talked enough about that:).
I often forgot to add the ALTER TABLE schema.table ENABLE ROW LEVEL SECURITY; statement when I declare row level security policies. Do you?
Let's use our database awesome introspection feature to list tables for which we attached access policies and then automatically activate row level security. The SQL request below list tables and display whether or not row level security is activated.
select pg_class.oid,
pg_namespace.nspname || '.' || pg_class.relname as schema_table,
pg_policy.polname as policy_name,
pg_class.relrowsecurity as has_row_level_security_enabled
from pg_catalog.pg_policy
inner join pg_catalog.pg_class on pg_class.oid = pg_policy.polrelid
inner join pg_catalog.pg_namespace on pg_class.relnamespace = pg_namespace.oid;
oid
schema_table
policy_name
has_row_level_security_enabled
369657
fsm.machine
fsm_machines_access_policy
true
369745
iam.user
user_access_policy
true
369803
actor.company
company_access_policy
true
369803
actor.company
company_access_policy_for_update
true
369842
contract_manager.contract
contract_access_policy
false
From the query output we observe that contract_manager.contract table does have an associated access policy called contract_access_policy but without row level security enabled on the table.
Let's now enable row level security for each table where at least one policy was defined:
update pg_catalog.pg_class
set relrowsecurity = true
where pg_class.oid in (select pg_class.oid
from pg_catalog.pg_policy
inner join pg_catalog.pg_class on pg_class.oid = pg_policy.polrelid
where pg_class.relrowsecurity = false);
This is it! We've activated row level security (not in FORCE mode) to every table with attached policies. No more mistakes. No more boilerplate \o/
Yep. You are wondering why it's working locally and not in production right? Or maybe why PostgREST login feature is not working at all?
The response.headers guc must be a JSON array composed of objects with a single key and a string value is often related to the fact that some call were made to set a header field but the value to be set was empty. Take a look at your settings.secrets table. If it's empty, that's the problem.
At least on the PostgREST-starter-kit, the settings.secrets table should contains at least 2 rows:
jwt_secret
your_secret_here
jwt_lifetime
3600
Looking for some guidance on PostgREST? How to setup a CI/CD with it? What test strategy to use? As a CTO I've built multiple SaaS with it underneath and now help tech teams build fast, reliable and safer API with PostgREST and PostgreSQL. Hire me on CodeMentor or Malt!
DO $$
DECLARE
schema_name INFORMATION_SCHEMA.routines.routine_schema%TYPE = 'api';
fns CURSOR FOR
select routine_name from INFORMATION_SCHEMA.routines WHERE routine_schema = schema_name;
BEGIN
FOR fn_record IN fns LOOP
EXECUTE 'grant execute on function ' || schema_name || '.' || fn_record.routine_name || ' to anonymous;';
END LOOP;
END$$;
Will expose all store functions from the api public schema in the generated swagger/openapi specification from PostgREST/SubZero. Of course, ensure that all underneath private tables have row-level-security enabled to stay secure.
DO $$
DECLARE
schema_name INFORMATION_SCHEMA.views.table_schema%TYPE = 'api';
views CURSOR FOR select table_name from INFORMATION_SCHEMA.views WHERE table_schema = schema_name;
BEGIN
FOR view_record IN views LOOP
EXECUTE 'grant select, insert, update, delete on ' || schema_name || '.' || view_record.table_name || ' to anonymous;';
END LOOP;
END$$;
Will expose all views from the api public schema in the generated swagger/openapi specification from PostgREST/SubZero. Of course, ensure that all underneath private tables have row-level-security enabled.
Depuis juin dernier je souhaitais vous proposer un feedback sur le talk Choisir entre une API RPC, SOAP, REST, #GraphQL et si le problème était ailleurs ? qui avait eu lieu au Web2Day à Nantes.
Comme le veut la coutume, j'en ai profité pour partager quelques stickers d'Image-Charts et de Redsmin!
Bien que le sujet soit touchy — s'attaquer à la doxa concernant les SGBDR et essayer de montrer qu'il y a d'autres voies n'était pas gagné d'avance —, les réactions ont été très positives !
Je confirme : l’une des meilleures conf. sur les API ever. Tant sur le fond, la doctrine, le rappel des fondamentaux. Que sur la forme : directe, décapante et soutenue. Cela mériterait un long article dans Médium par ex. Bravo.
Excellente présentation de @FGRibreau, j'en ressors grandi. Fini l'ère de la duplication d'informations. Aujourd'hui nous devons nous concentrer d'avantage sur la couche data qui offre des multitudes de possibilités. Si vous avez 35min, foncez ! 🚀 https://t.co/cIilNFCnhv
Merci de prêcher cette "database-centric philosophy" 👍 ! Ce n'est pas facile de faire comprendre les intérêts et les possibilités. Les RDBM tels que #PostgreSQL offrent des outils hyper puissants, trop souvent ignorés et sous-exploités.
Si vous n'avez pas pu assister au Web2Day, bonne nouvelle, les replays sont disponibles sur YouTube et voici l'intervention en question :
Questions / Réponses
Ce talk a aussi suscité des questions après la conférence et comme elles me sont souvent posées lorsque j'aborde ces sujets, je profite de ce billet pour y répondre afin d'y référer les prochains interlocuteurs 😁.
Comment sécuriser les appels ?
En fonction de la présence ou non de la JWT lors de l'appel ainsi que des privilèges associés aux rôles sur les vues et fonctions stockées (cf. le talk) les appels nécessiteront ou pas une authentification.
Je suis plus productif avec un ORM qu'en écrivant du SQL directement
Sans entrer dans le sujet ORM (qui d'ailleurs serait un débat passionnant), SQL est partout même là où on ne l'attend pas que cela soit en Big Data (Kafka KSQL, Spark, Flink) ou en NoSQL (Elasticsearch SQL, Cassandra CQL). Ceci pour expliciter que l'argument selon lequel un développeur n'aurait pas besoin de connaitre SQL est aussi caduque que la pensée qu'un développeur ne doit pas savoir écrire des tests ou connaitre OWASP top 10.
Les SGBDR et plus précisément PostgreSQL ont des fonctionnalités et permettent d'être déclaratif sur certaines problématiques comme la gestion des droits plutôt que de devoir re-développer à chaque projet un système from scratch. L'ORM ne résout pas le sujet des autorisations et même (c'est là où la discussion est intéressante), même si nous trouvions un ORM gérant les autorisations, le problème fondamental est qu'elles seraient gérées au niveau de la couche applicative, laissant, en cas de faille (e.g. escalade de privilège), un accès complet à la base. C'est l'intérêt de travailler sur les couches les plus basses pour avoir des leviers (ici la sécurité) sur les couches supérieures.
Quid de la gestion des droits multi-tenant et de la séparation read/write ?
Ça devient plus compliqué quand il faut différencier read/write et ajouter la gestion des droits multi-tenant.
Gérer le multi-tenant avec une approche déclarative — en comparaison à une approche impérative — permet de respecter single-source-of-truth (il est structurellement bien plus difficile d'avoir la garantie qu'une règle d'autorisation sera appliquée — de manière future-proof — sur l'intégralité d'une code base lorsque l'on passe par du code). Dans le cas de RLS (Row Level Security), limiter un utilisateur pour qu'il ne voit que ses propres éléments dans la table X, peu importe comment elle sera accédée dans le futur (avec jointure ou pas) revient uniquement à déclarer using (user_id = request.user_id()) ou encore, pour limiter l'accès au niveau d'une organisation using (organization_id = request.organization_id()).
À noter qu'il n'est pas obligé de mettre les identifiants d'utilisateur, d'organisation ou de groupe en colonne de chaque table car ces informations sont très souvent déjà présentes au niveau de la JWT, la mise en place d'une function stable pour accéder à ces attributs à join est aussi possible et tout aussi efficace.
Pour la gestion des accès en écriture (à savoir create, update, delete), toujours en RLS, with check prend la relève et offre une approche unifiée permettant ainsi de distinguer — si souhaité — les accès entre les opérations de lecture (avec using) et d'écriture (avec with check).
Comment gérer les montées en version ?
De la même manière qu'une API doit la plupart du temps supporter N et N-1. Dans le cas d'une breaking, la montée de version nécessite d’avoir une compatibilité descendante. Cela passe (cf. le début du talk) par la mise en place d'un second schéma (e.g. v2), avec des vues et fonctions stockées qui pointent vers les schémas privés et qui retournent les données sous le nouveau format.
Il faut noter que cette notion de (1..N) schémas publiques et (1..N) schémas privés permet une flexibilité dans les développements et les itérations tout en respectant le principe de Single Source of Truth.
J'ai peur que le "code" finisse par n'être qu'une liste de scripts de migration ?
Pour compléter la réponse précédente, effectivement, il a été un temps où tout n'était que migration. Et c'est problématique car une suite de fichiers SQL représentant les migrations sont bien plus difficile à suivre qu'un ensemble de fichier SQL représentant l'intégralité de l'état final des schémas de BDD. Fort heureusement des outils apparaissent et permettent de se concentrer uniquement sur l'état final et de laisser l'outil générer les migrations entre l'état précédent et le nouvel état (cf. question ci-dessous sur "Comment bien démarrer"). Le(s) développeur(s) se concentrent donc uniquement sur sa version cible et laisse l'outil gérer les diffs. C'est une approche saine que l'on retrouve d'ailleurs dans d'autres domaines comme celui de la gestion de configuration d'infrastructure (e.g. Terraform).
Comment scale ?
Il y a deux réponses à cette question. La première concerne le scaling de l'API (PostgREST ou Subzero, ...) et ici il s'agit de scaling horizontal classique.
La seconde question qui vient juste après concerne le scaling de la base de données. Mais avant de parler de cela, il nous faut ici rappeler un point fondamental qui est souvent oublié. Sur le modèle présenté ici (API passe-plat vers la BDD), la base de données fait autant de travail que dans une approche 3 tiers, c'est même souvent (et c'est structurel) plus performant. Pourquoi ? Parce qu'au lieu de N requêtes à la BDD (authentification, autorisations, lectures ou écritures multiples) depuis une requête HTTP vers l'API, dans le modèle présent tout est résolu au sein d'une même requête, donc pas d'overhead sur l'aller/retour avec la BDD.
Ensuite, nous sommes en 2018, PostgreSQL est maintenant capable de gérer des quantités astronomiques de données (10 To chez Leboncoin par exemple). Quand je vois la taille des datasets des entreprises, dans l'immense majorité, cela ne dépasse pas la dizaine voir la centaine de Go, ce qui est un non sujet avec PostgreSQL. Encore une fois, comme je le dis dans le talk, ce style d'architecture permet de traiter environ 80% des usages en entreprise. Pour ces 80% il n'est pas nécessaire de monter un cassandra/mongodb en cluster lorsque le besoin est finalement de stocker de la donnée relationnelle (rappel : notre univers est relationnel) représentant moins de 100 Go.
Et si problème de scaling il y a, alors c'est une excellente nouvelle ! Le business aura crû progressivement (ou rapidement), l'approche DB-centric aura permis d'avoir un time-to-market très aggressif tout en gardant un investissement en développement réduit (grâce à ladite architecture). Il sera temps d'investir cette fois dans de l'optimisation au cas par cas tout en ayant une idée des volumes en jeu et des informations de monitoring sur les goulôts d'étranglement. Cela permettra de choisir une alternative de manière éclairée. Elle est pas belle la vie ? 😉
Comment bien démarrer ?
Le starter-kit de subzero (l'équivalent de PostgREST mais qui offre aussi GraphQL par dessus) est un excellent point de départ !
Aller plus loin
Un nouveau produit (open-source !) a fait son apparition sur le marché : Hasura. Sa courbe d'apprentissage est bien plus aisée que PostgREST (sur l'aspect initialisation du projet) car il expose une interface graphique pour créer ses différentes tables, déclarer les dépendances et propose un premier niveau de gestion de permission. C'est moins violent que de partir directement sur des migrations SQL pour les nouveaux arrivants. Hasura expose le tout sous forme d'API GraphQL (avec support des subscriptions temps-réel sans code supplémentaire !) et un support des webhooks for-free, faites vous plaisir!
Pour mysql il y a xmysql, même logique que PostgREST mais qui ne respecte pas SoC (Separation of Concerns), il fait papa/maman (upload de fichiers, génération de graphique, ...), bien pour prototyper mais je ne le recommanderais pas pour de la production.
Une dernière chose 😅📚!
J'ai commencé à écrire un livre sur "tout ce que j'aurais aimé connaitre avant d'être CTO" (nom temporaire) "D'un petit SaaS à forte croissance à une très grande entreprise".
C'est une collection de tous les principes et patterns que j'ai pu découvrir depuis 2012, allant de la spécification de l'organisation, de la mise à l'échelle des processus internes, du staffing et sourcing, du management, de la gestion de la production, des choix, avec bien sûr une large partie concernant le pendant technique et l'affinage d'une vision. Ces dernières années beaucoup de collègues m'ont demandé de réunir ces principes au sein d'un même endroit... nous y voilà ! J'espère terminer ce livre avant mon 30ème anniversaire (juste pour le challenge), il reste encore 9 mois et les principes fondamentaux que je répète chaque jour au travail sont là.
Le formulaire ci-dessous te permettra d'être notifié de sa publication 🐣!
