{"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\/et\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","title":{"rendered":"Et\u00fc\u00fcd Row Level Security rakendamisest PostgreSQL-is","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

T\u00e4ienduseks Et\u00fc\u00fcd \u00e4rilogika rakendamisest PostgreSQL-i salvestatud funktsioonide tasemel<\/a><\/noindex> ja peamiselt p\u00f5hjalikku vastust jaoks<\/b> j\u00e4rgnevaga kommentaar<\/a><\/noindex>.<\/p>\n

Teoreetiline osa on h\u00e4sti kirjeldatud dokumentatsioonis Postgres Pro<\/a><\/noindex> \u2014 Ridade kaitse poliitikad<\/a><\/noindex>. Allpool on k\u00e4sitletud praktiline rakendus v\u00e4ikesest konkreetse \u00e4riprobleemi \u2014 kaugete andmete varjamise.<\/b> Essee on p\u00fchendatud rakendusele rollimudeli kasutamisega RLS<\/a><\/noindex><\/b> esitatud eraldi.<\/p>\n

\"Et\u00fc\u00fcd<\/p>\n

Artiklis ei ole midagi uut, ei ole varjatud m\u00f5tet ega salajasi teadmisi. Lihtsalt joonistamine teoreetilise idee praktilisest elluviimisest. Kui kedagi huvitab \u2014 lugege. Kui ei huvita \u2014 \u00e4rge kulutage oma aega raisku.<\/p><\/blockquote>\n

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

\u00dclesande seadmine<\/h3>\n

\nS\u00fcgavale teemasse minemata v\u00f5ib l\u00fchidalt \u00f6elda, et \u00fclesanne on j\u00e4rgmine: olemas on tabel, mis rakendab mingit \u00e4rilisi olemusi. Tabeli read v\u00f5ivad olla kustutatud, kuid f\u00fc\u00fcsiliselt ridu kustutada ei tohi; neid tuleb varjata. <\/p>\n

Sest \u00f6eldud on \u2014 \u201eMitte midagi ei kustuta, ainult nimetused muuda. Internet salvestab K\u00d5IKE\u201c <\/b><\/p>\n

<\/i> Samas on soovitav mitte kirjutada uuesti juba olemasolevaid salvestatud funktsioone, mis t\u00f6\u00f6tavad antud subjektiga.<\/p>\n

Selle kontseptsiooni rakendamiseks on tabelil atribuudi is_deleted<\/i>. Edasi on k\u00f5ik lihtne \u2014 tuleb teha nii, et klient n\u00e4eks ainult ridu, kus atribuudi is_deleted<\/i> v\u00e4\u00e4rtus on vale. Selleks kasutatakse rea tasandi turvamehhanismi.<\/b><\/p>\n

Rakendus<\/h3>\n

\nLoo eraldi roll ja skeem<\/p>\n

CREATE ROLE repos;\nCREATE SCHEMA repos;<\/code><\/pre>\n
\nLoo sihtm\u00e4rk tabel<\/p>\n
CREATE TABLE repos.file\n(\n...\nis_del BOOLEAN DEFAULT FALSE\n);\nCREATE SCHEMA repos<\/code><\/pre>\n
\nLuba rea tasandi turvalisus<\/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
\nTeenusfunktsioon<\/b> \u2014 rea kustutamine tabelis<\/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
\n\u00c4rifunktsioon<\/b> \u2014 dokumendi kustutamine<\/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
Tulemused<\/h3>\n
\nKliendikustutab dokumendi<\/p>\n
SELECT business_functions.delCFile( (SELECT json_build_object( 'CId', 3 )) );<\/code><\/pre>\n
\nP\u00e4rast kustutamist ei n\u00e4e dokumendi klient<\/p>\n
SELECT business_functions.getCFile( (SELECT json_build_object( 'CId', 3 )) ) ;\n-----------------\n(0 rows)<\/code><\/pre>\n
\nAga andmebaasis dokument ei ole kustutatud, ainult atribuut on muudetud 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 row)<\/code><\/pre>\n
\nJust nagu tehtud \u00fclesandes n\u00f5uti. <\/p>\n
Kokkuv\u00f5te<\/h2>\n
\nKui teema huvitab, siis j\u00e4rgmises eet\u00fc\u00fcd v\u00f5ib n\u00e4idata n\u00e4idet rollimudeli rakendamisest, kasutades Row Level Security.
\n
Allikas: 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\udd47Rida tasandi turvameetmete rakendamise \u00f5pik PostgreSQL-is | ProHoster","description":"Lisaks on esitatud \u00f5pik PostgreSQL-i salvestatud funktsioonide taseme \u00e4riloogika elluviimisest ja peamiselt vastuseks kommentaarile. Teoreetiline osa on suurep\u00e4raselt v\u00e4lja toodud Postgres Pro dokumentatsioonis \u2014 Rea kaitse poliitikad. Allpool k\u00e4sitletakse v\u00e4ikese konkreetse \u00e4ri\u00fclesande praktilist elluviimist \u2014 kustutatud andmete peitmine. Roolimudeli rakendusele, kasutades RLS-i, p\u00fchendatud \u00f5pik on esitatud.","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","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 \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","og:url":"https:\/\/prohoster.info\/et\/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"},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/92107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=92107"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/92107\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/92108"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=92107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=92107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=92107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}