Pivotal Knowledge Base

Follow

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

Problem

As non-superuser trying to create a PL/R function will result in error message "ERROR: permission denied for language plr"

Cause

The issue is 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 this is a trusted language, which means that it is believed not to grant access to anything outside the normal SQL execution environment.

Only superusers can create functions in untrusted languages.

Solution

You can use the below workaround to create the function as non-superuser

-- Create a super-user role

flightdata=# create role dba superuser;
CREATE ROLE
flightdata=# grant dba to aaa;
GRANT ROLE

-- As non superuser set the above mentioned role and create the function and once done reset it

[gpadmin@mdw ~]$ psql -U aaa
Password for user aaa:
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=>

Comments

Powered by Zendesk