Cannot drop role or revoke permissions on schema in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

You cannot drop a role because it has access to a schema:

# DROP ROLE role01;
ERROR: role "role01" cannot be dropped because some objects depend on it
DETAIL: access to schema schema01

You cannot revoke the permissions on the schema from the role:

# REVOKE ALL ON SCHEMA schema01 FROM role01;
NOTICE:  no privileges could be revoked from role role01 on object schema01
REVOKE

However the "\dn+" on the schema shows that the role does have access permissions on the schema:

\dn+ schema01 
  Name    |   Owner   |         Access privileges         |  Description 
----------+-----------+-----------------------------------+----------------------- 
 schema01 | sch_owner | sch_owner=U*C/sch_owner           |
                      : role01=U/role02

Environment

Product Version: 4.3

Resolution

The role that granted the permission needs to revoke the permission.

For example, if the schema schema01 has the permissions:

\dn+ schema01
  Name    |   Owner   |         Access privileges         |  Description

----------+-----------+-----------------------------------+-----------------------
 schema01 | sch_owner | sch_owner=U*C/sch_owner           | 
                      : role01=U/role02

Usage has been granted to role01 by role02.

SET ROLE role02;
REVOKE ALL ON SCHEMA schema01 FROM role01;
SET ROLE gpadmin;
DROP ROLE role01;