ERROR: cannot copy ACL from parent, because there is an existing ACL

Products

• VMware Tanzu Greenplum

Issue/Introduction

In Greenplum 6.x, we have new ALTER DEFAULT PRIVILEGES.
If the user adds a default privilege, it will generate an error on creating a partition table.

gpadmin=# alter default privileges for user gpadmin in schema public grant select on tables to evi_bi;
ALTER DEFAULT PRIVILEGES
gpadmin=#
gpadmin=#
gpadmin=# CREATE TABLE public.test_partition( dt date ) PARTITION BY RANGE (dt) ( START (date '2019-12-01') INCLUSIVE END (date '2020-02-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dt' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "test_partition_1_prt_1" for table "test_partition"
NOTICE:  CREATE TABLE will create partition "test_partition_1_prt_2" for table "test_partition"
ERROR:  cannot copy ACL from parent, because there is an existing ACL (aclchk.c:2276)

Environment

Product Version: 6.4

Resolution

Workaround:

As per the postgresql documentation (https://www.postgresql.org/docs/9.4/sql-alterdefaultprivileges.html), in order to reverse the changes, you can revoke granted privilege.

Example:

gpadmin=# ALTER DEFAULT PRIVILEGES FOR ROLE gpadmin IN SCHEMA public revoke SELECT ON TABLES from evi_bi;
ALTER DEFAULT PRIVILEGES
gpadmin=#
gpadmin=#
gpadmin=# CREATE TABLE public.test_partition( dt date ) PARTITION BY RANGE (dt) ( START (date '2019-12-01') INCLUSIVE END (date '2020-02-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dt' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "test_partition_1_prt_1" for table "test_partition"
NOTICE:  CREATE TABLE will create partition "test_partition_1_prt_2" for table "test_partition"
CREATE TABLE

Permanent fix:

Upgrade to 6.7 or higher. Even if you do have multiple privileges problems you don't have to revoke them, the upgrade should help to solve a problem with creating partition table.