{"id":92107,"date":"2020-08-23T07:42:16","date_gmt":"2020-08-23T05:42:16","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql"},"modified":"2020-08-23T07:42:16","modified_gmt":"2020-08-23T05:42:16","slug":"etyud-po-realizaczii-row-level-secutity-v-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/pl\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","title":{"rendered":"Studium przypadku implementacji Row Level Security w PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Jako dodatek do Studium dotycz\u0105ce wdra\u017cania logiki biznesowej na poziomie funkcji przechowywanych PostgreSQL.<\/a><\/noindex> i g\u0142\u00f3wnie dla rozwini\u0119tej odpowiedzi<\/b> na komentarz<\/a><\/noindex>.<\/p>\n

Cz\u0119\u015b\u0107 teoretyczna jest doskonale opisana w dokumentacji Postgres Pro<\/a><\/noindex> \u2014 Polityka ochrony wierszy<\/a><\/noindex>. Poni\u017cej przedstawiono praktyczn\u0105 realizacj\u0119 ma\u0142ego konkretnego zadania biznesowego \u2014 ukrywania danych usuni\u0119tych.<\/b> Studium po\u015bwi\u0119cone realizacji Modelu r\u00f3l z wykorzystaniem RLS<\/a><\/noindex><\/b> przedstawione jest osobno.<\/p>\n

\"Studium<\/p>\n

Artyku\u0142 nie zawiera nic nowego, nie ma ukrytego sensu ani tajemnej wiedzy. To po prostu szkic dotycz\u0105cy praktycznej realizacji teoretycznej idei. Je\u015bli kogo\u015b to interesuje \u2014 niech czyta. Kto nie jest zainteresowany \u2014 niech nie marnuje swojego czasu.<\/p><\/blockquote>\n

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

Sformu\u0142owanie zadania<\/h3>\n

\nNie wdra\u017caj\u0105c si\u0119 g\u0142\u0119boko w temat, w skr\u00f3cie, zadanie mo\u017cna sformu\u0142owa\u0107 nast\u0119puj\u0105co: istnieje tabela realizuj\u0105ca pewn\u0105 biznesow\u0105 encj\u0119. Wiersze w tabeli mog\u0105 by\u0107 usuwane, ale fizycznie nie mo\u017cna ich usuwa\u0107, nale\u017cy je ukrywa\u0107. <\/p>\n

I\u017c powiedziano \u2014 \u201eNic nie usuwaj, tylko zmieniaj nazwy. Internet wszystko pami\u0119ta\u201d <\/b><\/p>\n

<\/i> Dodatkowo, po\u017c\u0105dane jest, aby nie przepisywa\u0107 ju\u017c istniej\u0105cych funkcji sk\u0142adowych dzia\u0142aj\u0105cych z dan\u0105 encj\u0105.<\/p>\n

Aby zrealizowa\u0107 t\u0119 koncepcj\u0119, tabela ma atrybut is_deleted<\/i>. Dalej wszystko jest proste \u2014 nale\u017cy zrobi\u0107 tak, aby klient widzia\u0142 tylko wiersze, w kt\u00f3rych atrybut is_deleted<\/i> jest fa\u0142szywy. Do tego celu wykorzystywany jest mechanizm Row Level Security.<\/b><\/p>\n

Realizacja<\/h3>\n

\nTworzymy osobn\u0105 rol\u0119 i schemat<\/p>\n

CREATE ROLE repos;\nCREATE SCHEMA repos;<\/code><\/pre>\n
\nTworzymy docelow\u0105 tabel\u0119<\/p>\n
CREATE TABLE repos.file\n(\n...\nis_del BOOLEAN DEFAULT FALSE\n);\nCREATE SCHEMA repos<\/code><\/pre>\n
\nW\u0142\u0105czamy Row Level Security<\/i><\/p>\n
ALTER TABLE repos.file ENABLE ROW LEVEL SECURITY;\nCREATE POLICY file_invisible_deleted ON repos.file FOR ALL TO dba_role USING (NOT is_deleted);\nGRANT ALL ON TABLE repos.file TO dba_role;\nGRANT USAGE ON SCHEMA repos TO dba_role;<\/code><\/pre>\n
\nFunkcja serwisowa<\/b> \u2014 usuwanie wiersza z tabeli<\/p>\n
CREATE OR REPLACE repos.delete(curr_id repos.file.id%TYPE)\nRETURNS integer AS $$\nBEGIN\n...\nUPDATE repos.file\nSET is_del = TRUE \nWHERE id = curr_id;\n...\nEND\n$$ LANGUAGE plpgsql SECURITY DEFINER;<\/code><\/pre>\n
\nFunkcja biznesowa<\/b> \u2014 usuwanie dokumentu<\/p>\n
CREATE OR REPLACE business_functions.deleteDoc(doc_for_delete JSON)\nRETURNS JSON AS $$\nBEGIN\n...\nPERFORM repos.delete(doc_id);\n...\nEND\n$$ LANGUAGE plpgsql SECURITY DEFINER;<\/code><\/pre>\n
<\/p>\n
Wyniki<\/h3>\n
\nKlient usuwa dokument<\/p>\n
SELECT business_functions.delCFile((SELECT json_build_object('CId', 3)));<\/code><\/pre>\n
\nPo usuni\u0119ciu, klient dokumentu nie widzi<\/p>\n
SELECT business_functions.getCFile( (SELECT json_build_object( 'CId', 3 )) ) ;\n-----------------\n(0 wierszy)<\/code><\/pre>\n
\nAle w BDR dokument nie zosta\u0142 usuni\u0119ty, tylko zmieniono atrybut is_del<\/i><\/p>\n
psql -d my_db\nSELECT id, name, is_del FROM repos.file;\nid | name | is_del\n--+---------+------------\n 1 | test_1 | t\n(1 wiersz)<\/code><\/pre>\n
\nCo by\u0142o wymagane w postawionym zadaniu. <\/p>\n
Podsumowanie<\/h2>\n
\nJe\u015bli temat b\u0119dzie interesuj\u0105cy, w nast\u0119pnym studium mo\u017cna przedstawi\u0107 przyk\u0142ad realizacji modelu r\u00f3l rozdzielenia dost\u0119pu do danych z wykorzystaniem Row Level Security.
\n
\u0179r\u00f3d\u0142o: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0412 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435 \u0434\u043e\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043a \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 \u0438 \u0432 \u043e\u0441\u043d\u043e\u0432\u043d\u043e\u043c \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. \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 \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044f \u043c\u0430\u043b\u0435\u043d\u044c\u043a\u043e\u0439 \u043a\u043e\u043d\u043a\u0440\u0435\u0442\u043d\u043e\u0439 \u0431\u0438\u0437\u043d\u0435\u0441 \u0437\u0430\u0434\u0430\u0447\u0438 \u2014 \u0441\u043a\u0440\u044b\u0442\u0438\u044f \u0443\u0434\u0430\u043b\u0435\u043d\u043d\u044b\u0445 \u0434\u0430\u043d\u043d\u044b\u0445 . \u042d\u0442\u044e\u0434 \u043f\u043e\u0441\u0432\u044f\u0449\u0435\u043d\u043d\u044b\u0439 \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0438 \u0420\u043e\u043b\u0435\u0432\u043e\u0439 \u043c\u043e\u0434\u0435\u043b\u0438 \u0441 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435\u043c RLS \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":92108,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-92107","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\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\udd47 Studium na temat realizacji Row Level Security w PostgreSQL | ProHoster","description":"Jako uzupe\u0142nienie do Studium na temat realizacji logiki biznesowej na poziomie funkcji sk\u0142adowanych PostgreSQL oraz g\u0142\u00f3wnie dla wdro\u017cenia.","canonical_url":"https:\/\/prohoster.info\/pl\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"pl_PL","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\u042d\u0442\u044e\u0434 \u043f\u043e \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0438 Row Level Secutity \u0432 PostgreSQL | ProHoster","og:description":"\u0412 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435 \u0434\u043e\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043a \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 \u0438 \u0432 \u043e\u0441\u043d\u043e\u0432\u043d\u043e\u043c \u0434\u043b\u044f \u0440\u0430\u0437\u0432\u0435\u0440\u043d\u0443\u0442\u043e\u0433\u043e.","og:url":"https:\/\/prohoster.info\/pl\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-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-23T05:42:16+00:00","article:modified_time":"2020-08-23T05:42:16+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"92107","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:24","updated":"2022-09-28 09:33:44","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/posts\/92107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/comments?post=92107"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/posts\/92107\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/media\/92108"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/media?parent=92107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/categories?post=92107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/pl\/wp-json\/wp\/v2\/tags?post=92107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}