Studie over de implementatie van Row Level Security in PostgreSQL

Als aanvulling op Studie over de implementatie van businesslogica op het niveau van opgeslagen functies in PostgreSQL en voornamelijk voor een uitgebreid antwoord en een werkende opdracht krijgen. over het onderwerp "En hoe is het uiteindelijk afgelopen?". Op mijn uitgebreide antwoord hoorde ik "Dit zou een artikel moeten worden". Nou, als het zo is, dan wordt het een artikel. Misschien is het nuttig voor iemand. Daarin zal de lezer enkele feiten leren over de structuur van backend-codegeneratie in QEMU, en ook hoe je een Just-in-Time compiler voor een webapplicatie schrijft..

Het theoretische gedeelte is uitstekend beschreven in de documentatie Postgres Pro — Rijbescherming beleidsregels. Hieronder bespreken we de praktische implementatie van een kleine specifieke zakelijke taak — het verbergen van verwijderde gegevens. Deze studie is gewijd aan de implementatie van een rolmodel met behulp van RLS dat apart wordt gepresenteerd.

In het artikel is niets nieuws, er is geen verborgen betekenis of geheime kennis. Gewoon een schets van de praktische uitvoering van een theoretisch idee. Als iemand geïnteresseerd is — lees het. Als het je niet interesseert — verspil je tijd niet.

Taakstelling

Zonder diep in het onderwerp te duiken, kan de taak kort als volgt worden geformuleerd: er is een tabel die een bepaalde zakelijke entiteit implementeert. Rijen in de tabel kunnen worden verwijderd, maar de rijen fysiek verwijderen is niet toegestaan, ze moeten verborgen worden.

Want er staat geschreven — "Verwijder niets, hernoem alleen. Het internet onthoudt ALLES"

Syntactisch gezien is het wenselijk om bestaande opgeslagen functies die met deze entiteit werken niet opnieuw te schrijven.

Voor de implementatie van dit concept heeft de tabel een attribuut is_deleted. Vervolgens is alles eenvoudig — het moet zo worden gedaan dat de klant alleen de rijen kan zien waarin het attribuut is_deleted onwaar is. Hiervoor wordt het mechanisme Row Level Security gebruikt.

Implementatie

We creëren een aparte rol en schema

CREATE ROLE repos;
CREATE SCHEMA repos;

We creëren de doel tabel

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

Activeer 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;

Serviefunctie — verwijderen van een rij in de tabel

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;

Zakelijke functie — document verwijderen

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

Resultaten

De klant verwijdert het document

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

Na verwijdering ziet de klant van het document niet

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

Maar in de database is het document niet verwijderd, alleen het attribuut is gewijzigd is_del

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

Wat nodig was volgens de opdracht.

Conclusie

Als het onderwerp interessant is, kan in de volgende studie een voorbeeld worden gegeven van de implementatie van een rolmodel voor toegangscontrole tot gegevens met behulp van Row Level Security.

Bron: habr.com