การใช้โมเดลการเข้าถึงตามบทบาทโดยใช้ Row Level Security ใน PostgreSQL

การพัฒนาธีม ศึกษาการใช้ Row Level Security ใน PostgreSQL и สำหรับคำตอบโดยละเอียด บน ความคิดเห็น.

กลยุทธ์ที่ใช้เกี่ยวข้องกับการใช้แนวคิดของ "ตรรกะทางธุรกิจในฐานข้อมูล" ซึ่งได้อธิบายไว้ในรายละเอียดเพิ่มเติมเล็กน้อยที่นี่ - ศึกษาการนำตรรกะทางธุรกิจไปใช้ในระดับของฟังก์ชันที่เก็บไว้ของ PostgreSQL

ส่วนทางทฤษฎีได้อธิบายไว้อย่างดีในเอกสารประกอบ PostgreSQL - นโยบายการป้องกันแถว. ด้านล่างนี้เป็นการใช้งานจริง งานทางธุรกิจเฉพาะ - แบบอย่างของการเข้าถึงข้อมูล

ไม่มีอะไรใหม่ในบทความไม่มีความหมายที่ซ่อนอยู่และความรู้ที่เป็นความลับ เป็นเพียงภาพร่างเกี่ยวกับการนำแนวคิดทางทฤษฎีไปใช้จริง หากใครสนใจอ่านต่อ หากคุณไม่สนใจก็อย่าเสียเวลา

คำแถลงปัญหา

จำเป็นต้องแยกการเข้าถึงเพื่อดู/แทรก/แก้ไข/ลบเอกสารตามบทบาทของผู้ใช้แอปพลิเคชัน บทบาทหมายถึงรายการในตาราง บทบาท เชื่อมโยงกับความสัมพันธ์แบบกลุ่มต่อกลุ่มกับตาราง ผู้ใช้. รายละเอียดการใช้งานของตารางจะถูกละเว้นเนื่องจากความไม่สำคัญ ละเว้นรายละเอียดการใช้งานเฉพาะโดเมนด้วย

การดำเนินงาน

สร้างบทบาท สคีมา ตาราง

การสร้างวัตถุฐานข้อมูล

CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
  id integer ,         --id документа
  man_id integer , --id менеджера документа
  stat_id integer ,  --id статуса документа
  ...
  is_del BOOLEAN DEFAULT FALSE 
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;

การสร้างฟังก์ชั่นเพื่อนำ RLS ไปใช้

ตรวจสอบว่าสามารถเลือกแถวได้หรือไม่

ตรวจสอบ_เลือก

CREATE OR REPLACE FUNCTION store.check_select ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
  result boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- DBA имеет доступ ко всем документам
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  --Если документ имеет метку 'удален' - не показывать в выборке
  SELECT
    is_del
  INTO
    result
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF result = TRUE
 THEN
   RETURN FALSE ;
 END IF ;
 --------------------------------

 --Получить id текущего пользователя
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Получить id менеджера документа
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Если менеджер документа не текущий пользователь или менеджер не назначен
 --добавить документ в выборку
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE  ;
 ELSE
   --Получить текущий статус документа
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Если статус позволяет просмотреть документ - добавить документ в выборку                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE ;
   ELSE
   --Иначе - исключить документ из выборки
     RETURN FALSE ;
    END IF ;
  END IF ;
  --------------------------------

 RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_select( store.docs.id%TYPE  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) FROM public; 
GRANT EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) TO service_functions; 

ตรวจสอบความสามารถในการแทรกแถว

ตรวจสอบ_แทรก

CREATE OR REPLACE FUNCTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
  curr_role_id integer ;
BEGIN
  --DBA может добавлять строку в любом случае
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Получить id роли текущего пользователя 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Если роль допускает возможность создания нового документа
--разрешить
IF curr_role_id = 3 OR curr_role_id = 5     
THEN
  RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE  ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) TO service_functions; 

ตรวจสอบความสามารถในการลบแถว

ตรวจสอบ_ลบ

CREATE OR REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNS boolean AS $$
BEGIN  
  --Только DBA может удалять строку 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  RETURN FALSE ;
END
$$ LANGUAGE plpgsql
SECURITY DEFINER;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE  ) FROM public;

ตรวจสอบความสามารถในการอัปเดตแถว

update_using

CREATE OR REPLACE FUNCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean  )
RETURNS boolean AS $$
BEGIN  
   --Документы имеющие статус 'удален' - не редактируются
   IF is_del 
   THEN
     RETURN FALSE ;
 ELSE
    RETURN TRUE ;
  END IF ;

END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.update_using(  store.docs.id%TYPE ,  boolean  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.update_using(  store.docs.id%TYPE ,  boolean  ) FROM public;
GRANT EXECUTE ON FUNCTION store.update_using( store.docs.id%TYPE  ) TO service_functions;

อัพเดต_ตรวจสอบ

CREATE OR REPLACE FUNCTION store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
DECLARE
  current_rid integer ;
  current_statid integer ;
BEGIN                

  --DBA может просматривать строку 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Получить id роли текущего пользователя 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------                            

 --Удаление документа - изменение признака 
 IF is_deleted
 THEN
   --Если роль пользователя ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      INTO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      --Документ в статусе *** нельзя удалить 
      IF current_status_id = 11
      THEN
         RETURN FALSE ;
      ELSE
      --Можно удалить документ в других статусах
        RETURN TRUE ;
      END IF ;

    --Иначе , если роль пользователя ***
    ELSIF current_role_id = 5            
    THEN
      --Все статусы документа 
      RETURN TRUE ;
    ELSE
      --Другие пользователи не могут удалять документы
      RETURN FALSE ;
    END IF ;
 ELSE      
   --Обновление документа разрешено
    RETURN TRUE ;
END IF ;

RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.update_with_check( storg.docs.id%TYPE ,  boolean   ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.update_with_check( storg.docs.id%TYPE ,  boolean   )  FROM public;
GRANT EXECUTE ON FUNCTION store.update_with_check( store.docs.id%TYPE  ) TO service_functions;

เปิดใช้งานนโยบายความปลอดภัยระดับแถวสำหรับตาราง

เปิดใช้งานการรักษาความปลอดภัยระดับแถว

ALTER TABLE store.docs ENABLE ROW LEVEL SECURITY ;

CREATE POLICY doc_select ON store.docs FOR SELECT TO service_functions USING ( (SELECT store.check_select(id)) );
CREATE POLICY doc_insert ON store.docs FOR INSERT TO service_functions WITH CHECK ( (SELECT store.check_insert(id)) );
CREATE POLICY docs_delete ON store.docs FOR DELETE TO service_functions USING ( (SELECT store.check_delete(id)) );

CREATE POLICY doc_update_using ON store.docs FOR UPDATE TO service_functions USING ( (SELECT store.update_using(id , is_del )) );
CREATE POLICY doc_update_check ON store.docs FOR UPDATE TO service_functions  WITH CHECK ( (SELECT store.update_with_check(id , is_del )) );

ทั้งหมด

มันได้ผล.

กลยุทธ์ที่นำเสนอทำให้สามารถถ่ายโอนการดำเนินการตามแบบจำลองจากระดับของหน้าที่ทางธุรกิจไปยังระดับของการจัดเก็บข้อมูล

สามารถใช้ฟังก์ชันเป็นเทมเพลตเพื่อปรับใช้โมเดลการซ่อนข้อมูลที่ซับซ้อนมากขึ้นตามความต้องการทางธุรกิจ

ที่มา: will.com