Hi,
I'm trying create a SECURE TIER in my DB.
I need "dummys" tables that accept INSERTS from any users, then execute a
RULE that insert the datas into the REAL table. The REAL tables accept
INSERT only from "postgres user".
This work fine !
But when I need the RULE calls a FUNCTION (plpgsql) for more detailed data
manipulating, the FUNCTION runs with the normal user permissions, and not
with the RULE permissions.
Well, if a RULE calls the FUNCTION, the FUNCTION must run with the same
RULEs permissions ...
let-me show you.
----------------------------
\c - postgres
create table teste (codigo int4, nome varchar (30) );
create table teste2 (codigo int4, nome varchar (30) );
grant ALL on teste2 TO joe;
create rule teste2 as on insert to teste2 do insert into teste (codigo,nome)
values (new.codigo,new.nome);
\c - joe
insert into teste (codigo,nome) values (1,'tulio');
--> PERMISSION DENIED ! - OK
insert into teste2 (codigo,nome) values (1,'tulio');
--> INSERTS - OK
select * from teste2;
--> SHOW THE ROWS - OK
\c - postgres
select * from teste;
--> SHOW THE ROWS - OK
------------------------------------------------------------------------
In this example, all is OK, but ...
----------------------------
\c - postgres
create table teste (codigo int4, nome varchar (30) );
create table teste2 (codigo int4, nome varchar (30) );
grant ALL on teste2 TO joe;
create function teste (integer,text) returns integer as '
begin
insert into teste (codigo,nome) values ($1,$2);
end;' language 'plpgsql';
create rule teste2 as on insert to teste2 do select teste
(new.codigo::integer,new.nome::text);
\c - joe
insert into teste (codigo,nome) values (1,'tulio');
--> PERMISSION DENIED ! - OK
insert into teste2 (codigo,nome) values (1,'tulio');
--> PERMISSION DENIED ON TESTE =============================>> NOT OK
------------------------
Sorry my English..... Do you undestand ??
Could you help-me ?
I realy need make HEAVY consistencys, and I need a FUNCTION ...