BUG #13457: postgres_fdw, non-postgres user mapping, materialized view leads to failed pg_upgrade

The following bug has been logged on the website:

Bug reference: 13457
Logged by: Greg Burek
Email address: gregburek(at)heroku(dot)com
PostgreSQL version: 9.4.2
Operating system: Linux
Description:

Hello,

A customer has run into a strange interaction between the postgres_fdw
extension, materialized view and pg_restore, as used by pg_upgrade.

Reproduction schema:

CREATE SERVER redshift_dw FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
dbname 'reporting',
host 'example.com',
port '5439',
sslmode 'require'
);
ALTER SERVER redshift_dw OWNER TO u5cuus46hhtdfs;
CREATE USER MAPPING FOR u5cuus46hhtdfs SERVER redshift_dw OPTIONS (
password '',
"user" 'user'
);

CREATE MATERIALIZED VIEW daily_stats_mv AS
SELECT daily_stats_v.campaign_id,
daily_stats_v.targeting_group_id,
daily_stats_v.creative_id,
daily_stats_v.date,
daily_stats_v.impressions,
daily_stats_v.clicks,
daily_stats_v.media_cost,
daily_stats_v.spend,
daily_stats_v.serving_fees
FROM daily_stats_v
WITH NO DATA;
ALTER TABLE public.daily_stats_mv OWNER TO u5cuus46hhtdfs;

When running upgrading from postgres version 9.3.5 to 9.4.2, the pg_upgrade
command fails with logs that include:

pg_restore: creating MATERIALIZED VIEW daily_stats_mv
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 354; 1259 266280
MATERIALIZED VIEW daily_stats_mv u5cuus46hhtdfs
pg_restore: [archiver (db)] could not execute query: ERROR: user mapping
not found for "postgres"
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('266282'::pg_catalog.oid);

It appears that as part of the pg_upgrade script, the db is run through
pg_dump and pg_restore. The schema generated by pg_dump appears to attempt
to create the MATERIALIZED VIEW daily_stats_mv as user postgres and then
change ownership to user u5cuus46hhtdfs. The table create fails because the
postgres_fdw that the materialized view is based on has no user mapping for
the postgres user, even though the correct user is set as the next
statement.

Should the schema be rendered by pg_dump so that the materialized view is
created as the intended user to avoid a trip through the postgres user,
which may or may not have a user mapping that dictates if the materialized
view may be created?

User worked around the issue by dropping the fdw and the materialized view
before performing a dump and restore upgrade.

Greg