PostgreSQL: Running Python stored procedures as a normal user

Question

Hi,

I've installed PL/Python on my postgresql server under postgres privilleges:

netherlands=# CREATE PROCEDURAL LANGUAGE plpythonu;
CREATE LANGUAGE      

Now I need to grant permissions so I can use it as a normal user:

netherlands=# GRANT ALL ON LANGUAGE plpythonu TO adam;
ERROR:  language "plpythonu" is not trusted
HINT:  Only superusers can use untrusted languages.

I know that python isn't a 'trusted' language, but I'm willing to take my chances here. Any way to convince PostgreSQL to let me run Python stored procedures as a normal user?

Adam

Answer 1

Hi Adam,

Unfortunately, i don't believe it is possible to run untrusted interpreters unless your postgres account has superuser access. If you are the database server administrator, createuser will ask you if the new account should be superuser.

The 'untrusted' flag does not mean that the runtime is unstable or unreliable, simply that its security model does not fit very well as a stored-procedure interpreter. This could result in privilege escalation from your stored procedures, or potentially disastrous security bugs.

If you are unable to run as the postgres user or create a superuser account, I'm afraid you will have to skip pl/python, and suggest you check out pl/pgsql instead. http://www.postgresql.org/docs/8.3/interactive/plpgsql.html

Answer 2

GRANT [USAGE] on languages means the user in question can create functions in that language. Once created you have to use GRANT EXECUTE to allow other users to use them.

postgres@dev:~$ psql
Welcome to psql 8.3.9, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# \c plpythonu_test
You are now connected to database "plpythonu_test".
plpythonu_test=# create language plpythonu;
CREATE LANGUAGE
plpythonu_test=# CREATE FUNCTION pymax (a integer, b integer)
plpythonu_test-#   RETURNS integer
plpythonu_test-# AS $$
plpythonu_test$#   if a > b:
plpythonu_test$#     return a
plpythonu_test$#   return b
plpythonu_test$# $$ LANGUAGE plpythonu;
CREATE FUNCTION
plpythonu_test=# grant execute on function pymax (a integer, b integer) to plpythonu_test;
GRANT
plpythonu_test=#



C:\Users\milen>psql.exe -U plpythonu_test -h ...
Password for user plpythonu_test:
psql (8.4.4, server 8.3.9)
WARNING: psql version 8.4, server version 8.3.
         Some psql features might not work.
WARNING: Console code page (866) differs from Windows code page (1251)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

plpythonu_test=> select pymax(1,2);
 pymax
-------
     2
(1 row)


plpythonu_test=>