Schema dump/restore not restoring grants on the schema

[Mike Roest]

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  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.

Re: Schema dump/restore not restoring grants on the schema

[Mike Roest]

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.

--Mike

pg_visible_in_snapshot clarification

[Mike Roest]

Hey There,
   I'm looking for some clarification around pg_visible_in_snapshot
function.  As it seems to not be working the way I would expect or the way
the documentation is setup.

I've attempted this on pg13 and pg15 and am getting the same behaviour.

3 connections via psql 2 to the primary and 1 to the secondary
C1: primary
C2: primary
C3: secondary (setup using streaming replication with hot_standby_feedback
on)

Reproduction:
C1:
CREATE TABLE test (
id integer,
val text);
BEGIN TRANSACTION;
INSERT INTO test values (1, 'test 1');
SELECT pg_current_xact_id();

C3:
SELECT pg_visible_in_snapshot(''::xid8,pg_current_snapshot()); -- returns false
SELECT pg_current_snapshot(); -- shows ::

C2
BEGIN TRANSACTION;
INSERT INTO test values (2, 'test 2');
SELECT pg_current_xact_id();


C3
SELECT pg_visible_in_snapshot(''::xid8,pg_current_snapshot()); -- returns false
SELECT pg_visible_in_snapshot(''::xid8,pg_current_snapshot()); -- returns false
SELECT pg_current_snapshot(); -- shows ::

C2:
COMMIT;

C3
SELECT pg_visible_in_snapshot(''::xid8,pg_current_snapshot()); -- returns true
SELECT pg_visible_in_snapshot(''::xid8,pg_current_snapshot()); -- returns true
SELECT pg_current_snapshot(); -- shows ::

This is where things don't make sense to me.  I would expect
SELECT pg_visible_in_snapshot(''::xid8,pg_current_snapshot()); -- returns true

to return false as the transaction on C1 is still open and not commited.
The 1,test 1 record in the test table is not available on the secondary yet
however
pg_visible_in_snapshot is returning true for it's xactid.

I think this has to do with the pg_current_snapshot not showing the
transaction in the xip_list which appears to be empty on both C1 (in the
transaction) and C3 on the replica.  However C2 pg_current_snapshot() does
show C1 xactid as in progress in the xip_list.

So I'm guessing from this that pg_visible_in_snapshot is not safe to use
between a primary and a secondary?

If anyone could provide any additional insight that would be amazing.

Thanks