Untrusted Languages

Greenplum supports many different languages that can be executed in-database and in parallel. SQL and pgSQL are the most common but Perl, R, Python, and Java are also available.

Some of these languages provide means to interact with the operating system directly which can bypass security measures in the database. When these languages are created in the database, they are considered to be “Untrusted” where “Trusted” languages have certain commands removed so that the language can’t interact with the operating system.

Python is a very popular language and code can be written in Python using PL/PythonU in Greenplum. There isn’t a Trusted version of Python for PostgreSQL.

The “U” at the end indicates that it is in fact an Untrusted language. Again, this means a function written in this language can do nasty things to the operating system so be careful allowing this language to be used.

CREATE OR REPLACE FUNCTION fn_test() RETURNS SETOF text AS
$$
        import os
        return os.listdir('/home/gpadmin/')
$$
LANGUAGE plpythonu;

This simple function can return data about the operating system but nastier functions can be created to do all sorts of things as a privileged user (gpadmin).

PL/Container

PL/Container is a language extension in Greenplum that allows the database to interact with Docker containers to execute code. This means you can use Untrusted languages such as Python, and have that code execute in containers rather than on the host operating system. This isolation makes the use of Untrusted languages safe to be used.

CREATE OR REPLACE FUNCTION fn_test2() RETURNS SETOF text AS
$$
        # container: plc_python_shared
        import os
        return os.listdir('/')
$$
LANGUAGE plcontainer;

Running this function will list the directory contents but of the container. This isolation protects the database cluster from being exploited by the use of the popular Python language.

So have fun with Python in Greenplum knowing you can use PL/Container to protect the cluster from “Untrusted” code.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.