Etude om implementering av Row Level Security i PostgreSQL

Som eit tilskott til Etude om implementering av forretningslogikk på nivået av lagrede funksjoner i PostgreSQL og hovudsakleg for ein utfyllande svar En funksjon har blitt kommentar.

Teoretisk del er godt beskrevet i dokumentasjonen Postgres Pro — Rettighetsbeskyttelsespolitikk for rader. Nedenfor er ei praktisk implementering av ei lita konkret forretningsoppgåve — skjuling av fjerna data. Studien er dedikert til realiseringa av Rollemodellen med bruk av RLS og presentert separat.

Artikkelen inneholder ingenting nytt, ingen skjulte betydninger eller hemmelig kunnskap. Bare en skisse over den praktiske implementeringen av en teoretisk idé. Hvis noen er interessert — les. Hvis ikke — ikke kast bort tiden din.

Definering av oppgaven

Utan å gå djupt inn i emnet kan oppgåva kortformulerast som følgjer: det finnes ei tabell som implementerer ei viss forretningsentitet. Rada i tabellen kan bli sletta, men det er ikkje mogleg å fysiske slette radene, dei må skjulast.

For det er sagt — «Slett ingenting, berre omdøyp det. Internett lagrar ALT»

Samtidig er det ønskjeleg å ikkje omprogrammere eksisterande lagra funksjonar som jobbar med denne entiteten.

For å implementere dette konseptet, har tabellen attributtet is_deleted. Så er det enkelt — det må gjerast slik at klienten berre kan sjå radene der attributtet is_deleted er falskt. Derfor blir mekanismen Row Level Security brukt.

Implementering

Vi opprettar ei eiga rolle og skjema

CREATE ROLE repos;
CREATE SCHEMA repos;

Vi opprettar måltabellen

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

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

Tenestefunksjon — sletting av rad i tabellen

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;

Forretningsfunksjon — sletting av dokument

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

Resultater

Klienten slettar dokumentet

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

Etter slettinga kan ikkje dokumentklienten sjå

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

Men i databasen er ikkje dokumentet sletta, berre attributtet is_del endra

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

Det var det som vart krava i oppgåva.

Resultat

Om temaet er interessant, kan vi i neste studie vise eit eksempel på implementeringa av rollemodellen for deling av tilgang til data med bruk av Row Level Security.

Kilde: habr.com