Muy buenos dias a todos.
Me pude dar un tiempo y me dedique a investigar el tema de referencia (Row
Security) llevo en ello ya dos días y pido disculpas si lo que pregunto es
muy obvio.
No encuentro como propagar la seguridad en integridad referencial.
Esto es: Si defino una regla de seguridad a nivel registro, quiero que los
detalles en otras tablas que hacen referencia, tambien hereden la
visibilidad definida en la regla.
Asi como logré hacerlo andar debo en todas las tablas definir las reglas, y
si en un detalle que apunta a una cabecera no lo hiciera, el usuario
logeado veria el detalle que tiene un ID en el campo del FK que apunta a
una tupla que para él no existe.
Esta es la prueba y como pude lograr lo que queria, aun cuando no es lo
automático que pretendo:
create schema aux;
create table aux.empresas (
id serial not null primary key,
descripcion text
);
create table aux.cabeceras (
id serial not null primary key,
descripcion text
);
create table aux.detalles (
id serial not null primary key,
cabera integer references aux.cabeceras(id),
descripcion text
)
create table aux.empresas_usos_datos (
esquema text,
tabla text,
id integer,
empresa integer references aux.empresas(id),
primary key (esquema, tabla, id, empresa)
)
create table aux.empresas_usuarios (
empresa integer references aux.empresas(id),
usuario text,
primary key (empresa,usuario)
)
grant all on aux.empresas_usos_datos to administracion;
grant all on aux.empresas_usuarios to administracion;
insert into aux.empresas (descripcion) values ('Empresa 1');
insert into aux.empresas (descripcion) values ('Empresa 2');
insert into aux.empresas_usuarios (empresa,usuario) values (1,'vhr');
insert into aux.empresas_usuarios (empresa,usuario) values (2,'sofy');
CREATE OR REPLACE FUNCTION aux.vinculo_tupla_con_empresa()
RETURNS trigger AS
$BODY$
declare
v_old_data TEXT;
v_new_data TEXT;
v_c integer;
r_cursor record;
id1 integer;
BEGIN
BEGIN
v_c := NEW.ID;
EXCEPTION
WHEN OTHERS THEN
BEGIN
v_c := NEW.GID;
EXCEPTION
WHEN OTHERS THEN
v_c := -1;
END;
END;
IF (TG_OP = 'DELETE') THEN
for id1 in (select distinct u1.empresa from aux.empresas_usuarios
u1 where u1.usuario = session_user::TEXT ) loop
RAISE NOTICE 'Ejecutado borrado % % % % % %',id1,
v_c,session_user, TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT, now();
delete from aux.empresas_usos_datos where
esquema=TG_TABLE_SCHEMA::TEXT and tabla=TG_TABLE_NAME::TEXT and id=v_c and
empresa= id1;
end loop;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
for id1 in (select distinct u1.empresa from aux.empresas_usuarios
u1 where u1.usuario = session_user::TEXT ) loop
RAISE NOTICE 'Ejecutado % % % % % %',id1,
v_c,user,session_user, current_user, now();
insert into aux.empresas_usos_datos (empresa,esquema,tabla,id )
values (id1, TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,v_c);
end loop;
RETURN NEW;
END IF;
EXCEPTION
WHEN data_exception THEN
RAISE WARNING '[aux.vinculo_tupla_con_empresa] - ERROR [DATA
EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN unique_violation THEN
RAISE WARNING '[aux.vinculo_tupla_con_empresa] - ERROR [UNIQUE] -
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN OTHERS THEN
RAISE WARNING '[aux.vinculo_tupla_con_empresa] - ERROR [OTHER] -
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
CREATE OR REPLACE FUNCTION aux.verifico_tupla_con_empresa(esquema_r
text,tabla_r text, id_r integer)
RETURNS boolean AS
$BODY$
declare
v_c integer;
r_cursor record;
id1 integer;
BEGIN
--RAISE NOTICE 'Ejecutado % % % %
%',id_r,esquema_r,tabla_r,session_user, now();
select 1 from aux.empresas_usos_datos u1 into id1
where u1.id = id_r and u1.esquema = esquema_r and u1.tabla =
tabla_r and exists (select 1 from aux.empresas_usuarios eu where eu.usuario
= session_user and eu.empresa = u1.empresa);
if (id1 = 1) then
return true;
else
return false;
end if;
EXCEPTION
WHEN data_exception THEN
RAISE WARNING '[aux.verifico_tupla_con_empresa] - ERROR [DATA
EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN unique_violation THEN
RAISE WARNING '[aux.verifico_tupla_con_empresa] - ERROR [UNIQUE] -
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN OTHERS THEN
RAISE WARNING '[aux.verifico_tupla_con_empresa] - ERROR [OTHER] -
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
grant all on aux.verifico_tupla_con_empresa(
CREATE TRIGGER cabeceras_trg
after INSERT OR DELETE
ON aux.cabeceras
FOR EACH ROW
EXECUTE PROCEDURE aux.vinculo_tupla_con_empresa();
CREATE TRIGGER detalles_trg
after INSERT OR DELETE
ON aux.detalles
FOR EACH ROW
EXECUTE PROCEDURE aux.vinculo_tupla_con_empresa();
GRANT ALL ON SCHEMA aux TO administracion;
grant all on aux.cabeceras to administracion;
GRANT ALL ON aux.cabeceras_id_seq TO administracion;
grant all on aux.detalles to administracion;
GRANT ALL ON aux.detalles_id_seq TO administracion;
ALTER TABLE aux.cabeceras ENABLE ROW LEVEL SECURITY;
DROP POLICY cabeceras_administracion ON aux.cabeceras;
CREATE POLICY cabeceras_administracion ON aux.cabeceras TO administracion
USING ( aux.verifico_tupla_con_empresa( 'aux','cabeceras', id)
);
drop POLICY cabeceras_select_administracion ON aux.cabeceras;
CREATE POLICY cabeceras_select_administracion ON aux.cabeceras for select
TO administracion
USING ( aux.verifico_tupla_con_empresa( 'aux','cabeceras', id)
);
drop POLICY cabeceras_insert_administracion ON aux.cabeceras;
CREATE POLICY cabeceras_insert_administracion ON aux.cabeceras for INSERT
TO administracion
WITH CHECK (true);
DROP POLICY cabeceras_update_administracion ON aux.cabeceras
CREATE POLICY cabeceras_update_administracion ON aux.cabeceras for update
TO administracion
USING ( aux.verifico_tupla_con_empresa( 'aux','cabeceras', id)
);
ALTER TABLE aux.detalles ENABLE ROW LEVEL SECURITY;
DROP POLICY detalles_administracion ON aux.detalles;
CREATE POLICY detalles_administracion ON aux.detalles TO administracion
USING ( aux.verifico_tupla_con_empresa( 'aux','detalles', id)
);
drop POLICY detalles_select_administracion ON aux.detalles;
CREATE POLICY detalles_select_administracion ON aux.detalles for select TO
administracion
USING ( aux.verifico_tupla_con_empresa( 'aux','detalles', id)
);
drop POLICY detalles_insert_administracion ON aux.detalles;
CREATE POLICY detalles_insert_administracion ON aux.detalles for INSERT TO
administracion
WITH CHECK (true);
DROP POLICY detalles_update_administracion ON aux.detalles
CREATE POLICY detalles_update_administracion ON aux.detalles for update TO
administracion
USING ( aux.verifico_tupla_con_empresa( 'aux','detalles', id)
);
select * from aux.detalles
delete from aux.empresas_usos_datos
where id < 19
table aux.empresas_usos_datos
table aux.empresas_usuarios
delete from aux.cabeceras
where id > 0
SET SESSION AUTHORIZATION vhr;
insert into aux.cabeceras(descripcion) values ('Intento con usuario:
'||session_user||' '||now()::text);
insert into aux.detalles (cabera,descripcion) select b.id,'renglon 1 de
'||b.descripcion from aux.cabeceras b
select * from aux.cabeceras
select * from aux.detalles
SET SESSION AUTHORIZATION sofy;
select * from aux.cabeceras
select * from aux.detalles
insert into aux.cabeceras(descripcion) values ('Intento con usuario:
'||session_user||' '||now()::text);
insert into aux.detalles (cabera,descripcion) select b.id,'renglon 1 de
'||b.descripcion from aux.cabeceras b
La pregunta hay algo automático que resuleva este problema?
O La integridad dependerá de mi prolijidad al definir las reglas de
seguridad?
Desde ya muchas gracias por la ayuda que pudieran darme y nuevamente
disculpas si lo que pregunto es trivial.
Fuerte Abrazo a todos
