Pivotal Knowledge Base


Permission Denied when Creating Temporary Tables in Pivotal Greenplum Database


Pivotal Greenplum Database (GPDB 4.3.x)


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

Error Messages:

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 


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


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}



Powered by Zendesk