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

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