Обсуждение: BUG #13458: postgres_fdw with usermapping dumped with pg_dump --no-owners results in dump file with implicit own

The following bug has been logged on the website:

Bug reference:      13458
Logged by:          Greg Burek
Email address:      gregburek@heroku.com
PostgreSQL version: 9.4.2
Operating system:   Linux
Description:

Hello,

The same customer as in bug #13457 has found that a postgres_fdw user
mapping dictates that pg_dump --no-acl --no-owner will reference the owner
of the dumped db and will produce an error during pg_restore --no-acl
--no-owner to a db without a matching previous user.

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'
);

A pg_dump --no-acl --no-owner will reference the user:

--
-- TOC entry 4313 (class 0 OID 0)
-- Dependencies: 2216
-- Name: USER MAPPING u5cuus46hhtdfs SERVER redshift_dw; Type: USER MAPPING;
Schema: -; Owner: -
--

CREATE USER MAPPING FOR u5cuus46hhtdfs SERVER redshift_dw OPTIONS (

And a pg_restore --no-acl --no-owner will fail on this line as pg_restore is
using a different user:

pg_restore: [archiver (db)] Error from TOC entry 4491; 0 0 USER MAPPING USER
MAPPING u5cuus46hhtdfs SERVER redshift_dw u5cuus46hhtdfs
pg_restore: [archiver (db)] could not execute query: ERROR:  role
"u5cuus46hhtdfs" does not exist
Command was: CREATE USER MAPPING FOR u5cuus46hhtdfs SERVER redshift_dw
OPTIONS (

Should the schema user mapping output by pg_dump --no-owner preserve the
previous owner, as it does above, or is there a way to make the user mapping
mutable during a dump and restore so that it may be restored to an arbitrary
user?

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

Greg

gregburek@heroku.com writes:
> 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'
> );

> A pg_dump --no-acl --no-owner will reference the user:

> --
> -- TOC entry 4313 (class 0 OID 0)
> -- Dependencies: 2216
> -- Name: USER MAPPING u5cuus46hhtdfs SERVER redshift_dw; Type: USER MAPPING;
> Schema: -; Owner: -
> --
> CREATE USER MAPPING FOR u5cuus46hhtdfs SERVER redshift_dw OPTIONS (

> And a pg_restore --no-acl --no-owner will fail on this line as pg_restore is
> using a different user:

> pg_restore: [archiver (db)] Error from TOC entry 4491; 0 0 USER MAPPING USER
> MAPPING u5cuus46hhtdfs SERVER redshift_dw u5cuus46hhtdfs
> pg_restore: [archiver (db)] could not execute query: ERROR:  role
> "u5cuus46hhtdfs" does not exist
> Command was: CREATE USER MAPPING FOR u5cuus46hhtdfs SERVER redshift_dw
> OPTIONS (

This does not seem like a bug to me.  The only thing we could really do
to avoid the error is to decree that pg_restore should treat USER MAPPING
objects as being suppressed altogether by --no-acl or --no-owner; and
I'm doubtful that that would be an improvement.

            regards, tom lane