14 - Sécurité : rôles, permissions et Row Level Security

Ce que tu vas apprendre

• Rôles, utilisateurs et groupes dans PostgreSQL
• GRANT et REVOKE a différents niveaux (schema, table, colonne)
• Row Level Security (RLS) pour filtrer les lignes par utilisateur
• Sécurité de connexion et prevention des injections SQL

Prerequisites

Avoir lu les articles précédents, en particulier les transactions.

Les rôles : utilisateurs et groupes

Dans PostgreSQL, il n'y a pas de distinction entre "utilisateur" et "groupe". Tout est un rôle. Un rôle avec LOGIN peut se connecter. Un rôle sans LOGIN est un groupe.

sql-- Creer un role applicatif (l'utilisateur de ton app)
CREATE ROLE app_user WITH LOGIN PASSWORD 'un_vrai_mot_de_passe_long';

-- Creer un role en lecture seule (pour les rapports, le monitoring)
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'autre_mot_de_passe';

-- Creer un groupe (pas de LOGIN)
CREATE ROLE backend_team;

-- Ajouter un role a un groupe
GRANT backend_team TO app_user;

Le superuser (postgres) a tous les droits sur tout. C'est le root de ta base de donnees. Ne l'utilise jamais dans ton application. Cree un rôle dédié avec les permissions minimales.

GRANT et REVOKE

Permissions au niveau du schema

sql-- Autoriser l'acces au schema
GRANT USAGE ON SCHEMA public TO app_user;

-- Autoriser le SELECT sur toutes les tables existantes
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Aussi sur les futures tables (sinon il faudra GRANT a chaque CREATE TABLE)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

Permissions au niveau des tables

sql-- SELECT, INSERT, UPDATE, DELETE sur des tables specifiques
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT SELECT, INSERT, UPDATE ON order_items TO app_user;
GRANT SELECT ON customers TO app_user;  -- lecture seule sur customers

-- Utilisation des sequences (necessaire pour les SERIAL/auto-increment)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Retirer un droit
REVOKE DELETE ON orders FROM app_user;

Permissions au niveau des colonnes

sql-- L'app peut lire toutes les colonnes de users, mais ne peut modifier que name et email
GRANT SELECT ON users TO app_user;
GRANT UPDATE (name, email) ON users TO app_user;
-- app_user ne peut pas modifier password_hash, role, ou created_at

C'est un niveau de granularité rarement utilise en pratique, mais ca peut etre utile pour des cas spécifiques (empecher l'app de modifier le rôle d'un utilisateur par exemple).

Schemas pour séparer les acces

Les schemas sont des namespaces dans la base. Par défaut, tout va dans public. Tu peux créer des schemas pour isoler les donnees :

sqlCREATE SCHEMA analytics;

-- Creer des tables dans le schema analytics
CREATE TABLE analytics.page_views (
  id SERIAL PRIMARY KEY,
  path TEXT NOT NULL,
  user_id INT,
  viewed_at TIMESTAMP DEFAULT NOW()
);

-- Le role analytics peut lire ses tables, pas les autres
CREATE ROLE analytics_user WITH LOGIN PASSWORD 'motdepasse';
GRANT USAGE ON SCHEMA analytics TO analytics_user;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analytics_user;
-- analytics_user n'a aucun acces au schema public

Row Level Security (RLS)

RLS est une fonctionnalité de PostgreSQL qui filtre les lignes en fonction du rôle connecte. C'est comme un WHERE automatique et invisible.

Le cas d'usage classique

Tu as une application multi-tenant ou chaque client ne doit voir que ses donnees :

sql-- Table avec un tenant_id
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  tenant_id INT NOT NULL,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Activer RLS sur la table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Creer une policy : chaque tenant ne voit que ses documents
CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.tenant_id')::INT);

L'application doit définir le app.tenant_id au début de chaque requête :

sqlSET app.tenant_id = '42';
SELECT * FROM documents;
-- Ne retourne que les documents du tenant 42
-- Meme un SELECT * est filtre automatiquement

En TypeScript avec Prisma ou pg :

typescript// Au debut de chaque requete API
await db.query(`SET app.tenant_id = '${tenantId}'`);
// Toutes les requetes suivantes dans cette connexion sont filtrees

Policies plus avancees

sql-- Policy separee pour SELECT et INSERT
CREATE POLICY tenant_read ON documents
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id')::INT);

CREATE POLICY tenant_write ON documents
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::INT);

-- Policy basee sur le role
CREATE POLICY admin_all ON documents
  FOR ALL
  USING (current_setting('app.role') = 'admin');

USING filtre les lignes visibles (SELECT, UPDATE, DELETE). WITH CHECK vérifié les lignes inserees ou modifiees. Avec la policy tenant_write, un tenant ne peut pas insérer un document avec le tenant_id d'un autre.

RLS et le superuser

Le superuser bypass toujours RLS. Le rôle proprietaire de la table aussi (par défaut). Pour forcer RLS meme sur le proprietaire :

sqlALTER TABLE documents FORCE ROW LEVEL SECURITY;

Tester RLS

sql-- Se connecter en tant qu'app_user
SET ROLE app_user;
SET app.tenant_id = '42';

SELECT * FROM documents;
-- Seulement les documents du tenant 42

INSERT INTO documents (tenant_id, title) VALUES (99, 'Hack');
-- ERROR: new row violates row-level security policy

-- Revenir au superuser
RESET ROLE;

Supabase utilise RLS comme mecanisme de sécurité principal. Chaque table a des policies basees sur le JWT de l'utilisateur. C'est ce qui permet au frontend d'appeler PostgreSQL (via PostgREST) sans backend. La doc Supabase sur RLS est une bonne référencé.

Sécurité de connexion : pg_hba.conf

Le fichier pg_hba.conf contrôle qui peut se connecter, d'ou, et comment :

# TYPE  DATABASE  USER       ADDRESS        METHOD
local   all       postgres                  peer
host    all       app_user   10.0.0.0/24    scram-sha-256
host    all       all        0.0.0.0/0      reject

• local : connexion via socket Unix
• host : connexion TCP/IP
• peer : le nom d'utilisateur OS doit correspondre au rôle PostgreSQL
• scram-sha-256 : authentification par mot de passe (le plus sécurisé)
• md5 : ancien hash de mot de passe (moins sécurisé, éviter)
• reject : refuse la connexion

En production, limite les connexions au réseau interne. N'expose jamais le port 5432 sur Internet. Utilise SSL :

hostssl all app_user 10.0.0.0/24 scram-sha-256

hostssl exige une connexion SSL. Sans ca, les mots de passe transitent en clair sur le réseau.

Prevention des injections SQL

La regle numero un : jamais de concatenation de strings dans les requêtes SQL.

typescript// MAUVAIS (injection SQL possible)
const query = `SELECT * FROM users WHERE email = '${email}'`;
// Si email = "'; DROP TABLE users; --", ta table disparait

// BON (requete parameteree)
const query = 'SELECT * FROM users WHERE email = $1';
const result = await db.query(query, [email]);

Les ORM (Prisma, Drizzle, TypeORM) utilisent des requêtes parameterees par défaut. Le risque d'injection SQL existe surtout quand tu ecris du SQL brut. Dans Prisma :

typescript// Requete brute securisee (Prisma parameterise automatiquement)
const users = await prisma.$queryRaw`
  SELECT * FROM users WHERE email = ${email}
`;

// MAUVAIS (Prisma ne peut pas proteger une string brute)
const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE email = '${email}'`
);

Le principe de moindre privilege s'applique aussi ici : si ton application n'a besoin que de SELECT/INSERT/UPDATE, ne lui donne pas le droit de DROP TABLE. Meme en cas d'injection SQL, les degats sont limites.

Sur paltemps.fr, le rôle applicatif n'a pas le droit de faire de DDL (CREATE, ALTER, DROP). Les migrations tournent avec un rôle séparé, utilise uniquement pendant les deployments.

Résumé

• Creer un rôle dédié par application avec les permissions minimales
• GRANT au niveau schema, table et colonne pour le contrôle fin
• RLS filtre les lignes automatiquement par tenant/utilisateur
• pg_hba.conf pour contrôler les connexions réseau, toujours avec SSL
• Requetes parameterees pour empecher les injections SQL, jamais de concatenation

Article précédent : 13 - Performance Article suivant : 15 - Glossaire

Sources

• PostgreSQL Documentation - Rôles
• PostgreSQL Documentation - Row Level Security
• Supabase - Row Level Security Guide
• OWASP - SQL Injection Prevention