Re: Schema dump/restore not restoring grants on the schema
От | Adrian Klaver |
---|---|
Тема | Re: Schema dump/restore not restoring grants on the schema |
Дата | |
Msg-id | c13ebf41-c6ab-7acd-0e31-32d896463e32@aklaver.com обсуждение исходный текст |
Ответ на | Re: Schema dump/restore not restoring grants on the schema (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Следующее
От: Adrian KlaverДата:
Сообщение: Re: Schema dump/restore not restoring grants on the schema