Обсуждение: 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

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

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.klaver@aklaver.com

On 10/1/19 8:49 AM, Mike Roest wrote:
> Thanks for the reply Tom,
>     We're going to look at removing the filtering on the pg_restore 
> which I think should allow us to move forward since we have the pg_dump 
> already filtered.

It will. If you want to verify do:

pg_restore -f testschema.txt test.backup

That will give you a plain text version of the restore.

> 
> --Mike
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com