Permission denied when creating temporary tables in Pivotal Greenplum Database

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

When attempting to create a temporary table or during some product installations (such as Madlib), an error similar to the error messages shown below is generated:

gpadmin=> create TEMP table test_temporary (column_one int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'column_one' 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. ERROR: permission denied for schema pg_temp_XXXX 

— or — 

plpy.SPIError: permission denied to create temporary tables in database

Environment

Cause

The error can be generated when the User or Role has not been granted the TEMPORARY privilege on the database.

select datname,datacl from pg_database where datname = 'database_name';
 datname       |              datacl
---------------+----------------------------------
 database_name | {=c/gpadmin,gpadmin=CTc/gpadmin}

Access privileges are interpreted as:

 
rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

Resolution

In order to allow temporary tables to be generated, the role must be granted the TEMPORARY privilege.

GRANT TEMPORARY on DATABASE <database_name> to <public or role_name>;
​
select datname,datacl from pg_database where datname = 'database_name';
 datname       |              datacl
---------------+----------------------------------
 database_name | {=Tc/gpadmin,gpadmin=CTc/gpadmin}