"CREATE SCHEMA ... AUTHORIZATION" Error- "Permission denied for database XXX (seg...)"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

When attempting to create a schema with AUTHORIZATION, it fails with error messages as shown below.

Error Message:

gpadmin=# create schema event authorization user1;
ERROR:  permission denied for database gpadmin  (seg2 sdw2:40000 pid=23058)

 

Environment

Cause

Since the query was running with superuser gpadmin, the schema should be created successfully with the owner being set to user1. However, it failed at segment which should not happen. 

This is due to a software defect which is being traced by an internal JIRA.

 

Resolution

The final solution would be to upgrade the Greenplum system when the release including the fix to this issue is ready. 

Before the fix is ready, the workaround is to create a schema with the superuser first and then change the owner to the desired one. Refer to the following example:

gpadmin=# alter schema event owner to user1;
ALTER SCHEMA
gpadmin=# \dn event
List of schemas
 Name | Owner 
-------+-------
 event | user1