Schema dump/restore not restoring grants on the schema

Поиск
Список
Период
Сортировка
От Mike Roest
Тема Schema dump/restore not restoring grants on the schema
Дата
Msg-id CAE7Byhhrgb==pTP7kKYs45ksjajjO3nRt7B=K3gVA8EdL4-NaQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Schema dump/restore not restoring grants on the schema  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hi There,
   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.

example

in template1:
create database backuptest;
create database restoretest;
create role testuser with login password 'password';

in backuptest;
create schema testschema
create table testschema.stuff (id integer not null);
grant usage on testschema to testuser;
grant insert,update,delete,select on testschema.stuff to testuser;

pg_dump -n testschema -d backuptest -U postgres -h localhost -F c -f test.backup
pg_restore -U postgres -d restoretest -h localhost -n testschema test.backup

In backuptest
backuptest=# \dn+
                            List of schemas
    Name    |  Owner   |  Access privileges   |      Description
------------+----------+----------------------+------------------------
 public     | postgres | postgres=UC/postgres+| standard public schema
            |          | =UC/postgres         |
 testschema | postgres | postgres=UC/postgres+|
            |          | testuser=U/postgres  |

in restore test:
restoretest=# \dn+
                            List of schemas
    Name    |  Owner   |  Access privileges   |      Description
------------+----------+----------------------+------------------------
 public     | postgres | postgres=UC/postgres+| standard public schema
            |          | =UC/postgres         |
 testschema | postgres |                      |
(2 rows)

How ever the table does have the grant in restoretest
restoretest=# \z testschema.stuff
                                   Access privileges
   Schema   | Name  | Type  |     Access privileges     | Column privileges | Policies
------------+-------+-------+---------------------------+-------------------+----------
 testschema | stuff | table | postgres=arwdDxt/postgres+|                   |
            |       |       | testuser=arwd/postgres    |                   |
(1 row)


This behaviour seems counter intuitive as unless I'm providing --no-acl on the backup or restore I would expect the grants on the schema to come along as well.

We've observed this behaviour with 9.5/10 & 11 client tools.

Thanks

--
Data's inconvienient when people have opinions.

В списке pgsql-general по дате отправления:

Предыдущее
От: Ayub M
Дата:
Сообщение: fetch time included in pg_stat_statements?
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: "Failed to connect to Postgres database" : No usage specified forcertificate (update)