Apologies for flooding the list. I was updating the table when I should update the view.
That's the price for working after a regular day job.
Regards,
Geraldo Lopes de Souza
2011/4/16 Geraldo Lopes de Souza <geraldo.ls@gmail.com>
Hi,
I'm trying to implement tenant view filter with postgres. The docs says
"Rewrite rules don't have a separate owner. The owner of a relation (table or view) is automatically the owner of the rewrite rules that are defined for it. The PostgreSQL rule system changes the behavior of the default access control system. Relations that are used due to rules get checked against the privileges of the rule owner, not the user invoking the rule. This means that a user only needs the required privileges for the tables/views that he names explicitly in his queries."
Postgres 9.0.3
I can confirm that on insert and delete rules: (do nothing ones ommited)
create rule tnt_operadora_insert as
on insert to tnt_operadora
where new.tenant_id = current_tenant()
do instead
insert into operadora (id, tabeladecobranca, versaodoxml, nome, numeronaoperadora, testedouble, registroans, "version", tenant_id)
values (new.id, new.tabeladecobranca, new.versaodoxml, new.nome, new.numeronaoperadora, new.testedouble, new.registroans, new.version, new.tenant_id);
create rule tnt_operadora_del as
on delete to tnt_operadora
where old.tenant_id=current_tenant()
do instead
delete from operadora
where tenant_id=old.tenant_id and
id=old.id;
the view is tnt_operadora is a proxy for operadora table and to insert into or delete from this view the user needs privileges to the view only docs says.
GRANT SELECT,INSERT,UPDATE,DELETE ON TNT_OPERADORA TO PUBLIC;
For update rule that's not the case:
create rule tnt_operadora_upd as
on update to tnt_operadora
where old.tenant_id = current_tenant() and
new.tenant_id = old.tenant_id
do instead
update operadora
set
tabeladecobranca = new.tabeladecobranca,
versaodoxml = new.versaodoxml,
nome = new.nome,
numeronaoperadora = new.numeronaoperadora,
testedouble = new.testedouble,
registroans = new.registroans,
"version" = new."version"
where
tenant_id = old.tenant_id and
id = old.id;
Unless the user has update rights on the target table operadora I get:
/opt/PostgreSQL/9.0/bin/psql clinica_dev tnt1 -f upd.sql
Password for user tnt1:
psql:upd.sql:3: ERROR: permission denied for relation operadora
upd.sql:
update operadora
set tabeladecobranca= 'new value'
where id=83 and tenant_id=1
Further details:
The purpose of these rules is to limit application code activities to the records that belong's to the ordinary user representing the tenant, that is intercepted through current_tenant() function.
create domain tenant_id integer not null;
create table tenant (
id tenant_id primary key,
nome text not null,
email text
);
create or replace function current_tenant() returns tenant_id as $$
begin
if substring(current_user,1,3) = 'tnt' then
return cast( substring(current_user,4,10) as integer);
else
return null;
end if;
end
$$ language plpgsql
create or replace view public.tnt_operadora as
select * from public.operadora
where tenant_id=current_tenant();
Thank you very much,
Geraldo Lopes de Souza
