Creating a PL/R function as non-superuser fails with "ERROR: permission denied for language plr"

Products

• VMware Tanzu Greenplum

Issue/Introduction

A non-superuser trying to create a PL/R function will produce the following error message: "ERROR: permission denied for language plr".

Cause

The issue arises because PL/R is not a trusted language as indicated by the column lanpltrusted.

flightdata=> select * from pg_language where lanname='plr';
 lanname  | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
----------+---------+--------------+---------------+--------------+--------
 plr      | t       | f            |         57662 |            0 |

The column lanpltrusted is true if the column lanname is a trusted language. This means that the lanname is believed not to grant access to anything outside the normal SQL execution environment.

Only superusers can create functions in untrusted languages.

Resolution

Apply the following workaround to create the function as non-superuser.

1. Create a super-user role:
   
   flightdata=# create role dba superuser; CREATE ROLE flightdata=# grant dba to ###; GRANT ROLE
   
   
2. Create the function and then reset the function:

[gpadmin@### ~]$ psql -U aaa
Password for user ###:
psql (8.2.15)
Type "help" for help.
flightdata=> CREATE OR REPLACE FUNCTION text01.test01()
flightdata->   RETURNS character[] AS
flightdata-> $BODY$
flightdata$> version : HAWQ 1.2.1.0
flightdata$> library(cairoDevice)
flightdata$> library(RGtk2)
flightdata$> pixmap <- gdkPixmapNew(w=500, h=500, depth=24)
flightdata$> gdkDrawableSetColormap(pixmap, gdkColormapGetSystem())
flightdata$> asCairoDevice(pixmap)
flightdata$> plot(c(1:10))
flightdata$> plot_pixbuf <- gdkPixbufGetFromDrawable(NULL, pixmap,
flightdata$>
flightdata$> pixmap$getColormap(), 0, 0, 0, 0, 500, 500)
flightdata$> buffer <- gdkPixbufSaveToBufferv(plot_pixbuf, "png", character(0),
flightdata$>
flightdata$> return(0)
flightdata$> $BODY$
flightdata->   LANGUAGE plr VOLATILE;
ERROR:  permission denied for language plr

flightdata=> set role=dba
flightdata-> ;
SET

flightdata=# CREATE OR REPLACE FUNCTION text01.test01()
flightdata-#   RETURNS character[] AS
flightdata-# $BODY$
flightdata$# version : HAWQ 1.2.1.0
flightdata$# library(cairoDevice)
flightdata$# library(RGtk2)
flightdata$# pixmap <- gdkPixmapNew(w=500, h=500, depth=24)
flightdata$# gdkDrawableSetColormap(pixmap, gdkColormapGetSystem())
flightdata$# asCairoDevice(pixmap)
flightdata$# plot(c(1:10))
flightdata$# plot_pixbuf <- gdkPixbufGetFromDrawable(NULL, pixmap,
flightdata$#
flightdata$# pixmap$getColormap(), 0, 0, 0, 0, 500, 500)
flightdata$# buffer <- gdkPixbufSaveToBufferv(plot_pixbuf, "png", character(0),
flightdata$#
flightdata$# return(0)
flightdata$# $BODY$
flightdata-#   LANGUAGE plr VOLATILE;
CREATE FUNCTION

flightdata=# reset role;
RESET
flightdata=>