Cannot `create function` in plpython3u, permission denied

Question

As postgres user, I have create extension plpython3u; in my database

then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true

but when my db_user tries

 create function check_data_valid(id bigint)
 returns boolean
 as $$
 -- ...
 return true
 $$ language plpython3u;

I got the error: permission denied for the language plpython3u

So, with my postgres user then I have tried: grant usage on plpython3u to db_user and grant execute on plpython3u to db_user but both returns the error:

relation python doesn't exist

maybe it's because it's an extension... however, I don't what to do so as to create my stored procedure.

• Craig Ringer: If you're letting them use plpython3u you might as well just make them a superuser anyway; they can fairly easily make themselves a superuser from within a Python script. (This isn't a good reason to make them a superuser, it's a good reason not to use plpython3u for user-written procedures).
• Stephane Rolland: @CraigRinger you do think it's the solution ?
• Stephane Rolland: @CraigRinger i am the user and the admin... but I want to keep both role seperated.
• Milen A. Radev: BTW the GRANT you're looking for is GRANT USAGE ON LANGUAGE plpython3u TO db_user.
• Stephane Rolland: that's right... it works better !

Answer 1

First don't make plpython3u into a trusted language. This is bad. You are giving access to all kinds of things on the database, the filesystem, and the like in ways that could be horribly abused. Please reconsider.

Now from your comments you want to keep the admin permission separate from the normal user permission. The way to do this is to make sure the superuser is NOINHERIT and the user is granted the role. You can:

 CREATE ROLE mydba WITH SUPERUSER NOINHERIT;
 GRANT mydba TO myuser;

Then logged in as myuser then you can:

 SET ROLE mydba;

And you get superuser privileges. You can use this to then to temporarily escalate your permissions in a way similar to sudo on *nix systems.

• Stephane Rolland: do you mean: in real life, the use of PL/Python and PL/C is discouraged ? There are no other trusted languages than PL/pgSQL ?
• Chris Travers: I don't mean that at all. It does mean that you need to very carefully control who can create these procedures and what they do. Also there is a trusted version of pl/perl. You may find this interesting: wiki.postgresql.org/wiki/PL_Matrix
• Stephane Rolland: okay, myuser has to use carefully the set role mydba. Does it goes off after psql connection ? or should I manually set role none when done with programming the stored procedure in untrusted language ?
• Chris Travers: RESET ROLE; resets it
• Craig Ringer: @ChrisTravers Or in this case even better, myplpythonuser that you GRANT USAGE to without having to make them otherwise a superuser.