Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
1  
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). – Craig Ringer Mar 22 at 12:25
@CraigRinger you do think it's the solution ? – Stephane Rolland Mar 22 at 12:31
@CraigRinger i am the user and the admin... but I want to keep both role seperated. – Stephane Rolland Mar 22 at 12:34
BTW the GRANT you're looking for is GRANT USAGE ON LANGUAGE plpython3u TO db_user. – Milen A. Radev Mar 22 at 12:54
that's right... it works better ! – Stephane Rolland Mar 22 at 13:05

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
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 ? – Stephane Rolland Mar 22 at 13:11
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 – Chris Travers Mar 22 at 13:25
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 ? – Stephane Rolland Mar 22 at 13:35
1  
RESET ROLE; resets it – Chris Travers Mar 22 at 13:56
1  
@ChrisTravers Or in this case even better, myplpythonuser that you GRANT USAGE to without having to make them otherwise a superuser. – Craig Ringer Mar 22 at 14:06
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.