La estrategia de uso implica el uso del concepto de "lógica de negocios en la base de datos", que se acaba de describir en detalle aquí - Un estudio sobre la implementación de la lógica de negocios almacenada en las funciones de PostgreSQL
parte teórica perfectamente descrita en la documentación de las líneas Postgres Pro - Políticas de protección . A continuación, consideramos la implementación práctica de una tarea comercial específica: un modelo a seguir de acceso a datos.
El artículo no es nada nuevo, no hay ningún significado oculto ni conocimiento secreto. Solo un boceto sobre la implementación práctica de una idea teórica. Si alguien está interesado, siga leyendo. Quién no está interesado, no pierda el tiempo.
Formulación del problema
Es necesario delimitar el acceso para visualizar / insertar / modificar / eliminar un documento de acuerdo con el rol del usuario de la aplicación. Un rol significa una entrada en la tabla de roles relacionada en una relación de muchos a muchos con la tabla de usuarios . Los detalles de implementación de las tablas se omiten por trivialidad. Además, se omiten los detalles de implementación específicos relacionados con el dominio.
Implementación
Creamos roles, esquemas, tabla
Creando objetos de base de datos
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 ;
Creando funciones para implementar RLS
Comprobando si una fila es 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;
Comprobación de filas INSERT
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;
Comprobando si una fila se puede BORRAR
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;
Comprobando si la fila se puede ACTUALIZAR.
update_using
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;
comprobación de actualización
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;
Habilitación de la política de seguridad de nivel de fila para la mesa.
HABILITAR LA SEGURIDAD DE NIVEL DE FILA
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 )) );
Salir
Funciona.
La estrategia propuesta hizo posible trasladar la implementación del modelo a seguir del nivel de funciones comerciales al nivel de almacenamiento de datos.
Las funciones se pueden utilizar como plantilla para implementar modelos de ocultación de datos más sofisticados si los requisitos comerciales así lo requieren.