{"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\/no\/blog\/administrirovanie\/realizacziya-rolevoj-modeli-dostupa-s-ispolzovaniem-row-level-security-v-postgresql","title":{"rendered":"Implementering av en rollebasert tilgangsmodell ved hjelp av Row Level Security i PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Utvikling av tema Etude om implementering av Row Level Security i PostgreSQL<\/a><\/noindex> og for et utfyllende svar<\/b> En funksjon har blitt kommentar.<\/a><\/noindex><\/p>\n

Den brukte strategien inneb\u00e6rer konseptet \u00abForretningslogikk i DB\u00bb, som ble beskrevet litt mer detaljert her \u2014 Etude om implementering av forretningslogikk p\u00e5 niv\u00e5et av lagrede funksjoner i PostgreSQL<\/a><\/noindex><\/p>\n

Teoretisk del er godt beskrevet i dokumentasjonen Postgres Pro<\/a><\/noindex> \u2014 Rettighetsbeskyttelsespolitikk for rader<\/a><\/noindex>. Nedenfor er den praktiske implementeringen vurdert av en bestemt forretningsoppgave \u2014 en rollemodell for datatilgang.<\/b><\/p>\n

\"Implementering<\/p>\n

Artikkelen inneholder ingenting nytt, ingen skjulte betydninger eller hemmelig kunnskap. Bare en skisse over den praktiske implementeringen av en teoretisk id\u00e9. Hvis noen er interessert \u2014 les. Hvis ikke \u2014 ikke kast bort tiden din.<\/p><\/blockquote>\n

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

Definering av oppgaven<\/h2>\n

\nDet er n\u00f8dvendig \u00e5 skille mellom tilgang til visning\/insetting\/endring\/sletting av dokumentet i henhold til brukerens rolle i applikasjonen. Med rolle menes en post i tabellen roles<\/b> relatert til mange-til-mange-forhold med tabellen users<\/b>. Detaljer om implementeringen av tabellene er utelatt p\u00e5 grunn av trivielle grunner. Spesifikke detaljer knyttet til fagomr\u00e5det er ogs\u00e5 utelatt.<\/p>\n

Implementering<\/h2>\n

<\/p>\n

Vi oppretter roller, skjema, tabellen<\/h4>\n

<\/p>\n

Oppretting av DB-objekter<\/b><\/p>\n

CREATE ROLE store;\nCREATE SCHEMA store AUTHORIZATION store;\nCREATE TABLE store.docs\n(\n  id integer ,         --id for dokumentet\n  man_id integer , --id for dokumentets manager\n  stat_id integer ,  --id for dokumentets status\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
Vi lager funksjoner for implementering av RLS<\/h4>\n
\nSjekk muligheten til \u00e5 utf\u00f8re SELECT p\u00e5 raden<\/p>\n
                        check_select<\/b><\/p>\n
OPPRETT FUNKSJON store.check_select ( current_id store.docs.id%TYPE ) RETURNER boolean AS $$\nDECLARE\n  result boolean ;\n  curr_pid integer ;\n  curr_stat_id integer ;\n  doc_man_id integer ;\nBEGIN \n  -- DBA har tilgang til alle dokumenter\n  IF SESSION_USER = 'curr_dba'\n  THEN\n    RETURN TRUE ;\n  END IF ;\n  --------------------------------\n\n  --Hvis dokumentet har merkelapp 'slettet' - ikke vis i utvalget\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 --F\u00e5 id til aktuell bruker\n SELECT\n   service_function.get_curr_pid ()\n INTO\n   curr_pid ;\n --------------------------------\n\n --F\u00e5 id til dokumentets manager\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 --Hvis dokumentets manager ikke er aktuell bruker eller manager ikke er utnevnt\n --legge dokumentet til i utvalget\n IF doc_man_id != curr_pid OR doc_man_id IS NULL\n THEN\n   RETURN TRUE  ;\n ELSE\n   --F\u00e5 n\u00e5v\u00e6rende status for dokumentet\n   SELECT\n     stat_id                                         \n   INTO\n     curr_statid\n   FROM\n     store.docs\n   WHERE\n     id = current_id ;\n    \n   --Hvis status tillater visning av dokumentet - legg til dokumentet i utvalget                     \n   IF curr_statid = 4 OR curr_statid = 9\n   THEN\n     RETURN TRUE ;\n   ELSE\n   --Ellers - ekskluder dokumentet fra utvalget\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  ) EIER TIL store ;\nREVOKE EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) FRA public; \nGRANT EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) TIL service_functions; \n<\/code><\/pre>\n
\nSjekk muligheten for \u00e5 utf\u00f8re INSERT av rad<\/p>\n
                        check_insert<\/b><\/p>\n
OPPRETT FUNKSJON store.check_insert ( current_id store.docs.id%TYPE ) RETURNER boolean AS $$\nDECLARE\n  curr_role_id integer ;\nBEGIN\n  --DBA kan alltid legge til en rad\n  IF SESSION_USER = 'curr_dba'\n  THEN\n    RETURN TRUE ;\n  END IF ;\n  --------------------------------\n\n --F\u00e5 id til rollen til aktuell bruker \n SELECT\n   service_functions.current_rid()\n  INTO\n    curr_role_id ;\n --------------------------------\n\n--Hvis rollen tillater opprettelse av nytt dokument\n--tillat\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  ) EIER TIL store ;\nREVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) FRA public;\nGRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) TIL service_functions; \n<\/code><\/pre>\n
\nSjekk muligheten for \u00e5 utf\u00f8re DELETE av rad<\/p>\n
                        check_delete<\/b><\/p>\n
OPPRETT FUNKSJON store.check_delete ( current_id store.docs.id%TYPE )\nRETURNER boolean AS $$\nBEGIN  \n  --Bare DBA kan slette en rad \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  ) EIER TIL store ;\nREVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE  ) FRA public;<\/code><\/pre>\n
\nSjekk muligheten for \u00e5 utf\u00f8re UPDATE av rad.<\/p>\n
                        oppdatere_bruk<\/b><\/p>\n
OPPRETT ELLER BYTT UT FUNKSJON store.oppdatere_bruk ( n\u00e5v\u00e6rende_id store.docs.id%TYPE , er_del boolean )\nRETURNER boolean AS $$\nBEGIN  \n   --Dokumenter med status 'slettet' kan ikke redigeres\n   HVIS er_del \n   DA\n     RETURNER FALSE ;\n ELLERS\n    RETURNER TRUE ;\n  SLUTT HVIS ;\n\nSLUTT\n$$ SPR\u00c5K plpgsql SIKKERHET DEFINERER;\nENDRE FUNKSJON store.oppdatere_bruk( store.docs.id%TYPE ,  boolean  ) EIER TIL store ;\nFJERN UTF\u00d8RELSE P\u00c5 FUNKSJON store.oppdatere_bruk( store.docs.id%TYPE ,  boolean  ) FRA offentlig;\nGI UTF\u00d8RELSE P\u00c5 FUNKSJON store.oppdatere_bruk( store.docs.id%TYPE  ) TIL service_functions;<\/code><\/pre>\n
<\/p>\n
                        oppdatere_sjekk<\/b><\/p>\n
OPPRETT ELLER BYTT UT FUNKSJON store.oppdatere_med_sjekk ( n\u00e5v\u00e6rende_id store.docs.id%TYPE , er_del boolean )\nRETURNER boolean AS $$\nDECLARE\n  n\u00e5v\u00e6rende_rid integer ;\n  n\u00e5v\u00e6rende_statid integer ;\nBEGIN                \n\n  --DBA kan se raden \n  HVIS SESJON_BRUKER = 'curr_dba'\n  DA\n    RETURNER TRUE ;\n  SLUTT HVIS ;\n  --------------------------------\n\n --Hent id for rollen til den n\u00e5v\u00e6rende brukeren \n SELECT\n   service_functions.n\u00e5v\u00e6rende_rid()\n  INN I\n    curr_role_id ;\n --------------------------------                            \n\n --Sletting av dokument - endring av flagg \n HVIS er_slettet\n DA\n   --Hvis brukerens rolle ***\n   HVIS n\u00e5v\u00e6rende_rolle_id = 3        \n   DA\n      SELECT\n        stat_id                                          \n      INN I\n        curr_statid\n      FRA\n        store.docs\n      HVOR\n        id = n\u00e5v\u00e6rende_id ;\n\n      --Dokumentet med status *** kan ikke slettes \n      HVIS n\u00e5v\u00e6rende_status_id = 11\n      DA\n         RETURNER FALSE ;\n      ELLERS\n      --Kan slette dokumentet i andre statuser\n        RETURNER TRUE ;\n      SLUTT HVIS ;\n\n    --Ellers, hvis brukerens rolle ***\n    ELLER HVIS n\u00e5v\u00e6rende_rolle_id = 5            \n    DA\n      --Alle statusene til dokumentet \n      RETURNER TRUE ;\n    ELLER\n      --Andre brukere kan ikke slette dokumenter\n      RETURNER FALSE ;\n    SLUTT HVIS ;\n ELLERS      \n   --Oppdatering av dokumentet er tillatt\n    RETURNER TRUE ;\nSLUTT HVIS ;\n\nRETURNER FALSE ;\nSLUTT\n$$ SPR\u00c5K plpgsql SIKKERHET DEFINERER;\nENDRE FUNKSJON store.oppdatere_med_sjekk( storg.docs.id%TYPE ,  boolean   ) EIER TIL store ;\nFJERN UTF\u00d8RELSE P\u00c5 FUNKSJON store.oppdatere_med_sjekk( storg.docs.id%TYPE ,  boolean   ) FRA offentlig;\nGI UTF\u00d8RELSE P\u00c5 FUNKSJON store.oppdatere_med_sjekk( store.docs.id%TYPE  ) TIL service_functions;<\/code><\/pre>\n
\nAktivering av Row Level Security-policy for tabellen.<\/p>\n
                        AKTIVER ROW LEVEL SECURITY<\/b><\/p>\n
ENDRE TABELL store.docs AKTIVER ROW LEVEL SECURITY ;\n\nOPPRETT POLICY doc_select P\u00c5 store.docs FOR SELEKTERING TIL service_functions BRUKER ( (SELECT store.check_select(id)) );\nOPPRETT POLICY doc_insert P\u00c5 store.docs FOR SETTING TIL service_functions MED SJEKK ( (SELECT store.check_insert(id)) );\nOPPRETT POLICY docs_delete P\u00c5 store.docs FOR SLETTING TIL service_functions BRUKER ( (SELECT store.check_delete(id)) );\n\nOPPRETT POLICY doc_update_using P\u00c5 store.docs FOR OPPDATERING TIL service_functions BRUKER ( (SELECT store.oppdatere_bruk(id , er_del )) );\nOPPRETT POLICY doc_update_check P\u00c5 store.docs FOR OPPDATERING TIL service_functions MED SJEKK ( (SELECT store.oppdatere_med_sjekk(id , er_del )) );<\/code><\/pre>\n
<\/p>\n
Resultat<\/h2>\n
\nDet fungerer.<\/p>\n
Den foresl\u00e5tte strategien gjorde det mulig \u00e5 flytte implementeringen av rollemodellen fra niv\u00e5et for forretningsfunksjoner til niv\u00e5et for datalagring. <\/p>\n
Funksjoner kan brukes som en mal for implementering av mer sofistikerte modeller for dataskjuling, hvis forretningskravene tilsier det.
\n
Kilde: 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\udd47Implementering av en rollebasert tilgangsmodell med bruk av Row Level Security i PostgreSQL | ProHoster","description":"Utviklingen av emnet Etude om implementering av Row Level Security i PostgreSQL og for et utfyllende svar p\u00e5","canonical_url":"https:\/\/prohoster.info\/no\/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":"nn_NO","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\/no\/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\/no\/wp-json\/wp\/v2\/posts\/92114","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/comments?post=92114"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/posts\/92114\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/media\/92115"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/media?parent=92114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/categories?post=92114"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/tags?post=92114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}