Mike Roest <mike.roest@replicon.com> writes:
> Just trying to find out if something is intended behaviour. When doing a
> schema filtered pg_dump the created dump file includes the grants on that
> specific schema (in our case a grant usage to a unprivleged user) but doing
> a pg_restore with a -n <schema name> does not restore that grant however
> individual grants on object within the filtered schema are restored. But
> it's resulting in our unprivileged user not actually being able to access
> the limited number of tables it should be able to as the grant usage on the
> schema itself is being lost.
Hm. The pg_dump man page says
-n pattern
--schema=pattern
Dump only schemas matching pattern; this selects both the schema
itself, and all its contained objects.
while pg_restore says
-n schema
--schema=schema
Restore only objects that are in the named schema.
and the actual behavior seems consistent with that: pg_dump emits both
a CREATE SCHEMA command and GRANTs for it, while pg_restore emits
neither.
So I guess this is working as documented, but it does seem not very
nice that the two programs interpret the "same" switch differently.
I suppose the reasoning is lost in the mists of time :-(
Another thing that is not very nice is that pg_restore lacks the
ability to use patterns (wildcards) here. Someday maybe somebody
will get around to fixing that. I could see changing the definition
of -n to include the schema itself at the same time.
regards, tom lane