{"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\/sv\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","title":{"rendered":"Studie om genomf\u00f6rande av Row Level Security i PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Som ett komplement till Studie om genomf\u00f6rande av aff\u00e4rslogik p\u00e5 niv\u00e5n av lagrade funktioner i PostgreSQL<\/a><\/noindex> och fr\u00e4mst f\u00f6r en utf\u00f6rlig respons<\/b> till kommentar<\/a><\/noindex>.<\/p>\n

Den teoretiska delen \u00e4r utm\u00e4rkt beskriven i dokumentationen Postgres Pro<\/a><\/noindex> \u2014 Radernas skyddspolicyer<\/a><\/noindex>. Nedan diskuteras en praktisk implementering av en liten specifik aff\u00e4rsuppgift \u2014 att d\u00f6lja borttagna data.<\/b> Studien \u00e4gnas \u00e5t implementeringen av en rollmodell med anv\u00e4ndning av RLS<\/a><\/noindex><\/b> som presenteras separat.<\/p>\n

\"Studie<\/p>\n

Artikeln inneh\u00e5ller ingenting nytt, inga dolda betydelser eller hemliga insikter. Det \u00e4r bara en skiss av praktisk till\u00e4mpning av en teoretisk id\u00e9. Om n\u00e5gon \u00e4r intresserad \u2014 l\u00e4s. Om inte \u2014 sl\u00f6sa inte din tid.<\/p><\/blockquote>\n

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

Uppgiftsst\u00e4llande<\/h3>\n

\nUtan att f\u00f6rdjupa oss alltf\u00f6r mycket i \u00e4mnesomr\u00e5det kan vi kort formulera uppgiften p\u00e5 f\u00f6ljande s\u00e4tt: det finns en tabell som representerar en aff\u00e4rsenhet. Rader i tabellen kan tas bort, men rader f\u00e5r inte tas bort fysiskt, de m\u00e5ste d\u00f6ljas. <\/p>\n

Ty det \u00e4r sagt \u2014 \"Ta aldrig bort n\u00e5gonting, bara d\u00f6p om det. Internet kommer att spara ALLT\" <\/b><\/p>\n

<\/i> Det \u00e4r dessutom \u00f6nskv\u00e4rt att inte skriva om redan existerande lagrade funktioner som arbetar med denna enhet.<\/p>\n

F\u00f6r att genomf\u00f6ra detta koncept har tabellen en attribut is_deleted<\/i>. Vidare \u00e4r allt enkelt \u2014 vi m\u00e5ste se till att klienten endast kan se rader d\u00e4r attributet is_deleted<\/i> \u00e4r falskt. F\u00f6r detta anv\u00e4nds mekanismen Row Level Security.<\/b><\/p>\n

Implementering<\/h3>\n

\nVi skapar en s\u00e4rskild roll och schema<\/p>\n

CREATE ROLE repos;\nCREATE SCHEMA repos;<\/code><\/pre>\n
\nVi skapar den m\u00e5ltavlan<\/p>\n
CREATE TABLE repos.file\n(\n...\nis_del BOOLEAN DEFAULT FALSE\n);\nCREATE SCHEMA repos<\/code><\/pre>\n
\nVi aktiverar 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
\nTj\u00e4nstefunktion<\/b> \u2014 ta bort en rad i tabellen<\/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
\nAff\u00e4rsfunktion<\/b> \u2014 ta bort dokumentet<\/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
Resultat<\/h3>\n
\nKlienten tar bort dokumentet<\/p>\n
SELECT business_functions.delCFile((SELECT json_build_object('CId', 3)));<\/code><\/pre>\n
\nEfter borttagningen ser klienten av dokumentet inte<\/p>\n
V\u00c4LJ business_functions.getCFile( (V\u00c4LJ json_build_object( 'CId', 3 )) ) ;\n-----------------\n(0 rader)<\/code><\/pre>\n
\nMen i databasen har dokumentet inte tagits bort, endast attributet har \u00e4ndrats 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 rad)<\/code><\/pre>\n
\nVilket var vad som kr\u00e4vdes i uppgiften. <\/p>\n
Resultat<\/h2>\n
\nOm \u00e4mnet \u00e4r intressant kan vi i n\u00e4sta studie visa ett exempel p\u00e5 implementering av en rollmodell f\u00f6r delad datatillg\u00e5ng med anv\u00e4ndning av Row Level Security.
\n
K\u00e4lla: 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\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\udd47Studie om implementering av Row Level Security i PostgreSQL | ProHoster","description":"Som ett komplement till studie om implementering av aff\u00e4rslogik p\u00e5 niv\u00e5n av lagrade funktioner i PostgreSQL och fr\u00e4mst f\u00f6r en utf\u00f6rlig.","canonical_url":"https:\/\/prohoster.info\/sv\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sv_SE","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\/sv\/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\/sv\/wp-json\/wp\/v2\/posts\/92107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/comments?post=92107"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts\/92107\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media\/92108"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media?parent=92107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/categories?post=92107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/tags?post=92107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}