'ERROR: permission denied for schema' as gpadmin

Products

• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

When running any command that impacts a unique constraint or table index (such as an ALTER TABLE...ADD CONSTRAINT or CREATE UNIQUE INDEX command) - the process fails and returns the error 'ERROR: permission denied for schema <schema name>'.

This can occur in earlier versions of Greenplum that use Postgres 9.4. An index/constraint must have the same owner as its table. If a table owner has lost their permissions on the table's schema, it is unable to take ownership of any new index/constraints - resulting in permission errors. This can occur because a user has moved somewhere else and had their role/schema permissions revoked.

For example:
As GPADMIN

gpadmin=# create schema testschema;
CREATE SCHEMA
gpadmin=# grant all on schema testschema to testuser;
GRANT

As testuser:

gpadmin=> create table testschema.jd (id int) distributed by (id);
CREATE TABLE

As GPADMIN:

gpadmin=# revoke all on schema testschema from testuser;
REVOKE
gpadmin=# alter table testschema.jd add constraint jd_pkey primary key (id);
ERROR:  permission denied for schema testschema  (seg2 10.225.28.77:30154 pid=7751)
gpadmin=#  CREATE UNIQUE INDEX idx_jd_pkey ON testschema.jd using btree (id);
ERROR:  permission denied for schema testschema  (seg1 10.225.28.76:30155 pid=28442)

Environment

Product Version: 6.23

Resolution

The issue is resolved in Greenplum versions 6.23.1 and above. Upgrading to a newer version will resolve the error.

Release 6.23.1
Release Date: 2023-02-09
VMware Greenplum 6.23.1 is a maintenance release that resolves several issues.

14705
Resolves an issue where Greenplum Database returned the error permission denied for schema <name> during a CREATE INDEX operation when the owner of the table was not the current user.

As an alternative workaround, providing 'grant all on schema' permissions back to the original owner will prevent the errors from occurring.

grant all on schema testschema to testuser;