Studi Implementasi Keamanan Tingkat Baris di PostgreSQL

Sebagai pelengkap Etude tentang implementasi logika bisnis pada tingkat fungsi tersimpan PostgreSQL dan terutama untuk jawaban rinci atas komentar tersebut .



Teori ini dijelaskan dengan baik dalam dokumentasi PostgreSQL - Kebijakan Keamanan Baris . Di bawah ini adalah implementasi praktis dari tugas bisnis kecil tertentu - menyembunyikan data yang dihapus. Kajian tentang penerapan Role Model dengan menggunakan RLS disajikan secara terpisah.

Artikel bukanlah hal baru, tidak ada makna tersembunyi dan pengetahuan rahasia. Sekadar sketsa tentang implementasi praktis dari ide teoritis. Jika ada yang tertarik, baca terus. Siapa yang tidak tertarik - jangan buang waktu Anda.

Rumusan masalah

Tanpa mendalami pokok bahasan, secara singkat tugas tersebut dapat dirumuskan sebagai berikut: ada tabel yang mengimplementasikan suatu badan usaha tertentu. Baris dalam tabel dapat dihapus, tetapi Anda tidak dapat menghapus baris secara fisik, Anda perlu menyembunyikannya.



Karena dikatakan - “Jangan hapus apa pun, cukup ganti namanya. Internet menyimpan SEMUANYA. ”



Di sepanjang jalan, disarankan untuk tidak menulis ulang fungsi tersimpan yang ada yang bekerja dengan entitas ini.



Untuk mengimplementasikan konsep ini, tabel memiliki atribut is_deleted . Kemudian semuanya sederhana - Anda harus membuatnya sehingga klien hanya dapat melihat baris di mana atribut is_deleted salah. Untuk apa mekanisme Keamanan Tingkat Baris digunakan .

Penerapan

Buat peran dan skema terpisah

CREATE ROLE repos;
CREATE SCHEMA repos;

Buat tabel target

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

Aktifkan 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 - menghapus baris dalam 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 - penghapusan 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;

hasil

Klien menghapus dokumen

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

Setelah penghapusan, klien tidak melihat dokumen tersebut

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

Tetapi dokumen tersebut belum dihapus di database, hanya atribut is_del yang diubah

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

Inilah yang dibutuhkan dalam perumusan masalah.

Hasil

Jika topiknya menarik, di etude berikutnya Anda dapat menunjukkan contoh penerapan model berbasis peran untuk berbagi akses data menggunakan Row Level Security.