PostgreSQL 9.3 – How to Refresh Materialized Views with Different Users

errorsmaterialized-viewpermissionspostgresqlpostgresql-9.3

[I think the root cause of this problem is me not understanding permissions and privileges…]

So, to set the stage, the set up I have is a DB, call it MyDb.

I have two users, spu1 and u1, spu1 is a superuser and u1 a 'regular' user. The owner of MyDb is spu1. I think I should also say that u1 has create databases & create role privileges as inherited from a group role.

I have a schema sch1, which is a user defined schema.

Within this schema I have a table, call it tbl1, and a materialised view, call it mvw1.

The tbl1's owner is the spu1, the mvw1's owner is u1.

The Problem:

In the current set up, as described above, I cannot refresh mvw1 as either u1 or spu1. I simply get the below fun error (which I have googled extensively but not found anything which quite solves for my setup..).

ERROR:  permission denied for relation tbl1
********** Error **********

ERROR: permission denied for relation tbl1
SQL state: 42501

I have found out that

  1. Changing the owner of mvw1 to the spu1, allows me to refresh as spu1.
  2. Running the below allows me to refresh mvw1 as u1.

I'm trying to find out what the missing permission(s) (ideally the minimum required) I need to grant the regular user, u1, so that I can refresh this view when logged in as them.

The first option, whilst nice to know, doesn't solve my problem.
The second option seems like I'm in effect granting superuser permissions to a non-superuser, or rather granting greater privileges than I need to.

If anyone can explain to me what exactly is going on here (or point out what info I've missed out in the description of my problem needed to solve it), and let me know if my second option is in fact the way to go or of a better alternative?

Many Thanks!

Best Answer

You can do this with a function that runs in the security context of its owner.

Function that refreshes the view (create it with the user that owns the MV/table):

CREATE OR REPLACE FUNCTION refresh_mvw1()
RETURNS void
SECURITY DEFINER
AS $$
BEGIN
REFRESH MATERIALIZED VIEW mvw1 with data;
RETURN;
END;
$$ LANGUAGE plpgsql;

Grant execute on the function to any user that you want to be able to refresh the view:

-- Users have 'execute' permissions by default on functions!
revoke all on function refresh_mvw1() from public;
grant execute on function refresh_mvw1() to u1;

To refresh:

select refresh_mvw1();

From the Postgres docs:

SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.

Version that supports a parameter:

CREATE OR REPLACE FUNCTION refresh_mv_xxx(table_name text)
RETURNS void
SECURITY DEFINER
AS $$
DECLARE sql text; 
BEGIN
sql := 'REFRESH MATERIALIZED VIEW ' || table_name || ' with data';
EXECUTE sql;
RETURN;
END;
$$ LANGUAGE plpgsql;

... but I'm not sure if dynamic SQL will still execute as the definer.

Related Question