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

On 10/1/19 7:53 AM, Tom Lane wrote:
> 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 :-(

Some fooling around on my part found:

pg_restore -d test -U postgres -n utility utility_schema.out

pg_restore: [archiver (db)] could not execute query: ERROR: schema
"utility" does not exist

test_(postgres)# create schema utility;
CREATE SCHEMA
test_(postgres)# \dn+ utility
List of schemas
Name | Owner | Access privileges | Description
---------+----------+-------------------+-------------
utility | postgres | |
(1 row)

pg_restore -d test -U postgres -n utility utility_schema.out

test_(postgres)# \dn+ utility
List of schemas
Name | Owner | Access privileges | Description
---------+----------+-------------------+-------------
utility | postgres | |
(1 row)

test_(postgres)# drop schema utility cascade;

pg_restore -d test -U postgres utility_schema.out

test_(postgres)# \dn+ utility
List of schemas
Name | Owner | Access privileges | Description
---------+----------+-----------------------+-------------
utility | postgres | postgres=UC/postgres +|
| | production=U/postgres |
(1 row)

Looks to me the -n argument on restore is for restoring the objects into
an existing schema. Leaving it off restores the schema and the objects.

>
> 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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com