BUG #14147: Restore of MatView of Foreign Data Table fails

The following bug has been logged on the website:

Bug reference: 14147
Logged by: Lloyd Albin
Email address: lalbin@scharp.org
PostgreSQL version: 9.5.3
Operating system: SUSE Linux (64-bit)
Description:

I have found that restoration of Materialized Views of Foreign Data Tables
where user postgres does not have a user mapping will fail. It seems that it
is trying to execute the Foreign Data Table in some way instead of reading
the Foreign Table Definition.

The only workaround that I have found is to create a user mapping for user
postgres for every foreign data connection.

Example:

CREATE DATABASE db_a
WITH ENCODING = 'UTF8'
TEMPLATE = template1;

CREATE ROLE joe LOGIN PASSWORD 'xxx';

SET ROLE joe;

CREATE TABLE public.test (
id SERIAL,
v TEXT,
PRIMARY KEY(id)
)
WITH (oids = false);

SET ROLE postgres;

CREATE DATABASE db_b
WITH ENCODING = 'UTF8'
TEMPLATE = template1;

CREATE EXTENSION postgres_fdw;

GRANT USAGE
ON FOREIGN DATA WRAPPER postgres_fdw TO joe;

CREATE SERVER db_a
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host 'localhost',
port '5432',
dbname 'db_a');

CREATE USER MAPPING FOR joe
SERVER db_a
OPTIONS (
user 'joe',
password 'xxx');

CREATE FOREIGN TABLE public."test" (
id INTEGER NOT NULL,
v TEXT
)
SERVER db_a
OPTIONS (
schema_name 'public',
table_name 'test');

CREATE MATERIALIZED VIEW public.mv_test AS SELECT * FROM public.test;

CREATE DATABASE db_c
WITH ENCODING = 'UTF8'
TEMPLATE = template1;

pg_dump -h localhost -U postgres -Fc db_b > db_b.pgdump

pg_restore -h localhost -U postgres -d db_c db_b.pgdump

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 183; 1259 16430
MATERIALIZED VI
EW mv_test joe
pg_restore: [archiver (db)] could not execute query: ERROR: user mapping
not fo
und for "postgres"
Command was: CREATE MATERIALIZED VIEW mv_test AS
SELECT test.id,
test.v
FROM test
WITH NO DATA;

pg_restore: [archiver (db)] could not execute query: ERROR: relation
"mv_test"
does not exist
Command was: ALTER TABLE mv_test OWNER TO joe;

pg_restore: [archiver (db)] Error from TOC entry 2104; 0 16430 MATERIALIZED
VIEW
DATA mv_test joe
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"mv_test"
does not exist
Command was: REFRESH MATERIALIZED VIEW mv_test;

WARNING: errors ignored on restore: 3

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