Schema dump/restore not restoring grants on the schema

From: Mike Roest <mike(dot)roest(at)replicon(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Schema dump/restore not restoring grants on the schema
Date: 2019-10-01 12:02:17
Message-ID: CAE7Byhhrgb==pTP7kKYs45ksjajjO3nRt7B=K3gVA8EdL4-NaQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-10-01 14:28:15 Re: "Failed to connect to Postgres database" : No usage specified for certificate (update)
Previous Message Ayub M 2019-10-01 09:25:08 fetch time included in pg_stat_statements?