Étude d'implémentation de la sécurité au niveau des lignes dans PostgreSQL

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



La théorie est bien décrite dans la documentation PostgreSQL - Row Security Policies . Vous trouverez ci-dessous une mise en œuvre pratique d'une petite tâche commerciale spécifique - masquer les données supprimées. Une étude sur la mise en œuvre du modèle de rôle à l'aide de RLS est présentée séparément.

L'article n'est pas nouveau, il n'y a pas de sens caché et de connaissance secrète. Juste une esquisse sur la mise en œuvre pratique d'une idée théorique. Si quelqu'un est intéressé, lisez la suite. Qui n'est pas intéressé - ne perdez pas votre temps.

Formulation du problème

Sans entrer dans le sujet, brièvement, la tâche peut être formulée comme suit: il existe un tableau qui implémente une certaine entité commerciale. Les lignes du tableau peuvent être supprimées, mais vous ne pouvez pas supprimer physiquement les lignes, vous devez les masquer.



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



En cours de route, il est conseillé de ne pas réécrire les fonctions stockées déjà existantes travaillant avec cette entité.



Pour implémenter ce concept, la table a l'attribut is_deleted . Ensuite, tout est simple - vous devez faire en sorte que le client ne puisse voir que les lignes dans lesquelles l'attribut is_deleted est faux. Pour quoi le mécanisme de sécurité au niveau des lignes est utilisé .

la mise en oeuvre

Créer un rôle et un schéma séparés

CREATE ROLE repos;
CREATE SCHEMA repos;

Créer la table cible

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

Activer la 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 - suppression d'une ligne dans une 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 de documents

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 le document n'a pas été supprimé de la base de données, seul l'attribut is_del a été modifié

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 nécessaire pour formuler le problème.

Résultat

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