On 10/1/19 7:53 AM, Tom Lane wrote:
Mike Roest <mike.roest@xxxxxxxxxxxx> 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@xxxxxxxxxxx
