{"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\/it\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","title":{"rendered":"Studio sull'implementazione della Row Level Security in PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Come integrazione a Studio sulla realizzazione della logica di business a livello di funzioni memorizzate in PostgreSQL<\/a><\/noindex> e principalmente per una risposta dettagliata<\/b> con commento<\/a><\/noindex>.<\/p>\n

La parte teorica \u00e8 ben descritta nella documentazione Postgres Pro<\/a><\/noindex> \u2014 Politiche di protezione delle righe<\/a><\/noindex>. Di seguito \u00e8 riportata l'implementazione pratica di un piccolo caso d'uso specifico \u2014 nascondere i dati rimossi.<\/b> Studio dedicato all'implementazione di un modello di ruolo utilizzando RLS<\/a><\/noindex><\/b> \u00e8 presentato separatamente.<\/p>\n

\"Studio<\/p>\n

Nell'articolo non c'\u00e8 nulla di nuovo, nessun significato nascosto e conoscenze segrete. Solo un abbozzo su un'implementazione pratica di un'idea teorica. Se a qualcuno interessa \u2014 leggete. A chi non interessa \u2014 non sprecate il vostro tempo.<\/p><\/blockquote>\n

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

Definizione del compito<\/h3>\n

\nSenza addentrarsi profondamente nell'argomento, in breve, la questione pu\u00f2 essere formulata nel seguente modo: \u00e8 presente una tabella che implementa una certa entit\u00e0 aziendale. Le righe nella tabella possono essere eliminate, ma non \u00e8 possibile rimuovere fisicamente le righe, devono essere nascoste. <\/p>\n

Poich\u00e9 \u00e8 stato detto \u2014 \u00abNon eliminare niente, solo rinomina. Internet conserva TUTTO\u00bb <\/b><\/p>\n

<\/i> Nel contempo, \u00e8 consigliabile non riscrivere gi\u00e0 esistenti funzioni memorizzate che lavorano con questa entit\u00e0.<\/p>\n

Per implementare questo concetto, la tabella ha un attributo is_deleted<\/i>. Poi tutto \u00e8 semplice: \u00e8 necessario fare in modo che il cliente possa vedere solo le righe in cui l'attributo is_deleted<\/i> \u00e8 impostato. A questo scopo viene utilizzato il meccanismo Row Level Security.<\/b><\/p>\n

Implementazione<\/h3>\n

\nCreiamo un ruolo e uno schema separati<\/p>\n

CREATE ROLE repos;\nCREATE SCHEMA repos;<\/code><\/pre>\n
\nCreiamo la tabella di destinazione<\/p>\n
CREATE TABLE repos.file\n(\n...\nis_del BOOLEAN DEFAULT FALSE\n);\nCREATE SCHEMA repos<\/code><\/pre>\n
\nAttiviamo 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
\nFunzione di servizio<\/b> \u2014 eliminazione della riga nella tabella<\/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
\nFunzione aziendale<\/b> \u2014 eliminazione del documento<\/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
Risultati<\/h3>\n
\nIl cliente elimina il documento<\/p>\n
SELECT business_functions.delCFile( (SELECT json_build_object( 'CId', 3 )) );<\/code><\/pre>\n
\nDopo l'eliminazione, il cliente del documento non vede<\/p>\n
SELECT business_functions.getCFile( (SELECT json_build_object( 'CId', 3 )) );\n-----------------\n(0 righe)<\/code><\/pre>\n
\nMa nel DB il documento non \u00e8 stato eliminato, solo l'attributo \u00e8 stato modificato 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 riga)<\/code><\/pre>\n
\nChe era esattamente ci\u00f2 che era richiesto nella formulazione del compito. <\/p>\n
Risultato<\/h2>\n
\nSe l'argomento risulta interessante, nel prossimo studio possiamo mostrare un esempio di implementazione del modello di ruoli per la separazione dell'accesso ai dati utilizzando la Sicurezza a Livello di Riga.
\n
Fonte: 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\udd47Studio sull'implementazione della Sicurezza a Livello di Riga in PostgreSQL | ProHoster","description":"Come aggiunta allo studio sull'implementazione della logica aziendale a livello di funzioni memorizzate in PostgreSQL e principalmente per fornire una risposta dettagliata a un commento. La parte teorica \u00e8 ben descritta nella documentazione di Postgres Pro \u2014 Politiche di protezione delle righe. Di seguito viene esaminata l'implementazione pratica di un piccolo compito aziendale specifico \u2014 la mascheratura dei dati eliminati. Lo studio dedicato all'implementazione del Modello di Ruoli utilizzando RLS \u00e8 presentato.","canonical_url":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/etyud-po-realizaczii-row-level-secutity-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"it_IT","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\/it\/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\/it\/wp-json\/wp\/v2\/posts\/92107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/comments?post=92107"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/92107\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media\/92108"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media?parent=92107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/categories?post=92107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/tags?post=92107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}