{"id":92114,"date":"2020-08-23T19:41:56","date_gmt":"2020-08-23T17:41:56","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/realizacziya-rolevoj-modeli-dostupa-s-ispolzovaniem-row-level-security-v-postgresql"},"modified":"2020-08-23T19:41:56","modified_gmt":"2020-08-23T17:41:56","slug":"realizacziya-rolevoj-modeli-dostupa-s-ispolzovaniem-row-level-security-v-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/nl\/blog\/administrirovanie\/realizacziya-rolevoj-modeli-dostupa-s-ispolzovaniem-row-level-security-v-postgresql","title":{"rendered":"Implementatie van een rolmodel voor toegang met gebruik van Row Level Security in PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Thema ontwikkeling Studie over de implementatie van Row Level Security in PostgreSQL<\/a><\/noindex> en voor een uitgebreide respons<\/b> en een werkende opdracht krijgen. opmerking.<\/a><\/noindex><\/p>\n

De gebruikte strategie houdt in dat het concept 'Zakelijke logica in de database' wordt toegepast, wat hier iets gedetailleerder werd beschreven \u2014 Studie over de implementatie van businesslogica op het niveau van opgeslagen functies in PostgreSQL<\/a><\/noindex><\/p>\n

Het theoretische gedeelte is uitstekend beschreven in de documentatie Postgres Pro<\/a><\/noindex> \u2014 Rijbescherming beleidsregels<\/a><\/noindex>. Hieronder wordt de praktische implementatie besproken van een specifieke zakelijke taak \u2014 het rolmodel voor gegevens toegang.<\/b><\/p>\n

\"Implementatie<\/p>\n

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\u00efnteresseerd is \u2014 lees het. Als het je niet interesseert \u2014 verspil je tijd niet.<\/p><\/blockquote>\n

<\/a><\/noindex><\/p>\n

Taakstelling<\/h2>\n

\nToegang 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<\/b> die verbonden is met de veel-op-veel relatie met de tabel gebruikers<\/b>. 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.<\/p>\n

Implementatie<\/h2>\n

<\/p>\n

We cre\u00ebren rollen, schema's en de tabel<\/h4>\n

<\/p>\n

Database objecten cre\u00ebren<\/b><\/p>\n

CREATE ROLE store;\nCREATE SCHEMA store AUTHORIZATION store;\nCREATE TABLE store.docs\n(\n  id integer ,         --id van het document\n  man_id integer , --id van de document manager\n  stat_id integer ,  --id van de status van het document\n  ...\n  is_del BOOLEAN DEFAULT FALSE \n);\nALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);\nALTER TABLE store.docs OWNER TO store ;\n<\/code><\/pre>\n
<\/p>\n
We cre\u00ebren functies voor de implementatie van RLS<\/h4>\n
\nControle of SELECT op een rij kan worden uitgevoerd<\/p>\n
                        check_select<\/b><\/p>\n
CREATE OR REPLACE FUNCTION store.check_select ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$\nDECLARE\n  result boolean ;\n  curr_pid integer ;\n  curr_stat_id integer ;\n  doc_man_id integer ;\nBEGIN \n  -- DBA heeft toegang tot alle documenten\n  IF SESSION_USER = 'curr_dba'\n  THEN\n    RETURN TRUE ;\n  END IF ;\n  --------------------------------\n\n  --Als het document het label 'verwijderd' heeft - niet weergeven in de selectie\n  SELECT\n    is_del\n  INTO\n    result\n  FROM\n    store.docs\n  WHERE\n    id = current_id ;\n IF result = TRUE\n THEN\n   RETURN FALSE ;\n END IF ;\n --------------------------------\n\n --Haal id van de huidige gebruiker op\n SELECT\n   service_function.get_curr_pid ()\n INTO\n   curr_pid ;\n --------------------------------\n\n --Haal id van de documentmanager op\n SELECT\n   man_id\n INTO\n   doc_man_id\n FROM\n   store.docs\n WHERE\n   id = current_id ;\n --------------------------------\n\n --Als de documentmanager niet de huidige gebruiker is of als de manager niet is toegewezen\n --voeg document toe aan de selectie\n IF doc_man_id != curr_pid OR doc_man_id IS NULL\n THEN\n   RETURN TRUE  ;\n ELSE\n   --Haal de huidige status van het document op\n   SELECT\n     stat_id                                         \n   INTO\n     curr_statid\n   FROM\n     store.docs\n   WHERE\n     id = current_id ;\n    \n   --Als de status het bekijken van het document toestaat - voeg document toe aan de selectie                     \n   IF curr_statid = 4 OR curr_statid = 9\n   THEN\n     RETURN TRUE ;\n   ELSE\n   --Anders - sluit document uit van de selectie\n     RETURN FALSE ;\n    END IF ;\n  END IF ;\n  --------------------------------\n\n RETURN FALSE ;\nEND\n$$ LANGUAGE plpgsql SECURITY DEFINER;\nALTER FUNCTION store.check_select( store.docs.id%TYPE  ) OWNER TO store ;\nREVOKE EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) FROM public; \nGRANT EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) TO service_functions; \n<\/code><\/pre>\n
\nControleer de mogelijkheid om een rij INSERT uit te voeren<\/p>\n
                        check_insert<\/b><\/p>\n
CREATE OR REPLACE FUNCTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$\nDECLARE\n  curr_role_id integer ;\nBEGIN\n  --DBA kan altijd een rij toevoegen\n  IF SESSION_USER = 'curr_dba'\n  THEN\n    RETURN TRUE ;\n  END IF ;\n  --------------------------------\n\n --Haal id van de rol van de huidige gebruiker op \n SELECT\n   service_functions.current_rid()\n  INTO\n    curr_role_id ;\n --------------------------------\n\n--Als de rol het maken van een nieuw document toestaat\n--goedkeuren\nIF curr_role_id = 3 OR curr_role_id = 5     \nTHEN\n  RETURN TRUE ;\nEND IF ;\n--------------------------------\nRETURN FALSE  ;\nEND\n$$ LANGUAGE plpgsql SECURITY DEFINER;\nALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) OWNER TO store ;\nREVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) FROM public;\nGRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) TO service_functions; \n<\/code><\/pre>\n
\nControleer de mogelijkheid om een rij DELETE uit te voeren<\/p>\n
                        check_delete<\/b><\/p>\n
CREATE OR REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )\nRETURNS boolean AS $$\nBEGIN  \n  --Alleen DBA kan een rij verwijderen \n  IF SESSION_USER = 'curr_dba'\n  THEN\n    RETURN TRUE ;\n  END IF ;\n  --------------------------------\n\n  RETURN FALSE ;\nEND\n$$ LANGUAGE plpgsql\nSECURITY DEFINER;\nALTER FUNCTION store.check_delete( store.docs.id%TYPE  ) OWNER TO store ;\nREVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE  ) FROM public;<\/code><\/pre>\n
\nControleer de mogelijkheid om een rij UPDATE uit te voeren.<\/p>\n
                        update_using<\/b><\/p>\n
CREATE OR REPLACE FUNCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean )\nRETURNS boolean AS $$\nBEGIN  \n   -- Documenten met de status 'verwijderd' kunnen niet worden bewerkt\n   IF is_del \n   THEN\n     RETURN FALSE ;\n ELSE\n    RETURN TRUE ;\n  END IF ;\n\nEND\n$$ LANGUAGE plpgsql SECURITY DEFINER;\nALTER FUNCTION store.update_using(  store.docs.id%TYPE ,  boolean  ) OWNER TO store ;\nREVOKE EXECUTE ON FUNCTION store.update_using(  store.docs.id%TYPE ,  boolean  ) FROM public;\nGRANT EXECUTE ON FUNCTION store.update_using( store.docs.id%TYPE  ) TO service_functions;<\/code><\/pre>\n
<\/p>\n
                        update_check<\/b><\/p>\n
CREATE OR REPLACE FUNCTION store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )\nRETURNS boolean AS $$\nDECLARE\n  current_rid integer ;\n  current_statid integer ;\nBEGIN                \n\n  -- DBA kan de rij bekijken \n  IF SESSION_USER = 'curr_dba'\n  THEN\n    RETURN TRUE ;\n  END IF ;\n  --------------------------------\n\n -- Verkrijg de rol-id van de huidige gebruiker \n SELECT\n   service_functions.current_rid()\n  INTO\n    curr_role_id ;\n --------------------------------                            \n\n -- Verwijdering van het document - wijziging van de status \n IF is_deleted\n THEN\n   -- Als de rol van de gebruiker ***\n   IF current_role_id = 3        \n   THEN\n      SELECT\n        stat_id                                          \n      INTO\n        curr_statid\n      FROM\n        store.docs\n      WHERE\n        id = current_id ;\n\n      -- Document in status *** kan niet worden verwijderd \n      IF current_status_id = 11\n      THEN\n         RETURN FALSE ;\n      ELSE\n      -- Document kan in andere statussen worden verwijderd\n        RETURN TRUE ;\n      END IF ;\n\n    -- Anders, als de rol van de gebruiker ***\n    ELSIF current_role_id = 5            \n    THEN\n      -- Alle statussen van het document \n      RETURN TRUE ;\n    ELSE\n      -- Andere gebruikers kunnen geen documenten verwijderen\n      RETURN FALSE ;\n    END IF ;\n ELSE      \n   -- Bijwerken van het document is toegestaan\n    RETURN TRUE ;\nEND IF ;\n\nRETURN FALSE ;\nEND\n$$ LANGUAGE plpgsql SECURITY DEFINER;\nALTER FUNCTION store.update_with_check( storg.docs.id%TYPE ,  boolean   ) OWNER TO store ;\nREVOKE EXECUTE ON FUNCTION store.update_with_check( storg.docs.id%TYPE ,  boolean   )  FROM public;\nGRANT EXECUTE ON FUNCTION store.update_with_check( store.docs.id%TYPE  ) TO service_functions;<\/code><\/pre>\n
\nHet inschakelen van Row Level Security voor de tabel.<\/p>\n
                        ENABLE ROW LEVEL SECURITY<\/b><\/p>\n
ALTER TABLE store.docs ENABLE ROW LEVEL SECURITY ;\n\nCREATE POLICY doc_select ON store.docs FOR SELECT TO service_functions USING ( (SELECT store.check_select(id)) );\nCREATE POLICY doc_insert ON store.docs FOR INSERT TO service_functions WITH CHECK ( (SELECT store.check_insert(id)) );\nCREATE POLICY docs_delete ON store.docs FOR DELETE TO service_functions USING ( (SELECT store.check_delete(id)) );\n\nCREATE POLICY doc_update_using ON store.docs FOR UPDATE TO service_functions USING ( (SELECT store.update_using(id , is_del )) );\nCREATE POLICY doc_update_check ON store.docs FOR UPDATE TO service_functions  WITH CHECK ( (SELECT store.update_with_check(id , is_del )) );<\/code><\/pre>\n
<\/p>\n
Conclusie<\/h2>\n
\nDit werkt.<\/p>\n
De voorgestelde strategie heeft de implementatie van het rolmodel van het niveau van bedrijfsfuncties naar het niveau van gegevensopslag verplaatst. <\/p>\n
Functies kunnen worden gebruikt als sjabloon voor de implementatie van geavanceerdere modellen voor gegevensverhulling, indien de zakelijke vereisten dat vereisen.
\n
Bron: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0420\u0430\u0437\u0432\u0438\u0442\u0438\u0435 \u0442\u0435\u043c\u044b \u042d\u0442\u044e\u0434 \u043f\u043e \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0438 Row Level Secutity \u0432 PostgreSQL \u0438 \u0434\u043b\u044f \u0440\u0430\u0437\u0432\u0435\u0440\u043d\u0443\u0442\u043e\u0433\u043e \u043e\u0442\u0432\u0435\u0442\u0430 \u043d\u0430 \u043a\u043e\u043c\u043c\u0435\u043d\u0442\u0430\u0440\u0438\u0439. \u0418\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u043d\u0430\u044f \u0441\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u044f \u043f\u043e\u0434\u0440\u0430\u0437\u0443\u043c\u0435\u0432\u0430\u0435\u0442 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 \u043a\u043e\u043d\u0446\u0435\u043f\u0446\u0438\u0438 \u00ab\u0411\u0438\u0437\u043d\u0435\u0441-\u043b\u043e\u0433\u0438\u043a\u0430 \u0432 \u0411\u0414\u00bb, \u0447\u0442\u043e \u0431\u044b\u043b\u043e \u0447\u0443\u0442\u044c \u043f\u043e\u0434\u0440\u043e\u0431\u043d\u0435\u0435 \u043e\u043f\u0438\u0441\u0430\u043d\u043e \u0437\u0434\u0435\u0441\u044c \u2014 \u042d\u0442\u044e\u0434 \u043f\u043e \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044f \u0431\u0438\u0437\u043d\u0435\u0441-\u043b\u043e\u0433\u0438\u043a\u0438 \u043d\u0430 \u0443\u0440\u043e\u0432\u043d\u0435 \u0445\u0440\u0430\u043d\u0438\u043c\u044b\u0445 \u0444\u0443\u043d\u043a\u0446\u0438\u0439 PostgreSQL \u0422\u0435\u043e\u0440\u0435\u0442\u0438\u0447\u0435\u0441\u043a\u0430\u044f \u0447\u0430\u0441\u0442\u044c \u043e\u0442\u043b\u0438\u0447\u043d\u043e \u043e\u043f\u0438\u0441\u0430\u043d\u0430 \u0432 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438 Postgres Pro \u2014 \u041f\u043e\u043b\u0438\u0442\u0438\u043a\u0438 \u0437\u0430\u0449\u0438\u0442\u044b \u0441\u0442\u0440\u043e\u043a. \u041d\u0438\u0436\u0435 \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0435\u043d\u0430 \u043f\u0440\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u0430\u044f […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":92115,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-92114","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Implementatie van een rollenmodel voor toegang met behulp van Row Level Security in PostgreSQL | ProHoster","description":"Ontwikkeling van het onderwerp Etude over de implementatie van Row Level Security in PostgreSQL en voor een uitgebreid antwoord op","canonical_url":"https:\/\/prohoster.info\/nl\/blog\/administrirovanie\/realizacziya-rolevoj-modeli-dostupa-s-ispolzovaniem-row-level-security-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"nl_NL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0420\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044f \u0440\u043e\u043b\u0435\u0432\u043e\u0439 \u043c\u043e\u0434\u0435\u043b\u0438 \u0434\u043e\u0441\u0442\u0443\u043f\u0430 \u0441 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435\u043c Row Level Security \u0432 PostgreSQL | ProHoster","og:description":"\u0420\u0430\u0437\u0432\u0438\u0442\u0438\u0435 \u0442\u0435\u043c\u044b \u042d\u0442\u044e\u0434 \u043f\u043e \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0438 Row Level Secutity \u0432 PostgreSQL \u0438 \u0434\u043b\u044f \u0440\u0430\u0437\u0432\u0435\u0440\u043d\u0443\u0442\u043e\u0433\u043e \u043e\u0442\u0432\u0435\u0442\u0430 \u043d\u0430","og:url":"https:\/\/prohoster.info\/nl\/blog\/administrirovanie\/realizacziya-rolevoj-modeli-dostupa-s-ispolzovaniem-row-level-security-v-postgresql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-08-23T17:41:56+00:00","article:modified_time":"2020-08-23T17:41:56+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"92114","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 12:14:23","updated":"2022-10-06 10:04:45","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/posts\/92114","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/comments?post=92114"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/posts\/92114\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/media\/92115"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/media?parent=92114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/categories?post=92114"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/tags?post=92114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}