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.

score 7 · Accepted Answer · answered Mar 22 '13 at 13:05

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.

Cannot `create function` in plpython3u, permission denied

1 Answers1