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