Can't adjust GRANTs after backup and restore

[Chri-s]

I tried the following on PostgreSQL 13+14 on Windows 10.

I created a new database and ran:

create extension periods cascade;

create table test (id int not null primary key);

select periods.add_system_time_period('test');
select periods.add_system_versioning('test');

Everything works fine.

Then I dump and restore the database:

pg_dump.exe --file=periods_test.dump --format=custom periods_test
psql -c "DROP DATABASE periods_test;"
pg_restore -d postgres -C periods_test.dump

pg_restore raises these errors:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3392; 0 0 ACL TABLE test_history postgres
pg_restore: error: could not execute query: ERROR:  cannot revoke SELECT directly from "public.test_history", revoke SELECT from "public.test" instead
CONTEXT:  PL/pgSQL function periods.health_checks() line 255 at RAISE
Command was: REVOKE ALL ON TABLE public.test_history FROM postgres;
GRANT SELECT ON TABLE public.test_history TO postgres;


pg_restore: from TOC entry 3393; 0 0 ACL TABLE test_with_history postgres
pg_restore: error: could not execute query: ERROR:  cannot revoke SELECT directly from "public.test_with_history", revoke SELECT from "public.test" instead
CONTEXT:  PL/pgSQL function periods.health_checks() line 255 at RAISE
Command was: REVOKE ALL ON TABLE public.test_with_history FROM postgres;
GRANT SELECT ON TABLE public.test_with_history TO postgres;


pg_restore: warning: errors ignored on restore: 2

From now on, I can't change privileges.

If I run grant select on test to postgres; I get

ERROR:  cannot grant DELETE to "test_history"; history objects are read-only
CONTEXT:  PL/pgSQL function periods.health_checks() line 138 at RAISE

I think the reason for this is in

periods/periods--1.2.sql

Lines 3397 to 3459 in 9bb3df9

	FOR r IN
	SELECT *,
	EXISTS (
	SELECT
	FROM pg_class AS _c
	CROSS JOIN LATERAL aclexplode(COALESCE(_c.relacl, acldefault('r', _c.relowner))) AS _acl
	WHERE _c.oid = objects.table_name
	AND _acl.grantee = objects.grantee
	AND _acl.privilege_type = 'SELECT'
	) AS on_base_table
	FROM (
	SELECT sv.table_name,
	c.oid::regclass::text AS object_name,
	c.relkind AS object_type,
	acl.privilege_type,
	acl.privilege_type AS base_privilege_type,
	acl.grantee,
	'h' AS history_or_portion
	FROM periods.system_versioning AS sv
	JOIN pg_class AS c ON c.oid IN (sv.history_table_name, sv.view_name)
	CROSS JOIN LATERAL aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner))) AS acl
	UNION ALL
	SELECT fpv.table_name,
	c.oid::regclass::text,
	c.relkind,
	acl.privilege_type,
	acl.privilege_type,
	acl.grantee,
	'p' AS history_or_portion
	FROM periods.for_portion_views AS fpv
	JOIN pg_class AS c ON c.oid = fpv.view_name
	CROSS JOIN LATERAL aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner))) AS acl
	UNION ALL
	SELECT sv.table_name,
	p.oid::regprocedure::text,
	'f',
	acl.privilege_type,
	'SELECT',
	acl.grantee,
	'h'
	FROM periods.system_versioning AS sv
	JOIN pg_proc AS p ON p.oid = ANY (ARRAY[sv.func_as_of, sv.func_between, sv.func_between_symmetric, sv.func_from_to]::regprocedure[])
	CROSS JOIN LATERAL aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) AS acl
	) AS objects
	ORDER BY object_name, object_type, privilege_type
	LOOP
	IF
	r.history_or_portion = 'h' AND
	(r.object_type, r.privilege_type) NOT IN (('r', 'SELECT'), ('v', 'SELECT'), ('f', 'EXECUTE'))
	THEN
	RAISE EXCEPTION 'cannot grant % to "%"; history objects are read-only',
	r.privilege_type, r.object_name;
	END IF;
	IF NOT r.on_base_table THEN
	RAISE EXCEPTION 'cannot grant % directly to "%"; grant % to "%" instead',
	r.privilege_type, r.object_name, r.base_privilege_type, r.table_name;
	END IF;
	END LOOP;

In the CROSS JOIN is always COALESCE(c.relacl, acldefault('r', c.relowner)).

When pg_restore creates the table test_history, the table only has the default acl belonging to the owner (in the query c.relacl is null, so COALESCE returns acldefault('r', c.relowner) which is {postgres=arwdDxt/postgres}).

The query returns:

table_name	object_name	object_type	privilege_type	base_privilege_type	grantee	history_or_portion	on_base_table
test	test__as_of(timestamp with time zone)	f	EXECUTE	SELECT	10	h	t
test	test__between(timestamp with time zone,timestamp with time zone)	f	EXECUTE	SELECT	10	h	t
test	test__between_symmetric(timestamp with time zone,timestamp with time zone)	f	EXECUTE	SELECT	10	h	t
test	test__from_to(timestamp with time zone,timestamp with time zone)	f	EXECUTE	SELECT	10	h	t
test	test_history	r	DELETE	DELETE	10	h	t
test	test_history	r	INSERT	INSERT	10	h	t
test	test_history	r	REFERENCES	REFERENCES	10	h	t
test	test_history	r	SELECT	SELECT	10	h	t
test	test_history	r	TRIGGER	TRIGGER	10	h	t
test	test_history	r	TRUNCATE	TRUNCATE	10	h	t
test	test_history	r	UPDATE	UPDATE	10	h	t
test	test_with_history	v	DELETE	DELETE	10	h	t
test	test_with_history	v	INSERT	INSERT	10	h	t
test	test_with_history	v	REFERENCES	REFERENCES	10	h	t
test	test_with_history	v	SELECT	SELECT	10	h	t
test	test_with_history	v	TRIGGER	TRIGGER	10	h	t
test	test_with_history	v	TRUNCATE	TRUNCATE	10	h	t
test	test_with_history	v	UPDATE	UPDATE	10	h	t

Because all of the default acl privilege types are returned,

periods/periods--1.2.sql

Lines 3447 to 3453 in 9bb3df9

	IF
	r.history_or_portion = 'h' AND
	(r.object_type, r.privilege_type) NOT IN (('r', 'SELECT'), ('v', 'SELECT'), ('f', 'EXECUTE'))
	THEN
	RAISE EXCEPTION 'cannot grant % to "%"; history objects are read-only',
	r.privilege_type, r.object_name;
	END IF;

raises the exception.

pg_restore can't GRANT the SELECT privilege, so c.relacl will always be null and the health_check() trigger will block every GRANT. Even for tables which don't have periods.

[jmealo]

Some of the lingering issues are making me reluctant to use this in prod. Is there a workaround/fork that fixes this? 🤔