PostgreSQL: running Python stored procedures as a regular user

Question

I installed PL / Python on my postgresql server in postgres privilleges:

 netherlands=# CREATE PROCEDURAL LANGUAGE plpythonu; CREATE LANGUAGE 

Now I need to grant permissions so that I can use it as a regular 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 is not a "reliable" language, but I am ready to take my chances here. Any way to convince PostgreSQL to let me run Python stored procedures as a regular user?

Answer 1

 UPDATE pg_language SET lanpltrusted = true WHERE lanname = 'plpythonu'; 

Answer 2

Unfortunately, I do not believe that you can run untrusted interpreters if your postgres account does not have superuser access. If you are the database server administrator, createuser will ask you if the new account should be superuser.

The “unreliable” flag does not mean that runtime is unstable or unreliable, just that its security model is not well suited for the stored procedure interpreter. This can lead to escalation of privileges from your stored procedures or, possibly, catastrophic security errors.

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

Answer 3

GRANT [USE] in languages ​​means that the user in question can create functions in that language. Once created, you must use GRANT EXECUTE so other users can 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=>