Iwadi lori imuse Aabo Ipele Ipele ni PostgreSQL

Bi iranlowo si Iwadi lori imuse ọgbọn iṣowo ni ipele ti awọn iṣẹ ipamọ PostgreSQL и o kun fun a alaye idahun on asọye.

Awọn tumq si apakan ti wa ni daradara apejuwe ninu awọn iwe PostgreSQL - Awọn eto imulo aabo kana. Ni isalẹ jẹ imuse ti o wulo ti kekere kan iṣẹ-ṣiṣe iṣowo kan pato - fifipamọ data paarẹ. Sketch igbẹhin si imuse Awoṣe ipa nipa lilo RLS gbekalẹ lọtọ.

Ko si ohun titun ninu awọn article, nibẹ ni ko si farasin itumo tabi ìkọkọ imo. O kan afọwọya nipa imuse ilowo ti imọran imọran. Ti ẹnikẹni ba nife, ka. Ti o ko ba nife, maṣe fi akoko rẹ ṣòfo.

Igbekalẹ iṣoro naa

Laisi omi omi jinna si agbegbe koko-ọrọ, ni ṣoki, iṣoro naa le ṣe agbekalẹ bi atẹle: Tabili kan wa ti o ṣe imuse ile-iṣẹ iṣowo kan. Awọn ori ila ti o wa ninu tabili le paarẹ, ṣugbọn awọn ori ila ko le paarẹ ti ara; wọn gbọdọ wa ni pamọ.

Nitori a sọ pe: “Maṣe paarẹ ohunkohun, kan tun lorukọ rẹ. Intanẹẹti tọju ohun gbogbo"

Ni ọna, o ni imọran lati ma ṣe atunkọ awọn iṣẹ ipamọ ti o wa tẹlẹ ti o ṣiṣẹ pẹlu nkan yii.

Lati ṣe imuse ero yii, tabili ni abuda naa ti wa ni_parẹ. Lẹhinna ohun gbogbo rọrun - o nilo lati rii daju pe alabara le rii awọn laini nikan ninu eyiti ẹda naa ti wa ni_parẹ eke Kini ẹrọ ti a lo fun? Aabo Ipele kana.

Imuse

Ṣẹda ipa ti o yatọ ati eto

CREATE ROLE repos;
CREATE SCHEMA repos;

Ṣẹda awọn afojusun tabili

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

A pẹlu Aabo Ipele Kana

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 ;

Iṣẹ iṣẹ - piparẹ kana ni tabili

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;

Iṣẹ iṣowo - piparẹ iwe-ipamọ

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

Результаты

Onibara pa iwe-ipamọ naa

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

Lẹhin piparẹ, alabara ko rii iwe-ipamọ naa

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

Ṣugbọn ninu ibi ipamọ data ko ṣe paarẹ iwe-ipamọ, ẹda nikan ni o yipada jẹ_del

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

Ewo ni ohun ti a beere ninu alaye iṣoro naa.

Abajade

Ti koko-ọrọ ba jẹ iyanilenu, ninu ikẹkọ atẹle o le ṣafihan apẹẹrẹ ti imuse awoṣe ti o da lori ipa fun yiya sọtọ wiwọle data nipa lilo Aabo Ipele Ipele.

orisun: www.habr.com