Inconsistent behavior of inherited privileges when table moved to another schema

Sergey_Cherepan_1

Hi,

There is inconsistent behaviour of inherited privileges when table is moved to another schema.

When table have been created in schema with default inherited privileges, and moved to other schema with inherited privileges, table inherit new schema.

When table have been created in schema without default inherited privileges, and moved to other schema with inherited privileges, table do not inherit new schema privileges.

Below is an reproducing example.

This statement is OK:

alter table di1.ti1 set schema di2;

Table di2.ti1 changed privilege from SELECT to UPDATE, following changed default privilege between schemas di1 and di2.

Problematic statement is

alter table de1.te1 set schema di2;

It does not result in table di2.te1 having inherited privileges from schema di2.
At same time, table di2.te1 is shown in inherited_objects. That makes me think it is actually a bug.

I would totally expect that table moved to schema with default privileges will inherit it in any case.

Problem reproduced in 12.0.4-18.

Thank you
Sergey

drop schema if exists di1 cascade;
DROP SCHEMA
drop schema if exists di2 cascade;
DROP SCHEMA
drop schema if exists de1 cascade;
DROP SCHEMA
drop schema if exists de2 cascade;
DROP SCHEMA
create schema di1 default include privileges;
CREATE SCHEMA
create schema di2 default include privileges;
CREATE SCHEMA
grant select on schema di1 to public;
GRANT PRIVILEGE
grant update on schema di2 to public;
GRANT PRIVILEGE
create schema de1;
CREATE SCHEMA
create schema de2;
CREATE SCHEMA
create table di1.ti1 (c1 int);
vsql:inherit_bug.sql:13: WARNING 6978: Table "ti1" will include privileges from schema "di1"
CREATE TABLE
create table di1.ti2 (c1 int);
vsql:inherit_bug.sql:14: WARNING 6978: Table "ti2" will include privileges from schema "di1"
CREATE TABLE
create table de1.te1 (c1 int);
CREATE TABLE
create table de1.te2 (c1 int);
CREATE TABLE
select * from inherited_privileges where principal = 'public' and object_schema in ('di1', 'di2', 'de1', 'de2') and object_name in ('ti1', 'ti2', 'te1', 'te2') order by object_schema, object_name;
object_id | schema_id | object_schema | object_name | object_type | privileges_description | principal | principal_id | grantor | grantor_id | grant_id
-------------------+-------------------+---------------+-------------+-------------+------------------------+-----------+-------------------+---------+-------------------+-------------------
45035996288767130 | 45035996288767114 | di1 | ti1 | Table | SELECT | public | 45035996273704964 | dbadmin | 45035996273704962 | 45035996288767120
45035996288767132 | 45035996288767114 | di1 | ti2 | Table | SELECT | public | 45035996273704964 | dbadmin | 45035996273704962 | 45035996288767120
(2 rows)

select * from inheriting_objects where object_schema in ('di1', 'di2', 'de1', 'de2') and object_name in ('ti1', 'ti2', 'te1', 'te2') order by object_schema, object_name;
object_id | schema_id | object_schema | object_name | object_type
-------------------+-------------------+---------------+-------------+-------------
45035996288767130 | 45035996288767114 | di1 | ti1 | table
45035996288767132 | 45035996288767114 | di1 | ti2 | table
(2 rows)

alter table di1.ti1 set schema di2;
vsql:inherit_bug.sql:21: NOTICE 7199: Table "ti1" was set to include privileges from its schema; table accessibility may have changed
ALTER TABLE
alter table di1.ti2 set schema de2;
vsql:inherit_bug.sql:22: NOTICE 7199: Table "ti2" was set to include privileges from its schema; table accessibility may have changed
ALTER TABLE
alter table de1.te1 set schema di2;
ALTER TABLE
alter table de1.te2 set schema de2;
ALTER TABLE
select * from inherited_privileges where principal = 'public' and object_schema in ('di1', 'di2', 'de1', 'de2') and object_name in ('ti1', 'ti2', 'te1', 'te2') order by object_schema, object_name;
object_id | schema_id | object_schema | object_name | object_type | privileges_description | principal | principal_id | grantor | grantor_id | grant_id
-------------------+-------------------+---------------+-------------+-------------+------------------------+-----------+-------------------+---------+-------------------+-------------------
45035996288767130 | 45035996288767116 | di2 | ti1 | Table | UPDATE | public | 45035996273704964 | dbadmin | 45035996273704962 | 45035996288767124
(1 row)

select * from inheriting_objects where object_schema in ('di1', 'di2', 'de1', 'de2') and object_name in ('ti1', 'ti2', 'te1', 'te2') order by object_schema, object_name;
object_id | schema_id | object_schema | object_name | object_type
-------------------+-------------------+---------------+-------------+-------------
45035996288767132 | 45035996288767128 | de2 | ti2 | table
45035996288767130 | 45035996288767116 | di2 | ti1 | table

SruthiA

@Sergey_Cherepan_1 Could you please create a support case and share details?

Sergey_Cherepan_1

@SruthiA
Opened case 02819881
Thank you
Sergey