En plus des système de droits du standard SQL disponible via GRANT(7), les tables peuvent avoir des politiques de sécurité pour l'accès aux lignes qui restreignent, utilisateur par utilisateur, les lignes qui peuvent être renvoyées par les requêtes d'extraction ou les commandes d'insertions, de mises à jour ou de suppressions. Cette fonctionnalité est aussi connue sous le nom Row-Level Security. Par défaut, les tables n'ont aucune politique de ce type pour que, si un utilisateur a accès à une table selon les droits du standard SQL, toutes les lignes de la table sont accessibles aux requêtes de lecture ou d'écriture.
Lorsque la protection des lignes est activée sur une table (avec l'instruction ALTER TABLE ... ENABLE ROW LEVEL SECURITY), tous les accès classiques à la table pour sélectionner ou modifier des lignes doivent être autorisés par une politique de sécurité. Cependant, le propriétaire de la table n'est typiquement pas soumis aux politiques de sécurité. Si aucune politique n'existe pour la table, une politique de rejet est utilisé par défaut, ce qui signifie qu'aucune ligne n'est visible ou ne peut être modifiée. Les opérations qui s'appliquent pour la table dans sa globalité, comme TRUNCATE et REFERENCES, ne sont pas soumis à ces restrictions de niveau ligne.
Les politiques de sécurité niveau ligne peuvent s'appliquer en particulier soit à des commandes, soit à des rôles, soit aux deux. Une politique est indiquée comme s'appliquant à toutes les commandes par ALL, ou seulement à SELECT, INSERT, UPDATE ou DELETE. Plusieurs rôles peuvent être affectés à une politique donnée, et les règles normales d'appartenance et d'héritage s'appliquent.
Pour indiquer les lignes visibles ou modifiables pour une politique, une expression renvoyant un booléen est requise. Cette expression sera évaluée pour chaque ligne avant toutes conditions ou fonctions qui seraient indiquées dans les requêtes de l'utilisateur. (La seule exception à cette règle sont les fonctions marquées leakproof, qui annoncent ne pas dévoiler d'information ; l'optimiseur peut choisir d'appliquer de telles fonctions avant les vérifications de sécurité niveau ligne). Les lignes pour lesquelles l'expression ne renvoie pas true ne sont pas traitées. Des expressions différentes peuvent être indiquées pour fournir des contrôles indépendants pour les lignes qui sont visibles et pour celles qui sont modifiées. Les expressions attachées à la politique sont exécutées dans le cours de la requête et avec les droits de l'utilisateur qui exécute la commande, bien que les fonctions définies avec l'attribut SECURITY DEFINER peuvent être utilisées pour accèder à des données qui ne seraient pas disponibles à l'utilisateur effectuant la requête.
Les superutilisateurs et les roles avec l'attribut BYPASSRLS ne sont pas soumis au système de sécurité niveau ligne lorsqu'ils accèdent une table. Il en est de même par défaut du propriétaire d'une table, bien qu'il puisse choisir de se soumettre à ces contrôles avec ALTER TABLE ... FORCE ROW LEVEL SECURITY.
L'activation ou la désactivation de la sécurité niveau ligne, comme de l'ajout des polices à une table, est toujours le privilège du seul propriétaire de la table.
Les politiques sont créées en utilisant l'instruction CREATE POLICY(7), modifiées avec la commande ALTER POLICY(7) et supprimées avec la commande DROP POLICY(7). Pour activer et désactiver la sécurité niveau ligne pour une table donnée, utilisez la commande ALTER TABLE(7).
Chaque politique possède un nom et de multiples politiques peuvent être définies pour une table. Comme les politiques sont spécifiques à une table, chaque politique pour une même table doit avoir un nom différent. Différentes tables peuvent avoir des noms de politique de même nom.
Lorque plusieurs politiques sont applicables pour une même requête, elles sont combinées en utilisant OR, c'est-à-dire qu'une ligne est accessible si une des politiques le permet. Ceci est similaire à la règle qui veut qu'un rôle donné possède les droits de tous les rôles dont il est membre.
À titre de simple exemple, nous allons ici créer une politique sur la relation comptes pour autoriser seulement les membres du rôle admins à accèder seulement aux lignes de leurs propres comptes :
Droits
CREATE TABLE comptes (admin text, societe text, contact_email text);
ALTER TABLE comptes ENABLE ROW LEVEL SECURITY;
CREATE POLICY compte_admins ON comptes TO admins
USING (admin = current_user);
La politique ci-dessous fournit implicitement une clause WITH CHECK identique à sa clause USING, pour que la contrainte s'applique à la fois aux lignes sélectionnées par une commande (donc un administrateur ne peut pas exécuter SELECT, UPDATE, ou DELETE sur des lignes existantes appartenant à un autre administrateur) et aux lignes modifiées par une commande (donc des lignes appartenant à un autre administrateur ne peuvent pas être créées via INSERT ou UPDATE).
Si aucun rôle n'est indiqué ou si le nom de pseudo rôle PUBLIC est utilisé, alors la politique s'applique à tous les utilisateurs du système. Pour autoriser tous les utilisateurs à accèder seulement à leurs propres lignes dans une table utilisateurs, une simple politique peut être utilisée :
CREATE POLICY police_utilisateur ON utilisateurs
USING (user_name = current_user);
Ceci fonctionne de façon similaire à l'exemple précédent.
Pour utiliser une politique différente pour les lignes ajoutées à la table de celle appliquées pour les lignes visibles, plusieurs politiques peuvent être combinées. Cette paire de politiques permettrait à tous les utilisateurs de visualiser toutes les lignes de la table utilisateurs, mais seulement de modifier leur propre fiche :
CREATE POLICY police_utilisateur_sel ON utilisateurs
FOR SELECT
USING (true);
CREATE POLICY police_utilisateur_mod ON utilisateurs
USING (user_name = current_user);
Dans une commande SELECT, ces deux politiques sont combinées en utilisant OR, avec comme effet direct que toutes les lignes peuvent être lues. Dans les autres types de commandes, seule la deuxième politique s'applique, donc l'effet est le même qu'avant.
La sécurité niveau ligne peut également être désactivée avec la commande ALTER TABLE. La désactivation de la sécurité niveau ligne ne supprime pas les polices qui sont définies pour la table ; elles sont simplement ignorées. L'ensemble des lignes sont alors visibles et modifiables, selon le système standard des droits SQL.
Ci-dessous se trouve un exemple plus conséquent de la manière dont cette fonctionnalité peut être utilisée en production. La table passwd simule le fichier des mots de passe d'un système Unix.
-- Simple exemple basée sur le fichier passwd
CREATE TABLE passwd (
user_name text UNIQUE NOT NULL,
pwhash text,
uid int PRIMARY KEY,
gid int NOT NULL,
real_name text NOT NULL,
home_phone text,
extra_info text,
home_dir text NOT NULL,
shell text NOT NULL
);
CREATE ROLE admin; -- Administrateur
CREATE ROLE bob; -- Utilisateur normal
CREATE ROLE alice; -- Utilisateur normal
-- Chargement de la table
INSERT INTO passwd VALUES
('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');
-- Assurez vous d'activer le row level security pour la table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
-- Créer les polices
-- L'administrateur peut voir toutes les lignes et en ajouter comme il le souhaite
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Les utilisateurs normaux peuvent voir toutes les lignes
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Les utilisateurs normaux peuvent mettre à jour leurs propres lignes,
-- tout en limitant les shells qu'ils peuvent choisir
CREATE POLICY user_mod ON passwd FOR UPDATE
USING (current_user = user_name)
WITH CHECK (
current_user = user_name AND
shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
);
-- Donner à admin tous les droits normaux
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Les utilisateurs ne peuvent que sélectionner des colonnes publiques
GRANT SELECT
(user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
ON passwd TO public;
-- Autoriser les utilisateurs à mettre à jour certaines colonnes
GRANT UPDATE
(pwhash, real_name, home_phone, extra_info, shell)
ON passwd TO public;
Comme avec tous les réglages de sécurité, il est important de tester et de s'assurer que le système se comporte comme attendu. En utilisant l'exemple ci-dessus, les manipulations ci-dessous montrent que le système des droits fonctionne correctement
-- admin peut voir toutes les lignes et les colonnes
postgres=> set role admin;
SET
postgres=> table passwd;
user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
admin | xxx | 0 | 0 | Admin | 111-222-3333 | | /root | /bin/dash
bob | xxx | 1 | 1 | Bob | 123-456-7890 | | /home/bob | /bin/zsh
alice | xxx | 2 | 1 | Alice | 098-765-4321 | | /home/alice | /bin/zsh
(3 rows)
-- Tester ce que Alice est capable de faire:
postgres=> set role alice;
SET
postgres=> table passwd;
ERROR: permission denied for relation passwd
postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
user_name | real_name | home_phone | extra_info | home_dir | shell
-----------+-----------+--------------+------------+-------------+-----------
admin | Admin | 111-222-3333 | | /root | /bin/dash
bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh
alice | Alice | 098-765-4321 | | /home/alice | /bin/zsh
(3 rows)
postgres=> update passwd set user_name = 'joe';
ERROR: permission denied for relation passwd
-- Alice est autorisée à modifier son propre nom (real_name), mais pas celui des autres
postgres=> update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=> update passwd set shell = '/bin/xx';
ERROR: new row violates WITH CHECK OPTION for "passwd"
postgres=> delete from passwd;
ERROR: permission denied for relation passwd
postgres=> insert into passwd (user_name) values ('xxx');
ERROR: permission denied for relation passwd
-- Alice peut modifier son propre mot de passe; RLS empêche silencieusement la mise à jour d'autres lignes
postgres=> update passwd set pwhash = 'abc';
UPDATE 1
Les vérifications d'intégrité référentielle, tel que les contraintes d'unicité ou de clefs primaires et les références de clefs étrangères, passent toujours outre la sécurité niveau ligne pour s'assurer que l'intégrité des données est maintenue. Une attention particulière doit être prise lors de la mise en place des schémas et des politiques de sécurité de niveau ligne pour éviter le « covert channel » dévoilant des informations à travers de telles vérifications d'intégrité référentielle.
Dans certains contextes, il est important d'être certain que la sécurité niveau ligne n'est pas appliquée. Par exemple, lors d'une sauvegarde, il y aurait des conséquences désastreuses si la sécurité niveau ligne a pour conséquence de soustraire silencieusement certaines lignes de la sauvegarde. Dans une telle situation, vous pouvez positionner le paramètre de configuration row_security à off. En lui-même, ce paramètre ne passe pas outre la sécurité niveau ligne ; ce qu'il fait, c'est qu'il lève une erreur si une des requêtes devait être filtrée par une police. La raison de l'erreur peut alors être recherchée et résolue.
Dans les exemple ci-dessus, les expressions attachées aux polices considèrent uniquement les valeurs de la ligne courante accèdée ou modifiée. C'est le plus simple et le plus performant des cas ; lorsque c'est possible, il est préférable de concevoir les applications qui utilisent cette fonctionnalité de la sorte. S'il est nécessaire de consulter d'autres lignes ou tables pour que la politique puisse prendre une décision, ceci peut être réalisé en utilisant dans les expressions des politiques des sous-requêtes SELECT ou des fonctions qui contiennent des commandes SELECT. Cependant, faites attention que de tels accès peuvent créer des accès concurrents qui pourraient permettre de dévoiler des informations si aucune précaution n'est prise. À titre d'exemple, considérez la création de la table suivante :
-- définition des droits de groupes
CREATE TABLE groupes (groupe_id int PRIMARY KEY,
nom_groupe text NOT NULL);
INSERT INTO groupes VALUES
(1, 'bas'),
(2, 'moyen'),
(5, 'haut');
GRANT ALL ON groupes TO alice; -- alice est l'administratrice
GRANT SELECT ON groupes TO public;
-- définition des niveaux de droits utilisateurs
CREATE TABLE utilisateurs (nom_utilisateur text PRIMARY KEY,
groupe_id int NOT NULL REFERENCES groupes);
INSERT INTO utilisateurs VALUES
('alice', 5),
('bob', 2),
('mallory', 2);
GRANT ALL ON utilisateurs TO alice;
GRANT SELECT ON utilisateurs TO public;
-- table contenant l'information à protéger
CREATE TABLE information (info text,
groupe_id int NOT NULL REFERENCES groupes);
INSERT INTO information VALUES
('peu secret', 1),
('légèrement secret', 2),
('très secret', 5);
ALTER TABLE information ENABLE ROW LEVEL SECURITY;
-- une ligne devrait être visible et modifiable pour les utilisateurs
-- dont le groupe_id est égal ou plus grand au groupe_id de la ligne
CREATE POLICY fp_s ON information FOR SELECT
USING (groupe_id <= (SELECT groupe_id FROM utilisateurs WHERE nom_utilisateur = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
USING (groupe_id <= (SELECT groupe_id FROM utilisateurs WHERE nom_utilisateur = current_user));
-- nous comptons sur les RLS pour protéger la table information
GRANT ALL ON information TO public;
Maintenant, supposez qu'alice souhaite modifier l'information « légèrement secrète », mais décide que mallory ne devrait pas pouvoir obtenir ce nouveau contenu, elle le fait ainsi :
BEGIN;
UPDATE utilisateurs SET groupe_id = 1 WHERE nom_utilisateur = 'mallory';
UPDATE information SET info = 'caché à mallory' WHERE groupe_id = 2;
COMMIT;
Ceci semble correct, il n'y a pas de fenêtre pendant laquelle mallory devrait pouvoir accèder à la chaîne « caché à mallory ». Cependant il y a une situation de compétition ici. Si mallory fait en parallèle, disons :
SELECT * FROM information WHERE groupe_id = 2 FOR UPDATE;
et sa transaction est en mode READ COMMITED, il est possible qu'elle voit « caché à mallory ». C'est possible si sa transaction accède la ligne information juste après qu'alice l'ai fait. Elle est bloquée en attendant que la transaction d'alice valide, puis récupère la ligne mise à jour grâce à la clause FOR UPDATE. Cependant, elle ne récupère pas une ligne mise à jour pour la commande implicite SELECT sur la table utilisateurs parce que cette sous-commande n'a pas la clause FOR UPDATE ; à la place, la ligne utilisateurs est lue avec une image de la base de données prise au début de la requête. Ainsi, l'expression de la politique teste l'ancienne valeur du niveau de droit de mallory et l'autorise à voir la valeur mise à jour.
Il y a plusieurs solutions à ce problème. Une simple réponse est d'utiliser SELECT ... FOR SHARE dans la sous-commande SELECT de la politique de sécurité niveau ligne. Cependant, ceci demande à donner le droit UPDATE sur la table référencée (ici utilisateurs) aux utilisateurs concernés, ce qui peut ne pas être souhaité. (Uune autre politique de sécurité niveau ligne pourrait être mise en place pour les empêcher d'exercer ce droit ; ou la sous-commande SELECT pourrait être incluse dans une fonction marquée security definer). De plus, l'utilisation intensive concurrente de verrous partagés sur les lignes de la table référencée pourrait poser un problème de performance, tout spécialement si des mises à jour de cette table sont fréquentes. Une autre solution envisageable, si les mises à jour de la table référencée ne sont pas fréquentes, est de prendre un verrou de type ACCESS EXCLUSIVE sur la table référencée lors des mises à jour, de telle manière qu'aucune autre transaction concurrente ne pourrait consulter d'anciennes valeurs. Ou une transaction pourrait attendre que toutes les transactions se terminent après avoir validées une mise à jour de la table référencée et avant de faire des modifications qui reposent sur la nouvelle politique de sécurité.
Pour plus de détails, voir CREATE POLICY(7) et ALTER TABLE(7).