Failure loading materialized view with pg_restore

From: Brian Sutherland <brian(at)vanguardistas(dot)net>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Failure loading materialized view with pg_restore
Date: 2015-02-18 10:48:05
Message-ID: 20150218104805.GB33325@Admins-MacBook-Air.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

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:

# 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
createuser -S nobody
psql --username super -c 'ALTER TABLE myview OWNER TO "nobody";' 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

The error I get is:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2260; 0 16569 MATERIALIZED VIEW DATA myview nobody
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for relation x
Command was: REFRESH MATERIALIZED VIEW myview;

In pg_hba I am using the "trust" method for everything (this is a test
cluster).

Is this expected behaviour or a bug?

--
Brian Sutherland

Responses

Browse pgsql-general by date

  From Date Subject
Next Message BladeOfLight16 2015-02-18 11:03:21 Re: Failure loading materialized view with pg_restore
Previous Message Dmitry O Litvintsev 2015-02-18 04:55:47 postgresql93-9.3.5: deadlock when updating parent table expected?