Implementatie van een rolmodel voor toegang met gebruik van Row Level Security in PostgreSQL

Thema ontwikkeling Studie over de implementatie van Row Level Security in PostgreSQL en voor een uitgebreide respons en een werkende opdracht krijgen. opmerking.

De gebruikte strategie houdt in dat het concept 'Zakelijke logica in de database' wordt toegepast, wat hier iets gedetailleerder werd beschreven — Studie over de implementatie van businesslogica op het niveau van opgeslagen functies in PostgreSQL

Het theoretische gedeelte is uitstekend beschreven in de documentatie Postgres Pro — Rijbescherming beleidsregels. Hieronder wordt de praktische implementatie besproken van een specifieke zakelijke taak — het rolmodel voor gegevens toegang.

In het artikel is niets nieuws, er is geen verborgen betekenis of geheime kennis. Gewoon een schets van de praktische uitvoering van een theoretisch idee. Als iemand geïnteresseerd is — lees het. Als het je niet interesseert — verspil je tijd niet.

Taakstelling

Toegang voor het bekijken/invoegen/wijzigen/verwijderen van een document moet worden beperkt op basis van de rol van de gebruikersapplicatie. Onder rol verstaan we een record in de tabel roles die verbonden is met de veel-op-veel relatie met de tabel gebruikers. De details van de implementatie van de tabellen zijn omwille van de trivialiteit weggelaten. Ook zijn specifieke details van de implementatie met betrekking tot het onderwerp weggelaten.

Implementatie

We creëren rollen, schema's en de tabel

Database objecten creëren

CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
  id integer ,         --id van het document
  man_id integer , --id van de document manager
  stat_id integer ,  --id van de status van het document
  ...
  is_del BOOLEAN DEFAULT FALSE 
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;

We creëren functies voor de implementatie van RLS

Controle of SELECT op een rij kan worden uitgevoerd

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 heeft toegang tot alle documenten
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  --Als het document het label 'verwijderd' heeft - niet weergeven in de selectie
  SELECT
    is_del
  INTO
    result
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF result = TRUE
 THEN
   RETURN FALSE ;
 END IF ;
 --------------------------------

 --Haal id van de huidige gebruiker op
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Haal id van de documentmanager op
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Als de documentmanager niet de huidige gebruiker is of als de manager niet is toegewezen
 --voeg document toe aan de selectie
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE  ;
 ELSE
   --Haal de huidige status van het document op
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Als de status het bekijken van het document toestaat - voeg document toe aan de selectie                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE ;
   ELSE
   --Anders - sluit document uit van de selectie
     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; 

Controleer de mogelijkheid om een rij INSERT uit te voeren

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 kan altijd een rij toevoegen
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Haal id van de rol van de huidige gebruiker op 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Als de rol het maken van een nieuw document toestaat
--goedkeuren
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; 

Controleer de mogelijkheid om een rij DELETE uit te voeren

check_delete

CREATE OR REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNS boolean AS $$
BEGIN  
  --Alleen DBA kan een rij verwijderen 
  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;

Controleer de mogelijkheid om een rij UPDATE uit te voeren.

update_using

CREATE OR REPLACE FUNCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
BEGIN  
   -- Documenten met de status 'verwijderd' kunnen niet worden bewerkt
   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 kan de rij bekijken 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 -- Verkrijg de rol-id van de huidige gebruiker 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------                            

 -- Verwijdering van het document - wijziging van de status 
 IF is_deleted
 THEN
   -- Als de rol van de gebruiker ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      INTO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      -- Document in status *** kan niet worden verwijderd 
      IF current_status_id = 11
      THEN
         RETURN FALSE ;
      ELSE
      -- Document kan in andere statussen worden verwijderd
        RETURN TRUE ;
      END IF ;

    -- Anders, als de rol van de gebruiker ***
    ELSIF current_role_id = 5            
    THEN
      -- Alle statussen van het document 
      RETURN TRUE ;
    ELSE
      -- Andere gebruikers kunnen geen documenten verwijderen
      RETURN FALSE ;
    END IF ;
 ELSE      
   -- Bijwerken van het document is toegestaan
    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;

Het inschakelen van Row Level Security voor de tabel.

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

Conclusie

Dit werkt.

De voorgestelde strategie heeft de implementatie van het rolmodel van het niveau van bedrijfsfuncties naar het niveau van gegevensopslag verplaatst.

Functies kunnen worden gebruikt als sjabloon voor de implementatie van geavanceerdere modellen voor gegevensverhulling, indien de zakelijke vereisten dat vereisen.

Bron: habr.com