Re: Are Update rights on the target table of a do instead update rule necessary ?

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(dot)ls(at)gmail(dot)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
>