{"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\/fr\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","title":{"rendered":"\u00c9tude sur la mise en \u0153uvre de la s\u00e9curit\u00e9 au niveau des lignes dans PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

En compl\u00e9ment de \u00c9tude sur la mise en \u0153uvre de la logique m\u00e9tier au niveau des fonctions stock\u00e9es PostgreSQL<\/a><\/noindex> et principalement pour une r\u00e9ponse d\u00e9taill\u00e9e<\/b> sur commentaire<\/a><\/noindex>.<\/p>\n

La partie th\u00e9orique est bien d\u00e9crite dans la documentation Postgres Pro<\/a><\/noindex> \u2014 Politiques de protection des lignes<\/a><\/noindex>. Ci-dessous, nous examinons la mise en \u0153uvre pratique d'un probl\u00e8me commercial sp\u00e9cifique \u2014 la dissimulation des donn\u00e9es supprim\u00e9es.<\/b> L'\u00e9tude est consacr\u00e9e \u00e0 la mise en \u0153uvre d'un mod\u00e8le de r\u00f4le utilisant le RLS<\/a><\/noindex><\/b> pr\u00e9sent\u00e9 s\u00e9par\u00e9ment.<\/p>\n

\"\u00c9tude<\/p>\n

L'article n'apporte rien de nouveau, il n'y a pas de sens cach\u00e9 ni de connaissances secr\u00e8tes. Juste un aper\u00e7u de la mise en \u0153uvre pratique d'une id\u00e9e th\u00e9orique. Si cela vous int\u00e9resse, lisez. Si cela ne vous int\u00e9resse pas, ne perdez pas votre temps.<\/p><\/blockquote>\n

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

D\u00e9finition du probl\u00e8me<\/h3>\n

\nSans plonger profond\u00e9ment dans le domaine, nous pouvons formuler la t\u00e2che comme suit : il existe une table mettant en \u0153uvre une certaine entit\u00e9 commerciale. Les lignes de la table peuvent \u00eatre supprim\u00e9es, mais il est interdit de supprimer physiquement les lignes, il faut les cacher. <\/p>\n

Car il est dit \u2014 \u00abNe supprime rien, renomme simplement. Internet conserve TOUT\u00bb <\/b><\/p>\n

<\/i> Id\u00e9alement, il serait souhaitable de ne pas r\u00e9\u00e9crire les fonctions stock\u00e9es existantes qui fonctionnent avec cette entit\u00e9.<\/p>\n

Pour mettre en \u0153uvre ce concept, la table poss\u00e8de un attribut is_deleted<\/i>. Ensuite, tout est simple \u2014 il faut faire en sorte que le client ne puisse voir que les lignes o\u00f9 l'attribut is_deleted<\/i> est faux. C'est pourquoi nous utilisons le m\u00e9canisme Row Level Security.<\/b><\/p>\n

Mise en \u0153uvre<\/h3>\n

\nCr\u00e9ons un r\u00f4le et un sch\u00e9ma distincts<\/p>\n

CREATE ROLE repos;\nCREATE SCHEMA repos;<\/code><\/pre>\n
\nCr\u00e9ons la table cible<\/p>\n
CREATE TABLE repos.file\n(\n...\nis_del BOOLEAN DEFAULT FALSE\n);\nCREATE SCHEMA repos<\/code><\/pre>\n
\nNous activons 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
\nFonction de service<\/b> \u2014 suppression d'une ligne dans la table<\/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
\nFonction commerciale<\/b> \u2014 suppression d'un document<\/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
R\u00e9sultats<\/h3>\n
\nLe client supprime le document<\/p>\n
SELECT business_functions.delCFile((SELECT json_build_object('CId', 3)));<\/code><\/pre>\n
\nApr\u00e8s la suppression, le client du document ne voit pas<\/p>\n
SELECT business_functions.getCFile\"( (SELECT json_build_object( 'CId', 3 )) ) ;\n-----------------\n(0 lignes)<\/code><\/pre>\n
\nMais dans la base de donn\u00e9es, le document n'est pas supprim\u00e9, seul l'attribut 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 ligne)<\/code><\/pre>\n
\nCe qui \u00e9tait requis dans la t\u00e2che. <\/p>\n
Conclusion<\/h2>\n
\nSi le sujet vous int\u00e9resse, l'\u00e9tude suivante peut pr\u00e9senter un exemple de mise en \u0153uvre d'un mod\u00e8le de r\u00f4le pour la s\u00e9paration des acc\u00e8s aux donn\u00e9es en utilisant la s\u00e9curit\u00e9 au niveau des lignes.
\n
Source : 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\u00c9tude sur la mise en \u0153uvre de la s\u00e9curit\u00e9 au niveau des lignes dans PostgreSQL | ProHoster","description":"En compl\u00e9ment de l'\u00e9tude sur la mise en \u0153uvre de la logique m\u00e9tier au niveau des fonctions stock\u00e9es PostgreSQL et principalement pour un d\u00e9ploiement d\u00e9velopp\u00e9.","canonical_url":"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"fr_FR","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\/fr\/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\/fr\/wp-json\/wp\/v2\/posts\/92107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/comments?post=92107"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/posts\/92107\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/media\/92108"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/media?parent=92107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/categories?post=92107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/tags?post=92107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}