Ke hoʻokō nei i kahi kumu hoʻohālike e pili ana i ka hana me ka Row Level Security ma PostgreSQL

Hoʻomohala kumuhana He haʻawina e pili ana i ka hoʻokō ʻana i ka Row Level Security ma PostgreSQL и no ka pane kiko'ī maluna o he olelo.

ʻO ka hoʻolālā i hoʻohana ʻia e pili ana i ka hoʻohana ʻana i ka manaʻo "Business Logic in the Database", i wehewehe ʻia ma kahi kikoʻī liʻiliʻi ma aneʻi - He haʻawina e pili ana i ka hoʻokō ʻana i ka loiloi ʻoihana ma ke kiʻekiʻe o nā hana mālama ʻia ʻo PostgreSQL

Ua wehewehe maikaʻi ʻia ka ʻāpana theoretical ma ka palapala PostgreSQL - Nā kulekele pale lālani. Aia ma lalo kahi hoʻokō pono kahi hana ʻoihana kikoʻī - he kumu hoʻohālike no ka loaʻa ʻana o ka ʻikepili.

ʻAʻohe mea hou ma ka ʻatikala, ʻaʻohe manaʻo huna a ʻike huna. He kiʻi kiʻi wale nō e pili ana i ka hoʻokō pono ʻana o kahi manaʻo theoretical. Inā hoihoi kekahi, e heluhelu. Inā ʻaʻole ʻoe hoihoi, mai hoʻopau i kou manawa.

Ka hoʻokumu ʻana i ka pilikia

Pono e kaohi i ke komo ʻana i ka nānā ʻana/hoʻokomo ʻana/hoʻololi/ holoi ʻana i kahi palapala e like me ke kuleana o ka mea hoʻohana. ʻO ke kuleana e pili ana i kahi komo i loko o ka papaʻaina nā hana pili me ka pilina nui me ka papaʻaina mea hoʻohana. Hoʻopau ʻia nā kikoʻī o ka hoʻokō ʻana i nā papa, ma muli o ka triviality. Ua kāpae ʻia nā kikoʻī hoʻokō kikoʻī e pili ana i ke kumuhana.

Ka hoʻokō

E hana i nā kuleana, nā papa hana, nā papa

Ke hana nei i nā mea waihona

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 ;

Ke hana nei i nā hana e hoʻokō i ka RLS

Ke nānā nei i ka hiki ke hana i nā lālani SELECT

kaha_koho

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; 

Ke nānā nei i ka hiki ke hoʻokomo i nā lālani

kaha_hookomo

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; 

Ke nānā nei i ka hiki ke HELE i kahi lālani

nānā_hoʻopau

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;

Ke nānā nei i ka hiki ke UPDATE i kahi lālani.

hoʻohou_hoʻohana

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;

hōʻoia_hōʻoia

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;

E ho'ā i ke kulekele Hoʻopalekana Lālani no kahi papaʻaina.

E HOA I KA PALAPALA LALANI

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

ʻO ka hopena

Hana ia.

ʻO ka hoʻolālā i manaʻo ʻia ua hiki ke hoʻololi i ka hoʻokō ʻana i ke kumu hoʻohālike mai ke kiʻekiʻe o nā hana ʻoihana i ka pae mālama ʻikepili.

Hiki ke hoʻohana ʻia nā hana ma ke ʻano he laʻana no ka hoʻokō ʻana i nā hiʻohiʻona huna ʻikepili ʻoi aku ka maʻalahi inā koi ʻia nā koi ʻoihana.

Source: www.habr.com