Minangka pelengkap kanggo
Bagean teoretis diterangake kanthi apik ing dokumentasi
Ora ana sing anyar ing artikel kasebut, ora ana makna sing didhelikake utawa kawruh rahasia. Mung sketsa babagan implementasi praktis saka ide teoretis. Yen ana sing kasengsem, maca. Yen sampeyan ora kasengsem, aja mbuwang wektu.
Formulasi masalah
Tanpa nyilem kanthi jero menyang area subyek, kanthi ringkes, masalah kasebut bisa dirumusake kaya ing ngisor iki: Ana tabel sing ngleksanakake entitas bisnis tartamtu. Baris ing tabel bisa dibusak, nanging baris ora bisa dibusak sacara fisik, kudu didhelikake.
Amarga wis ngandika: "Aja mbusak apa-apa, mung ganti jeneng. Internet nyimpen kabeh"
Sadawane dalan, disaranake ora nulis maneh fungsi sing wis disimpen sing bisa digunakake karo entitas iki.
Kanggo ngleksanakake konsep iki, tabel nduweni atribut wis_deleted. Banjur kabeh iku prasaja - sampeyan kudu nggawe manawa klien bisa ndeleng mung garis kang atribut wis_deleted palsu Apa mekanisme digunakake? Keamanan Tingkat Baris.
Π Π΅Π°Π»ΠΈΠ·Π°ΡΠΈΡ
Nggawe peran lan skema sing kapisah
CREATE ROLE repos;
CREATE SCHEMA repos;
Nggawe tabel target
CREATE TABLE repos.file
(
...
is_del BOOLEAN DEFAULT FALSE
);
CREATE SCHEMA repos
We kalebu Keamanan Tingkat Baris
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 ;
Fungsi layanan - mbusak baris ing 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;
Fungsi bisnis - mbusak dokumen
CREATE OR REPLACE business_functions.deleteDoc( doc_for_delete JSON )
RETURNS JSON AS $$
BEGIN
...
PERFORM repos.delete( doc_id ) ;
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
Π Π΅Π·ΡΠ»ΡΡΠ°ΡΡ
Klien mbusak dokumen kasebut
SELECT business_functions.delCFile( (SELECT json_build_object( 'CId', 3 )) );
Sawise pambusakan, klien ora bisa ndeleng dokumen kasebut
SELECT business_functions.getCFile"( (SELECT json_build_object( 'CId', 3 )) ) ;
-----------------
(0 rows)
Nanging ing database dokumen ora dibusak, mung atribut diganti is_del
psql -d my_db
SELECT id, name , is_del FROM repos.file ;
id | name | is_del
--+---------+------------
1 | test_1 | t
(1 row)
Kang dibutuhake ing statement masalah.
Asile
Yen topik menarik, ing sinau sabanjurΓ© sampeyan bisa nuduhake conto ngleksanakake model basis peran kanggo misahake akses data nggunakake Row Level Keamanan.
Source: www.habr.com