Étude sur la mise en œuvre de la sécurité au niveau des lignes dans PostgreSQL

En complément de Étude sur la mise en œuvre de la logique métier au niveau des fonctions stockées PostgreSQL et principalement pour une réponse détaillée sur commentaire.

La partie théorique est bien décrite dans la documentation Postgres Pro — Politiques de protection des lignes. Ci-dessous, nous examinons la mise en œuvre pratique d'un problème commercial spécifique — la dissimulation des données supprimées. L'étude est consacrée à la mise en œuvre d'un modèle de rôle utilisant le RLS présenté séparément.

L'article n'apporte rien de nouveau, il n'y a pas de sens caché ni de connaissances secrètes. Juste un aperçu de la mise en œuvre pratique d'une idée théorique. Si cela vous intéresse, lisez. Si cela ne vous intéresse pas, ne perdez pas votre temps.

Définition du problème

Sans plonger profondément dans le domaine, nous pouvons formuler la tâche comme suit : il existe une table mettant en œuvre une certaine entité commerciale. Les lignes de la table peuvent être supprimées, mais il est interdit de supprimer physiquement les lignes, il faut les cacher.

Car il est dit — «Ne supprime rien, renomme simplement. Internet conserve TOUT»

Idéalement, il serait souhaitable de ne pas réécrire les fonctions stockées existantes qui fonctionnent avec cette entité.

Pour mettre en œuvre ce concept, la table possède un attribut is_deleted. Ensuite, tout est simple — il faut faire en sorte que le client ne puisse voir que les lignes où l'attribut is_deleted est faux. C'est pourquoi nous utilisons le mécanisme Row Level Security.

Mise en œuvre

Créons un rôle et un schéma distincts

CREATE ROLE repos;
CREATE SCHEMA repos;

Créons la table cible

CREATE TABLE repos.file
(
...
is_del BOOLEAN DEFAULT FALSE
);
CREATE SCHEMA repos

Nous activons Row Level Security

ALTER TABLE repos.file ENABLE ROW LEVEL SECURITY;
CREATE POLICY file_invisible_deleted ON repos.file FOR ALL TO dba_role USING (NOT is_deleted);
GRANT ALL ON TABLE repos.file TO dba_role;
GRANT USAGE ON SCHEMA repos TO dba_role;

Fonction de service — suppression d'une ligne dans la table

CREATE OR REPLACE repos.delete(curr_id repos.file.id%TYPE)
RETURNS integer AS $$
BEGIN
...
UPDATE repos.file
SET is_del = TRUE
WHERE id = curr_id;
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Fonction commerciale — suppression d'un document

CREATE OR REPLACE business_functions.deleteDoc(doc_for_delete JSON)
RETURNS JSON AS $$
BEGIN
...
PERFORM repos.delete(doc_id);
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Résultats

Le client supprime le document

SELECT business_functions.delCFile((SELECT json_build_object('CId', 3)));

Après la suppression, le client du document ne voit pas

SELECT business_functions.getCFile((SELECT json_build_object('CId', 3))); 
-----------------
(0 lignes)

Mais dans la base de données, le document n'est pas supprimé, seul l'attribut is_del

psql -d my_db
SELECT id, name, is_del FROM repos.file;
id | name | is_del
--+---------+------------
 1 | test_1 | t
(1 ligne)

Ce qui était requis dans la tâche.

Conclusion

Si le sujet vous intéresse, l'étude suivante peut présenter un exemple de mise en œuvre d'un modèle de rôle pour la séparation des accès aux données en utilisant la sécurité au niveau des lignes.

Source : habr.com