The following bug has been logged on the website:
Bug reference: 13318
Logged by: Adrien Sales
Email address: Adrien.Sales@gmail.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