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

En complément de Une étude sur l'implémentation de la logique métier au niveau des fonctions stockées PostgreSQL и principalement pour une réponse détaillée sur commenter.

La partie théorique est bien décrite dans la documentation PostgreSQL - Politiques de protection des lignes. Vous trouverez ci-dessous une mise en œuvre pratique d'un petit tâche commerciale spécifique - masquer les données supprimées. Croquis dédié à la mise en œuvre Modélisation de rôle à l'aide de RLS présentés séparément.

Il n'y a rien de nouveau dans l'article, il n'y a pas de sens caché ni de connaissance secrète. Juste un aperçu de la mise en œuvre pratique d'une idée théorique. Si quelqu'un est intéressé, lisez-le. Si vous n'êtes pas intéressé, ne perdez pas votre temps.

Formulation du problème

Sans approfondir le sujet, brièvement, le problème peut être formulé comme suit : Il existe une table qui implémente une certaine entité commerciale. Les lignes du tableau peuvent être supprimées, mais les lignes ne peuvent pas être physiquement supprimées ; elles doivent être masquées.

Car il est dit : « Ne supprimez rien, renommez-le simplement. Internet stocke TOUT"

En cours de route, il est conseillé de ne pas réécrire les fonctions stockées existantes qui fonctionnent avec cette entité.

Pour implémenter ce concept, la table a l'attribut est supprimé. Ensuite, tout est simple - vous devez vous assurer que le client ne peut voir que les lignes dans lesquelles l'attribut est supprimé FAUX A quoi sert le mécanisme ? Sécurité au niveau des lignes.

exécution

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

CREATE ROLE repos;
CREATE SCHEMA repos;

Créer la table cible

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

Allumez Sécurité au niveau des lignes

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 — supprimer une ligne dans le tableau

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 — supprimer 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 suppression, le client ne voit pas le document

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

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

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

C'est ce qui était requis dans l'énoncé du problème.

Total

Si le sujet est intéressant, dans l'étude suivante, vous pouvez montrer un exemple de mise en œuvre d'un modèle basé sur les rôles pour séparer l'accès aux données à l'aide de la sécurité au niveau des lignes.

Source: habr.com