It’s pretty simple, but it involves building software from source and the lack of any explicit documentation for a typical RHEL/CentOS 6 system using the PostgreSQL RPMs was something of a pain in the butt, so here’s the process:
First, make sure you take note of the warning at the onset of the documentation regarding this software. Basically, PL/R is an untrusted language extension in PostgreSQL, which means that functions written in the language have the ability to do anything the postgres UNIX account can do in the underlying operating system (RHEL/CentOS 6, in this case). That means, a user could write a function in this language which allows him to read absolutely any data from the PostgreSQL database cluster, since the postgres user has full control over all of it. In fact, any file in any file system which is accessible to the postgres user is accessible to functions written in this language extension.
It’s a challenge to keep one’s system secure, and you don’t want to totally nuke your security by missing that one. If you have to install an untrusted language extension such as PL/R, either ensure that you fully trust the user who will be authoring said extensions, for he will need to operate in a PostgreSQL superuser context to do so.
The proper way to secure this is up for debate, but a good way to do it is to modify the login role for the user to whom the responsibility of authoring functions will be assigned so that the login role has the NOINHERIT property, preventing the role from assuming all of its role-granted privileges upon login. Then, you can create a simple superuser role and grant it to the login role. Something like this:
postgres=# CREATE ROLE user WITH LOGIN NOINHERIT; CREATE ROLE postgres=# CREATE ROLE super WITH SUPERUSER; CREATE ROLE postgres=# GRANT super TO user; GRANT
Now, when the ‘user’ role is used to log into the database, the role won’t automatically be a superuser (thanks to NOINHERIT). Instead, the person using the role will have to manually perform a `SET ROLE super;` command to assume the superuser privileges and go about authoring functions. This prevents users from accidentally operating out of superuser mode all the time (which you definitely don’t want) and is the best way of constraining privileges in a situation when the role must be granted the ability to assume superuser privileges. It’d be nice if there were some way to prevent the user from doing anything in that role other than author functions, but there’s just not a way to do such a thing, as far as I am aware.
Ok, so on to making and installing the extension:
#Ensure postgresql94-devel (or the devel package for whatever version of postgresql you're using) is installed $ wget http://www.joeconway.com/plr/plr-188.8.131.52.tar.gz $ export PATH=$PATH:/usr/pgsql-9.4/bin/pg_config $ tar xf plr-184.108.40.206.tar.gz $ cd plr $ USE_PGXS=1 make $ sudo USE_PGXS=1 make install
If you’re interested in what, exactly, the installation is doing, it’s simple and straightforward:
$ USE_PGXS=1 make -n install /bin/mkdir -p '/usr/pgsql-9.4/lib' /bin/mkdir -p '/usr/pgsql-9.4/share/extension' /bin/mkdir -p '/usr/pgsql-9.4/share/extension' /bin/mkdir -p '/usr/pgsql-9.4/doc/extension' /usr/bin/install -c -m 755 plr.so '/usr/pgsql-9.4/lib/plr.so' /usr/bin/install -c -m 644 plr.control '/usr/pgsql-9.4/share/extension/' /usr/bin/install -c -m 644 plr--220.127.116.11.sql plr--unpackaged--18.104.22.168.sql plr.sql '/usr/pgsql-9.4/share/extension/' /usr/bin/install -c -m 644 README.plr '/usr/pgsql-9.4/doc/extension/'
neurotic a responsible sysadmin, I actually executed the `USE_PGXS=1 make -n install` command and then manually performed the `/usr/bin/install` operations each with sudo individually (managing a bunch of systems with unpackaged software installations sucks), so if my sudo command above doesn’t work out, let me know, and know that you can execute that list of commands there individually (ignoring the mkdir commands, as the directories should already exist) as an alternative.
Finally, to load the extension into a database, you need to act as a PostgreSQL superuser and perform the CREATE EXTENSION plr; command:
postgres=# \c test_database You are now connected to database "test_database" as user "postgres". test_database=# CREATE EXTENSION plr; CREATE EXTENSION test_database=# \q