Реализация на роловия модел за достъп с използване на Row Level Security в PostgreSQL

Развитие на темата Есенно изложение на реализацията на Row Level Security в PostgreSQL и за разширен отговор на коментар.

Използваната стратегия предвижда концепцията „Бизнес логика в БД“, което беше описано по-подробно тук — Есенно изложение на реализиране на бизнес логика на ниво храним функции PostgreSQL

Теоретичната част е отлично описана в документацията Postgres Pro — Политики за защита на редовете. По-долу е разгледана практическата реализация на конкретна бизнес задача — ролевата модел на достъп до данни.

В статията няма нищо ново, няма скрито значение и тайни знания. Просто скица на практическата реализация на теоретична идея. Ако някой се интересува — чете. Ако не се интересува — не губете времето си напразно.

Формулиране на задачата

Необходимо е да се разграничат правата за преглед/вмъкване/промяна/изтриване на документа в съответствие с ролята на потребителя на приложението. Под ролята се разбира записа в таблицата roles свързана с отношение много-към-много с таблицата users. Детайлите от реализацията на таблиците, поради тривиалността им, са опуснати. Също така са опуснати конкретни детайли от реализацията, свързани с предметната област.

Реализация

Създаваме роли, схеми, таблица

Създаване на обекти на БД

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

Проверка на възможността за изпълнение на SELECT ред

check_select

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; 

Проверка на възможността за извършване на INSERT на ред

check_insert

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; 

Проверка на възможността за извършване на DELETE на ред

check_delete

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 на ред.

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;

update_check

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( store.docs.id%TYPE ,  boolean   ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.update_with_check( store.docs.id%TYPE ,  boolean   )  FROM public;
GRANT EXECUTE ON FUNCTION store.update_with_check( store.docs.id%TYPE  ) TO service_functions;

Включване на политиката Row Level Security за таблицата.

ENABLE ROW LEVEL SECURITY

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 )) );

Резюме

Това работи.

Предложената стратегия позволи преместването на реализацията на модел на роля от нивото на бизнес функции до нивото на съхранение на данни.

Функциите могат да бъдат използвани като шаблон за реализиране на по-сложни модели на скриване на данни, ако бизнес изискванията го изискват.

Източник: habr.com