Na-eme ihe nlere nweta dabere na ọrụ site na iji nchekwa ọkwa ọkwa na PostgreSQL

Mmepe isiokwu Ọmụmụ maka imejuputa nchekwa ọkwa ọkwa na PostgreSQL и maka azịza zuru ezu on okwu.

Usoro a na-eji gụnyere iji echiche "Business Logic in Database", nke akọwara n'ime ntakịrị nkọwa ebe a - Ọmụmụ banyere imejuputa mgbagha azụmahịa na ọkwa nke ọrụ echekwara PostgreSQL

A na-akọwa akụkụ nke usoro ọmụmụ nke ọma na akwụkwọ ahụ PostgreSQL - Amụma nchedo ahịrị. N'okpuru bụ mmejuputa iwu bara uru ọrụ azụmahịa kpọmkwem - ihe nlereanya maka ịnweta data.

Ọ dịghị ihe ọhụrụ na isiokwu, ọ dịghị ihe zoro ezo pụtara ma ọ bụ ihe ọmụma nzuzo. Naanị ihe osise gbasara mmejuputa atumatu nke echiche echiche. Ọ bụrụ na onye ọ bụla nwere mmasị, gụọ ya. Ọ bụrụ na ị nweghị mmasị, egbula oge gị.

Ulationkpụzi nsogbu a

Ọ dị mkpa igbochi ịnweta nlele / ntinye / gbanwee / ihichapụ akwụkwọ dịka ọrụ onye ọrụ ngwa si dị. Ọrụ na-ezo aka na ntinye na tebụl ọrụ jikọtara ya na ọtụtụ-na-ọtụtụ mmekọrịta na tebụl ọrụ. A na-ahapụ nkọwa nke mmejuputa nke tebụl, n'ihi ihe na-adịghị mkpa. Ewepụkwara nkọwa mmejuputa iwu nke metụtara mpaghara isiokwu.

Mmejuputa iwu

Mepụta ọrụ, schema, tebụl

Ịmepụta Ihe Database

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 ;

Ịmepụta ọrụ iji mejuputa RLS

Na-elele ikike ịme ahịrị ahọpụtara

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; 

Na-enyocha ikike itinye ahịrị

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; 

Na-enyocha ikike ihichapụ ahịrị

check_ehichapụ

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;

Na-enyocha ikike imelite otu ahịrị.

update_iji

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

Kwado amụma nchekwa ọkwa ọkwa maka tebụl.

Kwado nchekwa ọkwa ọkwa

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

Nsonaazụ

Ọ na-arụ ọrụ.

Usoro a tụrụ aro mere ka o kwe omume ịnyefe mmejuputa ihe nlereanya site na ọkwa nke ọrụ azụmahịa na ọkwa nchekwa data.

Enwere ike iji ọrụ ndị a dị ka ndebiri iji mejuputa ụdị nzuzo data ọkaibe karịa ma ọ bụrụ na azụmahịa chọrọ ya.

isi: www.habr.com