PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?

Question

[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!

Answer 1

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.

Answer 2

@Philᵀᴹ's second function attempt is vulnerable to SQL injection by using:

SELECT refresh_mv_xxx('example_mview_name with data; TRUNCATE TABLE example_table_name CASCADE; REFRESH MATERIALIZED VIEW example_mview_name')

where example_mview_name and example_table_name refer to any of your materialized views and tables, respectively.

Which is dangerous in multitude of ways.

I would suggest either using his first attempt only or the following way:

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

format() with format specifier %I

I treats the argument value as an SQL identifier, double-quoting it if necessary. It is an error for the value to be null (equivalent to quote_ident).

Answer 3

Another option is to create a (non-superuser) group role to which both spu1 and u1 belong to/inherit from (e.g. "refreshers"), and assign that group role as the owner of the materialized view.