Re: Failure loading materialized view with pg_restore

[Brian Sutherland <brian@xxxxxxxxxxxxxxxxx>]

On Wed, Feb 18, 2015 at 10:34:33AM -0500, Tom Lane wrote:
> Brian Sutherland <brian@xxxxxxxxxxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general