Ọmụmụ maka imejuputa nchekwa ọkwa ọkwa na PostgreSQL

Dị ka ihe nkwado na Ọmụmụ banyere imejuputa mgbagha azụmahịa na ọkwa nke ọrụ echekwara PostgreSQL и tumadi maka azịza zuru oke on ikwu.

A na-akọwa akụkụ nke usoro ọmụmụ nke ọma na akwụkwọ ahụ PostgreSQL - Amụma nchedo ahịrị. N'okpuru ebe a bụ mmezu bara uru nke obere ọrụ azụmahịa kpọmkwem - izobe data ehichapụ. Sketch raara nye na mmejuputa iwu Nlegharị anya n'iji RLS ewepụtara iche.

Ọ dịghị ihe ọhụrụ na isiokwu, ọ dịghị ihe zoro ezo pụtara ma ọ bụ ihe ọmụma nzuzo. Naanị ihe osise gbasara mmejuputa atumatu nke echiche echiche. Ọ bụrụ na onye ọ bụla nwere mmasị, gụọ ya. Ọ bụrụ na ị nweghị mmasị, egbula oge gị.

Ulationkpụzi nsogbu a

Na-abanyeghị n'ime miri emi na mpaghara isiokwu, nkenke, enwere ike ịmepụta nsogbu dị ka ndị a: Enwere tebụl na-emejuputa otu ụlọ ọrụ azụmahịa. Enwere ike ihichapụ ahịrị ndị dị na tebụl, mana enweghị ike ihichapụ ahịrị ahụ n'anụ ahụ, a ga-ezorịrị ha.

N'ihi na e kwuru: "Ehichapụla ihe ọ bụla, dị nnọọ nyegharịa ya aha. Ịntanetị na-echekwa ihe niile"

N'ụzọ, ọ bụ ihe amamihe dị na ya ịghara idegharị ọrụ echekwara dị ugbu a na-arụ ọrụ na ụlọ ọrụ a.

Iji mejuputa echiche a, okpokoro nwere àgwà na-ehichapụ. Mgbe ahụ ihe niile dị mfe - ịkwesịrị ijide n'aka na onye ahịa ahụ nwere ike ịhụ naanị ahịrị nke àgwà ahụ na-ehichapụ ụgha Kedu ihe eji eme ya? Nchebe Ọkwa Ahịrị.

Mmejuputa iwu

Mepụta ọrụ na atụmatụ dị iche

CREATE ROLE repos;
CREATE SCHEMA repos;

Mepụta tebụl ebumnuche

CREATE TABLE repos.file
(
...
is_del BOOLEAN DEFAULT FALSE
);
CREATE SCHEMA repos

Anyị gụnyere Ahịrị Ọkwa Security

ALTER TABLE repos.file  ENABLE ROW LEVEL SECURITY ;
CREATE POLICY file_invisible_deleted  ON repos.file FOR ALL TO dba_role USING ( NOT is_deleted );
GRANT ALL ON TABLE repos.file to dba_role ;
GRANT USAGE ON SCHEMA repos TO dba_role ;

Ọrụ ọrụ - ihichapụ ahịrị na tebụl

CREATE OR REPLACE repos.delete( curr_id repos.file.id%TYPE)
RETURNS integer AS $$
BEGIN
...
UPDATE repos.file
SET is_del = TRUE 
WHERE id = curr_id ; 
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Ọrụ azụmahịa - ihichapụ akwụkwọ

CREATE OR REPLACE business_functions.deleteDoc( doc_for_delete JSON )
RETURNS JSON AS $$
BEGIN
...
PERFORM  repos.delete( doc_id ) ;
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Результаты

Onye ahịa na-ehichapụ akwụkwọ ahụ

SELECT business_functions.delCFile( (SELECT json_build_object( 'CId', 3 )) );

Mgbe ihichapụ, onye ahịa ahụghị akwụkwọ ahụ

SELECT business_functions.getCFile"( (SELECT json_build_object( 'CId', 3 )) ) ;
-----------------
(0 rows)

Ma na nchekwa data adịghị ehichapụ akwụkwọ ahụ, naanị àgwà gbanwere bụ_del

psql -d my_db
SELECT  id, name , is_del FROM repos.file ;
id |  name  | is_del
--+---------+------------
 1 |  test_1 | t
(1 row)

Nke bụ ihe achọrọ na nkwupụta nsogbu.

Nsonaazụ

Ọ bụrụ na isiokwu ahụ na-adọrọ mmasị, n'ọmụmụ ihe na-esote ị nwere ike igosi ihe atụ nke imejuputa ihe nlereanya dabere na ọrụ maka ikewa ịnweta data site na iji Ọkwa Ọkwa Nchekwa.

isi: www.habr.com