O se su'esu'ega ile fa'atinoina ole Row Level Security ile PostgreSQL

E fai ma fesoasoani i O se suʻesuʻega i le faʻatinoina o pisinisi faʻatatau i le tulaga o PostgreSQL galuega faʻapipiʻi и aemaise lava mo se tali auiliili i faʻamatalaga.

O le vaega fa'ata'ita'i o lo'o fa'amatalaina lelei ile fa'amaumauga PostgreSQL - faiga fa'avae puipuia laina. O loʻo i lalo se faʻatinoga faʻatinoga o se laʻititi galuega fa'apisinisi fa'apitoa - natia fa'amaumauga ua tapeina. Sketch fa'apitoa mo le fa'atinoga Fa'ata'ita'iga fa'ata'ita'iga e fa'aaoga ai le RLS tu'u ese'ese.

E leai se mea fou i totonu o le tusiga, e leai se uiga natia po o se malamalama lilo. Na'o se fa'ata'ita'iga e uiga i le fa'atinoga fa'atino o se manatu fa'ata'ita'i. Afai ei ai se tasi e fiafia, faitau i ai. Afai e te le fiafia, aua le faamaimaua lou taimi.

Fausiaina o le faʻafitauli

A aunoa ma le maulu loloto i le mataupu autu, puupuu, o le faafitauli e mafai ona faʻatulagaina e pei ona taua i lalo: O loʻo i ai se laulau e faʻaaogaina ai se pisinisi faʻapitoa. E mafai ona tape laina i le laulau, ae le mafai ona tapeina fa'aletino; e tatau ona natia.

Aua ua fai mai: "Aua le tapeina se mea, na o le toe faaigoa. O le Initaneti e teuina mea uma"

I luga o le ala, e fautuaina e aua neʻi toe tusia galuega faʻapipiʻi o loʻo i ai nei o loʻo galulue ma lenei vaega.

Ina ia faʻatinoina lenei manatu, o le laulau e iai le uiga is_deleted. Ona faigofie lea o mea uma - e tatau ona e mautinoa e mafai e le kalani ona vaʻaia naʻo laina o loʻo i ai le uiga is_deleted pepelo O le a le masini e fa'aoga ai? Saogalemu Tulaga Laila.

Реализация

Fausia se tulaga ese'ese ma se fuafuaga

CREATE ROLE repos;
CREATE SCHEMA repos;

Fausia le laulau fa'atatau

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

Ki Saogalemu Tulaga Laila

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 ;

Galuega tautua — tapeina se laina i le laulau

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;

Galuega tau pisinisi — tapeina o se pepa

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

Iʻuga

E tape e le kalani le pepa

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

A uma ona tape, e le iloa e le kalani le pepa

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

Ae i totonu o le database e le tapeina le pepa, naʻo le uiga e suia is_del

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

O le a le mea na manaʻomia i le faʻamatalaga faʻafitauli.

Le iʻuga

Afai e manaia le autu, i le isi suʻesuʻega e mafai ona e faʻaalia se faʻataʻitaʻiga o le faʻatinoina o se faʻataʻitaʻiga faʻavae mo le tuʻufaʻatasia o avanoa faʻamatalaga e faʻaaoga ai le Row Level Security.

puna: www.habr.com