[GENERAL] Are Update rights on the target table of a do instead update rule necessary ?

[Geraldo Lopes de Souza]

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