á¡ááŒááºá¡ááẠááœá¶á·ááŒáá¯ážááá¯ážáááºááŸá¯
á¡áá¯á¶ážááŒá¯ááá·áºáááºážáá»á°áá¬ááœáẠá€áá±áá¬ááœááºá¡áá±ážá
áááºáá±á¬áºááŒáá¬ážááá·áº âáá±áá¬áá±á·á
áºááŸáá
á®ážááœá¬ážáá±ážáá±á¬á·áá»á
áºâ á¡áá°á¡áááᯠá¡áá¯á¶ážááŒá¯ááŒááºáž áá«áááºáááºá
á
á¬áááºážááœáẠáá®á¡áá¯áá®á¡ááá¯ááºážááᯠáá±á¬ááºážá
áœá¬áá±á¬áºááŒáá¬ážáááºá
áá±á¬ááºážáá«ážááœáẠá¡áá áºá¡áááºážááá¯ááºáá«á áá»áŸáá¯á·ááŸááºá¡áááá¹áá¬áẠááá¯á·ááá¯áẠáá»áŸáá¯á·ááŸááºá¡ááááá¬áááŸááá«á áá®á¡áá¯áá®áá áºáá¯á á áááºáá°ážááᯠáááºááœá±á·á¡áá±á¬ááºá¡áááºáá±á¬áºááŒááºážá¡ááŒá±á¬ááºáž áá¯á¶ááŒááºážáá áºáá¯áá»áŸáá¬ááŒá áºáááºá á áááºáááºá á¬ážáá²á·áá°ááŸáááẠáááºááŒáá·áºááá¯ááºáá«á á áááºááááºá á¬ážááẠá¡áá»áááºáááŒá¯ááºážáá«áá²á·á
ááŒá¿áá¬ááá¯áá¯á¶áá±á¬áºááŒááºáž
á¡ááºááá®áá±ážááŸááºážá¡áá¯á¶ážááŒá¯áá°áá¡áááºážááá¹áááŸáá·áºá¡áá® á á¬ááœááºá á¬áááºážáá áºáá¯ááŒáá·áºááŸá¯ááŒááºáž/ááá·áºááœááºážááŒááºáž/ááŒááºáááºááŒááºáž/áá»ááºááŒááºážááá¯á·á¡á¬áž ááá·áºáááºááẠááá¯á¡ááºáá«áááºá Role ááẠááá¬ážáá áºáá¯á¡ááœááºáž áááºáá±á¬ááºááŸá¯ááᯠáááºááœáŸááºážáááºá á¡áááºážááá¹á ááá¬ážáá áºáá¯ááŸáá·áº áá áºáá¯ááŸáá·áºáá áºáᯠáá»á¬ážá áœá¬áááºá ááºááŸá¯áá áºáá¯ááŸáá·áº áááºá ááºáá±áááºá á¡áá¯á¶ážááŒá¯áá°áá»á¬ážáááº. á¡áá±ážá¡ááœá²ááŒá±á¬áá·áº ááá¬ážáá»á¬ážááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºááŒááºážáá¡áá±ážá áááºá¡áá»ááºá¡áááºáá»á¬ážááᯠáá»ááºááŸááºáá¬ážáááºá áá¬áá¬áááºáááºáááºááŸáá·áºáááºááá¯ááºááá·áº áá®ážááŒá¬ážá¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡áá±ážá áááºáá»á¬ážááá¯áááºáž áá»ááºááŸááºáá¬ážáááºá
á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯
á¡áááºážááá¹ááá»á¬ážá á¡á á®á¡á ááºáá»á¬ážá ááá¬ážáá»á¬ážááᯠáááºáá®ážáá«á
Database Objects áááºáá®ážááŒááºážá
CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
id integer , --id ЎПкÑЌеМÑа
man_id integer , --id ЌеМеЎжеÑа ЎПкÑЌеМÑа
stat_id integer , --id ÑÑаÑÑÑа ЎПкÑЌеМÑа
...
is_del BOOLEAN DEFAULT FALSE
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;
RLS ááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºááẠáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááᯠáááºáá®ážááŒááºážá
SELECT á¡áááºážáá¯ááºáá±á¬ááºááá¯ááºááŸá¯ááᯠá á áºáá±ážááŒááºážá
check_select
CREATE OR REPLACE FUNCTION store.check_select ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
result boolean ;
curr_pid integer ;
curr_stat_id integer ;
doc_man_id integer ;
BEGIN
-- DBA ÐžÐŒÐµÐµÑ ÐŽÐŸÑÑÑп кП вÑеЌ ЎПкÑЌеМÑаЌ
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
--ÐÑлО ЎПкÑÐŒÐµÐœÑ ÐžÐŒÐµÐµÑ ÐŒÐµÑÐºÑ 'ÑЎалеМ' - Ме пПказÑваÑÑ Ð² вÑбПÑке
SELECT
is_del
INTO
result
FROM
store.docs
WHERE
id = current_id ;
IF result = TRUE
THEN
RETURN FALSE ;
END IF ;
--------------------------------
--ÐПлÑÑОÑÑ id ÑекÑÑегП пПлÑзПваÑелÑ
SELECT
service_function.get_curr_pid ()
INTO
curr_pid ;
--------------------------------
--ÐПлÑÑОÑÑ id ЌеМеЎжеÑа ЎПкÑЌеМÑа
SELECT
man_id
INTO
doc_man_id
FROM
store.docs
WHERE
id = current_id ;
--------------------------------
--ÐÑлО ÐŒÐµÐœÐµÐŽÐ¶ÐµÑ ÐŽÐŸÐºÑЌеМÑа Ме ÑекÑÑОй пПлÑзПваÑÐµÐ»Ñ ÐžÐ»Ðž ÐŒÐµÐœÐµÐŽÐ¶ÐµÑ ÐœÐµ МазМаÑеМ
--ЎПбавОÑÑ ÐŽÐŸÐºÑÐŒÐµÐœÑ Ð² вÑбПÑкÑ
IF doc_man_id != curr_pid OR doc_man_id IS NULL
THEN
RETURN TRUE ;
ELSE
--ÐПлÑÑОÑÑ ÑекÑÑОй ÑÑаÑÑÑ ÐŽÐŸÐºÑЌеМÑа
SELECT
stat_id
INTO
curr_statid
FROM
store.docs
WHERE
id = current_id ;
--ÐÑлО ÑÑаÑÑÑ Ð¿ÐŸÐ·Ð²ÐŸÐ»ÑÐµÑ Ð¿ÑПÑЌПÑÑеÑÑ ÐŽÐŸÐºÑÐŒÐµÐœÑ - ЎПбавОÑÑ ÐŽÐŸÐºÑÐŒÐµÐœÑ Ð² вÑбПÑкÑ
IF curr_statid = 4 OR curr_statid = 9
THEN
RETURN TRUE ;
ELSE
--ÐМаÑе - ОÑклÑÑОÑÑ ÐŽÐŸÐºÑÐŒÐµÐœÑ ÐžÐ· вÑбПÑкО
RETURN FALSE ;
END IF ;
END IF ;
--------------------------------
RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_select( store.docs.id%TYPE ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE ) TO service_functions;
á¡áááºážáá»á¬ážááá·áºááœááºážááá¯ááºááŸá¯ááᯠá á áºáá±ážááŒááºážá
check_insert
CREATE OR REPLACE FUNCTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
curr_role_id integer ;
BEGIN
--DBA ÐŒÐŸÐ¶ÐµÑ ÐŽÐŸÐ±Ð°Ð²Ð»ÑÑÑ ÑÑÑÐŸÐºÑ Ð² лÑбПЌ ÑлÑÑае
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
--ÐПлÑÑОÑÑ id ÑПлО ÑекÑÑегП пПлÑзПваÑелÑ
SELECT
service_functions.current_rid()
INTO
curr_role_id ;
--------------------------------
--ÐÑлО ÑÐŸÐ»Ñ ÐŽÐŸÐ¿ÑÑÐºÐ°ÐµÑ Ð²ÐŸÐ·ÐŒÐŸÐ¶ÐœÐŸÑÑÑ ÑÐŸÐ·ÐŽÐ°ÐœÐžÑ ÐœÐŸÐ²ÐŸÐ³ÐŸ ЎПкÑЌеМÑа
--ÑазÑеÑОÑÑ
IF curr_role_id = 3 OR curr_role_id = 5
THEN
RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE ) TO service_functions;
á¡áááºážáá áºáá¯ááᯠáá»ááºááá¯ááºááŸá¯ááᯠá á áºáá±ážáá±áá«áááºá
check_delete
CREATE OR REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNS boolean AS $$
BEGIN
--ТПлÑкП DBA ÐŒÐŸÐ¶ÐµÑ ÑЎалÑÑÑ ÑÑÑПкÑ
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE ;
END
$$ LANGUAGE plpgsql
SECURITY DEFINER;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE ) FROM public;
á¡áááºážáá áºáá¯ááᯠá¡ááºááááºáá¯ááºááá¯ááºá áœááºážááᯠá á áºáá±ážáá±áááºá
update_á¡áá¯á¶ážááŒá¯ááŒááºážá
CREATE OR REPLACE FUNCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
BEGIN
--ÐПкÑЌеМÑÑ ÐžÐŒÐµÑÑОе ÑÑаÑÑÑ 'ÑЎалеМ' - Ме ÑеЎакÑОÑÑÑÑÑÑ
IF is_del
THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.update_using( store.docs.id%TYPE , boolean ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.update_using( store.docs.id%TYPE , boolean ) FROM public;
GRANT EXECUTE ON FUNCTION store.update_using( store.docs.id%TYPE ) TO service_functions;
update_check
CREATE OR REPLACE FUNCTION store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
DECLARE
current_rid integer ;
current_statid integer ;
BEGIN
--DBA ÐŒÐŸÐ¶ÐµÑ Ð¿ÑПÑЌаÑÑОваÑÑ ÑÑÑПкÑ
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
--ÐПлÑÑОÑÑ id ÑПлО ÑекÑÑегП пПлÑзПваÑелÑ
SELECT
service_functions.current_rid()
INTO
curr_role_id ;
--------------------------------
--УЎалеМОе ЎПкÑЌеМÑа - ОзЌеМеМОе пÑОзМака
IF is_deleted
THEN
--ÐÑлО ÑÐŸÐ»Ñ Ð¿ÐŸÐ»ÑзПваÑÐµÐ»Ñ ***
IF current_role_id = 3
THEN
SELECT
stat_id
INTO
curr_statid
FROM
store.docs
WHERE
id = current_id ;
--ÐПкÑÐŒÐµÐœÑ Ð² ÑÑаÑÑÑе *** МелÑÐ·Ñ ÑЎалОÑÑ
IF current_status_id = 11
THEN
RETURN FALSE ;
ELSE
--ÐПжМП ÑЎалОÑÑ ÐŽÐŸÐºÑÐŒÐµÐœÑ Ð² ÐŽÑÑгОÑ
ÑÑаÑÑÑаÑ
RETURN TRUE ;
END IF ;
--ÐМаÑе , еÑлО ÑÐŸÐ»Ñ Ð¿ÐŸÐ»ÑзПваÑÐµÐ»Ñ ***
ELSIF current_role_id = 5
THEN
--ÐÑе ÑÑаÑÑÑÑ ÐŽÐŸÐºÑЌеМÑа
RETURN TRUE ;
ELSE
--ÐÑÑгОе пПлÑзПваÑелО Ме ЌПгÑÑ ÑЎалÑÑÑ ÐŽÐŸÐºÑЌеМÑÑ
RETURN FALSE ;
END IF ;
ELSE
--ÐбМПвлеМОе ЎПкÑЌеМÑа ÑазÑеÑеМП
RETURN TRUE ;
END IF ;
RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.update_with_check( storg.docs.id%TYPE , boolean ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.update_with_check( storg.docs.id%TYPE , boolean ) FROM public;
GRANT EXECUTE ON FUNCTION store.update_with_check( store.docs.id%TYPE ) TO service_functions;
ááá¬ážáá áºáá¯á¡ááœáẠá¡áááºážá¡ááá·áº Secutiry áá°áá«áááᯠááœáá·áºáá«á
á¡áááºážáá¯á¶ááŒá¯á¶áá±ážááᯠááœáá·áºáá«á
ALTER TABLE store.docs ENABLE ROW LEVEL SECURITY ;
CREATE POLICY doc_select ON store.docs FOR SELECT TO service_functions USING ( (SELECT store.check_select(id)) );
CREATE POLICY doc_insert ON store.docs FOR INSERT TO service_functions WITH CHECK ( (SELECT store.check_insert(id)) );
CREATE POLICY docs_delete ON store.docs FOR DELETE TO service_functions USING ( (SELECT store.check_delete(id)) );
CREATE POLICY doc_update_using ON store.docs FOR UPDATE TO service_functions USING ( (SELECT store.update_using(id , is_del )) );
CREATE POLICY doc_update_check ON store.docs FOR UPDATE TO service_functions WITH CHECK ( (SELECT store.update_with_check(id , is_del )) );
ááááº
á¡áááºááŒá±áááºá
á¡ááá¯ááŒá¯áá¬ážáá±á¬ ááá¬áá»á°áá¬ááẠá á®ážááœá¬ážáá±ážáá¯ááºáááºážáá±á¬ááºáá¬á¡ááá·áºá០áá±áá¬ááááºážáááºážááŸá¯á¡ááá·áºááá¯á· á á¶ááá°áá¬ááŒá¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯ááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºááá¯ááºá á±áá²á·áááºá
áá¯ááºáááºážááá¯á¡ááºáá»ááºáá»á¬áž ááá¯á¡ááºáá«á ááá¯ááá¯áá±ááºáá®áááºážáá
áºáá±á¬ áá±áá¬ááŸááºáá¬ážáá±á¬ áá±á¬áºáááºáá»á¬ážááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºááẠáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááᯠááá°áá¬á¡ááŒá
Ạá¡áá¯á¶ážááŒá¯ááá¯ááºáá«áááºá
source: www.habr.com