BUG #13318: refresh materilaized view privileges

From: Adrien(dot)Sales(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13318: refresh materilaized view privileges
Date: 2015-05-19 21:07:27
Message-ID: 20150519210727.26514.22507@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13318
Logged by: Adrien Sales
Email address: Adrien(dot)Sales(at)gmail(dot)com
PostgreSQL version: 9.3.6
Operating system: Linux, Windows
Description:

Introduction : I don't think it's a bug but rather a Work As Designed
feature, indeed, it would be very useful to make it work the same way Oracle
does.

Description : A user (user_usr) needs to refresh a materialized view that he
does not own (the owner is user_adm). This user has all DML privileges on
user_adm schema but no DDL privileges.
On oracle, when we have the same need (for example for truncating a table),
we embed the sql in a stored procedure, grant execute privilege on the
procedure... and the job is done...but it does not work on PostgreSQL as it
does still complain that "ERROR: must be owner of relation".

Please find deblow the script used to test :

psql -U user_adm test

create table titi(i integer);
insert into titi values (1);
insert into titi values (2);
insert into titi values (3);
insert into titi values (4);

select * from titi;

create materialized view vm_titi as select * from titi;

REFRESH MATERIALIZED VIEW vm_titi with data;

CREATE OR REPLACE FUNCTION refresh_astre()
RETURNS void
AS $$
BEGIN
REFRESH MATERIALIZED VIEW vm_titi with data;
RETURN;
END;
$$ LANGUAGE plpgsql;

select refresh_astre();

grant execute on function refresh_astre() to user_usr;

psql -U user_usr test

test=> select refresh_astre();
ERROR: must be owner of relation vm_titi
CONTEXT: SQL statement "REFRESH MATERIALIZED VIEW vm_titi with data"
PL/pgSQL function refresh_astre() line 6 at EXECUTE statement

Any feedback would be appreciated on this behavior. The constraint is that
we don't want the user_usr user to own any objects, but only to use the
user_adm ones.
Thank you in advance for your help.

Best Regards,

Adrien

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message John R Pierce 2015-05-19 22:39:18 Re: BUG #13318: refresh materilaized view privileges
Previous Message David G. Johnston 2015-05-19 20:18:00 Re: BUG #13317: Cannot select multiple columns from multiple rows returned from PL/Python function