Hi, 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...@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 >
