Re: pg_restore restores privileges differently from psql

Sherrylyn Branchaw <sbranchaw(at)gmail(dot)com> writes:
> I'm restoring the schema from one database (prod) to another (dev). There
> are users that exist in prod that don't exist in dev. When the restore job
> tries to grant privileges to nonexistent users in dev, I would like it to
> generate an error, which is safe to ignore, but still correctly grant
> privileges to any user that does exist in dev.

> That's the behavior I see when I dump to a plain file and restore it
> using *psql
> -f*, but not the behavior I see when I do a *pg_dump -Fc* followed by
> *pg_restore.* *pg_restore *seems to treat all the *GRANT* statements for a
> single object as a single statement, and when one errors out, they all
> error out, meaning I'm left with no privileges on the object in question.

Yeah, this is a known issue --- the various GRANTs for a specific object
are stored in a single "TOC entry" in the archive, which pg_restore will
send to the server in a single PQexec call, causing them to be effectively
one transaction. The easiest way to deal with it is to not send
pg_restore's output directly to the target server, but feed it through
psql, something like

pg_restore ... | psql [connection parameters]

There's been some discussion of a real fix, but it seems messy.
pg_restore doesn't have a parser that would be adequate to separate
out multiple SQL commands in a TOC entry, and we'd rather not try
to give it one (mainly because of fear of cross-version compatibility
issues).

regards, tom lane