Re: Failure loading materialized view with pg_restore

On Wed, Feb 18, 2015 at 10:34:33AM -0500, Tom Lane wrote:
> Brian Sutherland <brian(at)vanguardistas(dot)net> writes:
> > If I run this set of commands against PostgreSQL 9.4.1 I pg_restore
> > throws an error with a permission problem. Why it does so is a mystery
> > to me, given that the user performing the restore is a superuser:
>
> The same thing would happen without any dump and restore:
>
> regression=# create user nobody;
> CREATE ROLE
> regression=# CREATE TABLE x (y int);
> CREATE TABLE
> regression=# CREATE MATERIALIZED VIEW myview AS select * from x;
> SELECT 0
> regression=# ALTER TABLE myview OWNER TO "nobody";
> ALTER TABLE
> regression=# REFRESH MATERIALIZED VIEW myview;
> ERROR: permission denied for relation x
>
> User "nobody" does not have permission to read table x, so the REFRESH
> fails, because the view's query executes as the view's owner.

If you grant select permission for the user nobody on x, pg_restore
still fails even though a REFRESH succeeds:

# superuser creates database and materialized view
createuser -s super

createdb --username super orig
psql --username super -c "select 'USING:' || version();" orig
psql --username super -c 'CREATE TABLE x (y int);' orig
psql --username super -c 'CREATE MATERIALIZED VIEW myview AS select * from x' orig

# change the owner of the view to myview and grant SELECT to nobody
createuser -S nobody
psql --username super -c 'GRANT SELECT ON x TO nobody' orig
psql --username super -c 'ALTER TABLE myview OWNER TO "nobody";' orig

# refresh does work if you are nobody
psql --username nobody -c 'REFRESH MATERIALIZED VIEW myview;' orig

# dump and reload
pg_dump --username super --format c -f dump.dump orig
createdb copied

# pg_restore errors
pg_restore --username super -d copied dump.dump

I guess I provided a too-minimal example...

--
Brian Sutherland