Thread: BUG #13318: refresh materilaized view privileges

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

On 5/19/2015 2:07 PM, Adrien.Sales@gmail.com wrote:
> CREATE OR REPLACE FUNCTION refresh_astre()
> RETURNS void
> SECURITY DEFINER    -- <== added this
> 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();
> ...

try again, with SECURITY DEFINER in the function declaration.   This
says when this function is run, it runs with the permissions of the
function owner, rather than the caller.

.

--
john r pierce, recycling bits in santa cruz