دراسة تنفيذ السرية على مستوى الصف في PostgreSQL

كإضافة إلى Etude بشأن تنفيذ منطق الأعمال على مستوى الوظائف المخزنة في PostgreSQL وبشكل أساسي للحصول على إجابة مفصلة على التعليق .



تم وصف النظرية جيدًا في وثائق PostgreSQL - سياسات أمان الصف . يوجد أدناه تنفيذ عملي لمهمة عمل صغيرة محددة - إخفاء البيانات المحذوفة. يتم تقديم دراسة حول تنفيذ نموذج الدور باستخدام RLS بشكل منفصل.

المقال ليس بجديد ، فلا معنى خفي ومعرفة سرية. مجرد رسم تخطيطي حول التنفيذ العملي لفكرة نظرية. إذا كان أي شخص مهتم ، واصل القراءة. من غير مهتم - لا تضيع وقتك.

صياغة المشكلة

بدون التعمق في مجال الموضوع ، باختصار ، يمكن صياغة المهمة على النحو التالي: يوجد جدول ينفذ كيانًا تجاريًا معينًا. يمكن حذف الصفوف الموجودة في الجدول ، ولكن لا يمكنك حذف الصفوف فعليًا ، فأنت بحاجة إلى إخفائها.



لأنه يقال - "لا تحذف أي شيء ، فقط أعد التسمية. يخزن الإنترنت كل شيء. "على



طول الطريق ، من المستحسن عدم إعادة كتابة الوظائف المخزنة الحالية التي تعمل مع هذا الكيان.



لتنفيذ هذا المفهوم ، يحتوي الجدول على سمة is_deleted . ثم يكون كل شيء بسيطًا - تحتاج إلى إجرائه حتى يتمكن العميل من رؤية الأسطر التي تكون فيها سمة is_deleted خاطئة فقط. لما يتم استخدام آلية أمان مستوى الصف.

التنفيذ

إنشاء دور ومخطط منفصل

CREATE ROLE repos;
CREATE SCHEMA repos;

قم بإنشاء الجدول الهدف

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

قم بتشغيل أمان مستوى الصف

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 ;

وظيفة الخدمة - حذف صف في جدول

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;

وظيفة العمل - حذف الوثيقة

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

النتائج

العميل يحذف الوثيقة

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

بعد الحذف ، لا يرى العميل المستند

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

ولكن لم يتم حذف المستند في قاعدة البيانات ، تم تغيير سمة is_del فقط

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

هذا هو المطلوب في صياغة المشكلة.

النتيجة

إذا كان الموضوع مثيرًا للاهتمام ، يمكنك في الدليل التالي إظهار مثال على تنفيذ نموذج قائم على الدور لمشاركة الوصول إلى البيانات باستخدام أمان مستوى الصف.