Re: BUG #13907: Restore materialized view throw permission denied

On Tue, Jul 26, 2016 at 8:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kevin Grittner <kgrittn@gmail.com> writes:
>> So you are suggesting that restoring from pg_dump output should
>> generate materialized view data under a different security context
>> than would be used by a REFRESH statement on the source database?
>
> Yes.  Consider the following simple example (done by a non-superuser
> named joe):
>
> create table joes_table(f1 int);
> insert into joes_table values(1);
> revoke insert on joes_table from joe;
>
> pg_dump is required to be able to restore the state of this table
> correctly.  It will fail to do so if it issues the revoke before
> loading data.  The same issue applies to all data loading,
> including refreshing matviews.

test=# create role joe;
CREATE ROLE
test=# set role joe;
SET
test=> create table joes_table(f1 int);
CREATE TABLE
test=> insert into joes_table values(1);
INSERT 0 1
test=> revoke insert on joes_table from joe;
REVOKE
test=> create materialized view joes_mv as select * from joes_table;
SELECT 1
test=> revoke insert on joes_mv from joe;
REVOKE
test=> refresh materialized view joes_mv;
REFRESH MATERIALIZED VIEW

The problem we're having restoring the matview state is that not
all ACLs needed for *SELECT* permissions are in place in time.  I
am not seeing the problem with self-revoke on REFRESH.  What am I
missing?

Of course, since the REVOKE on the matview has no affect, it should
probably not be allowed.  I could add an error for the attempt.

One other question about the patch was what I did for testing.  It
seemed like a good idea to have dump/restore tests, but I don't see
how to do that without leaving a role or two lingering in the
cluster.  Is that allowed?  (I see that I need to DIE first, to
prevent errors on multiple runs, but otherwise?)

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company