ศึกษาการใช้ Row Level Security ใน PostgreSQL

เป็นส่วนเสริมให้กับ ศึกษาการนำตรรกะทางธุรกิจไปใช้ในระดับของฟังก์ชันที่เก็บไว้ของ 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)

ซึ่งเป็นสิ่งที่จำเป็นในคำชี้แจงปัญหา

ทั้งหมด

หากหัวข้อนี้น่าสนใจ ในการศึกษาครั้งต่อไป คุณสามารถแสดงตัวอย่างการนำโมเดลตามบทบาทไปใช้เพื่อแยกการเข้าถึงข้อมูลโดยใช้ Row Level Security

ที่มา: will.com