Thread: 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@heroku.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

I'm running into this same problem using pg_dumpall.

On Sun, Jun 21, 2015 at 9:20 PM, <gregburek@heroku.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13457
> Logged by:          Greg Burek
> Email address:      gregburek@heroku.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
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>