Re: Schema dump/restore not restoring grants on the schema

Mike Roest <mike(dot)roest(at)replicon(dot)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