Thread: BUG #5560: pg_dump generates invalid DDL

The following bug has been logged online:

Bug reference:      5560
Logged by:          Shigeru Hanada
Email address:      hanada@metrosystems.co.jp
PostgreSQL version: 9.1devel
Operating system:   Red Hat Enterprise Linux Server release 5.1 (Tikanga)
Description:        pg_dump generates invalid DDL
Details:

When target database contains user mapping for public, pg_dump generates
invalid DDL with empty string like this.

CREATE USER MAPPING FOR "" SERVER srv;

This DDL causes error below.

ERROR:  zero-length delimited identifier at or near """" at character 25
STATEMENT:  CREATE USER MAPPING FOR "" SERVER srv;

How to reproduce:

psql<<EOF
CREATE FOREIGN DATA WRAPPER fdw;
CREATE SERVER srv FOREIGN DATA WRAPPER fdw;
CREATE USER MAPPING FOR PUBLIC SERVER srv;
EOF
pg_dump > mapping.sql
createdb newdb
psql -f mapping.sql newdb

"Shigeru Hanada" <hanada@metrosystems.co.jp> writes:
> When target database contains user mapping for public, pg_dump generates
> invalid DDL with empty string like this.

> CREATE USER MAPPING FOR "" SERVER srv;

I can reproduce this behavior.

Another serious problem with the pg_dump code for user mappings is that
it fails outright if the user is not superuser --- meaning that that
patch has completely broken non-superuser use of pg_dump.  That's
entirely unacceptable IMO.

The simplest fix seems to be to have pg_dump look at
the pg_user_mappings view rather than directly at pg_user_mapping.
That both provides the special case for PUBLIC and gets around the
permissions problem.  It does mean that user mappings will be silently
dumped without the options data if you don't have privileges for the
specific mapping, but ISTM that beats failing entirely.  (Alternatively,
we could probably persuade pg_dump to not dump mappings the caller
hasn't got permissions for ... is that better?)

Peter, any comments?

            regards, tom lane