Nazarin kan aiwatar da Tsaro Level Tsaro a PostgreSQL

A matsayin kari ga Nazarin kan aiwatar da dabarun kasuwanci a matakin PostgreSQL da aka adana ayyukan и musamman ga cikakken amsa a kan sharhin.

An kwatanta ɓangaren ka'idar da kyau a cikin takardun PostgreSQL - Manufofin kariyar layi. Da ke ƙasa akwai aiwatar da ƙarami takamaiman aikin kasuwanci - ɓoye bayanan da aka goge. Sketch sadaukar don aiwatarwa Role modeling ta amfani da RLS gabatar daban.

Babu wani sabon abu a cikin labarin, babu wani boyayyar ma'ana ko ilimin sirri. Zane kawai game da aiwatar da aiwatar da ra'ayi mai amfani. Idan kowa yana sha'awar karanta shi. Idan ba ku da sha'awar, kada ku ɓata lokacinku.

Tsara matsalar

Ba tare da nutsewa cikin abin da ake magana ba, a taƙaice, ana iya tsara matsalar kamar haka: Akwai tebur da ke aiwatar da wani yanki na kasuwanci. Ana iya share layuka a cikin tebur, amma ba za a iya share layuka a zahiri ba, dole ne a ɓoye su.

Domin an ce: “Kada ku share wani abu, kawai sake suna. Intanet tana adana komai"

A kan hanya, yana da kyau kada a sake rubuta ayyukan da aka adana da ke aiki tare da wannan mahallin.

Don aiwatar da wannan ra'ayi, tebur yana da sifa an_share. Sa'an nan duk abin da yake sauki - kana bukatar ka tabbatar da cewa abokin ciniki iya ganin kawai Lines a cikin abin da sifa an_share karya Menene tsarin da ake amfani dashi? Tsaro Level Level.

Aiwatarwa

Ƙirƙirar rawar daban da tsari

CREATE ROLE repos;
CREATE SCHEMA repos;

Ƙirƙiri teburin manufa

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

Mun hada Tsaro Mataki na Tsaro

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 ;

Ayyukan sabis - share jere a cikin tebur

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;

Ayyukan kasuwanci - share takarda

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

Результаты

Abokin ciniki yana share daftarin aiki

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

Bayan gogewa, abokin ciniki baya ganin takaddar

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

Amma a cikin ma'ajin bayanai ba a goge daftarin aiki ba, kawai ana canza sifa da_del

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

Wanne shine abin da ake buƙata a cikin bayanin matsalar.

Sakamakon

Idan batun yana da ban sha'awa, a cikin bincike na gaba za ku iya nuna misali na aiwatar da samfurin tushen rawar don raba damar bayanai ta amfani da Tsaro Level Security.

source: www.habr.com