Re: untrusted languages and non-global superusers?

От:

Tino Wildenhain <tino@wildenhain.de>

Дата:

4 августа 2005 г. в 04:15:49

Am Mittwoch, den 03.08.2005, 21:29 -0700 schrieb CSN:
> 
> --- Tom Lane  wrote:
> 
> > CSN  writes:
> > > I'm using plphpu and I'd like to allow the regular
> > > database user to use it, but since it's
> > "untrusted" it
> > > requires users to be superusers. If I have to do
> > this,
> > > I don't want the user to be a superuser for all
> > > databases. Is it possible to grant superuser
> > status to
> > > a user for a specific database?
> > 
> > Exactly how would you prevent him from converting
> > that into global
> > access?  Especially if you're going to give him use
> > of an untrusted
> > language?  He could easily rewrite any configuration
> > file you might
> > think is going to lock him out of your other
> > databases.
> 
> You lost me - how is any of that possible?

untrusted languages run in the context of the database
and have full access to the filesystem. In short, you
can do anything with them your database can do + a lot more.

> > 
> > > (The function uses mail(), so IIRC that
> > necessitates
> > > using plphpu).
> > 
> > Sending mail from a database function (or doing
> > anything else that
> > involves external side-effects) is generally A Bad
> > Idea, for reasons
> > that have been covered many times in the list
> > archives.
> 
> Why, exactly? In this situation I just set up a
> trigger that sends a welcome email to newly inserted
> members. Very convenient.

Why cant your application handle this?
Otoh, why dont you provide a function to send mail,
which takes some parameters and just let your users
use them? No need for everybody to write her own
mail function.

Re: untrusted languages and non-global superusers?

От:

Tino Wildenhain <tino@wildenhain.de>

Дата:

4 августа 2005 г. в 06:08:46

Am Donnerstag, den 04.08.2005, 01:35 -0700 schrieb CSN:
> 
> --- Tino Wildenhain  wrote:
> > > > > (The function uses mail(), so IIRC that
> > > > necessitates
> > > > > using plphpu).
> > > > 
> > > > Sending mail from a database function (or doing
> > > > anything else that
> > > > involves external side-effects) is generally A
> > Bad
> > > > Idea, for reasons
> > > > that have been covered many times in the list
> > > > archives.
> > > 
> > > Why, exactly? In this situation I just set up a
> > > trigger that sends a welcome email to newly
> > inserted
> > > members. Very convenient.
> > 
> > Why cant your application handle this?
> > Otoh, why dont you provide a function to send mail,
> > which takes some parameters and just let your users
> > use them? No need for everybody to write her own
> > mail function.
> 
> Convenience! I want the email sent whether the member
> is added via the web interface, directly in the
> database, from the command line, etc. I don't see any

Well, I also do such things with a small script which 
basically LISTENs to notify from database, spools the
mails and go sleep again.

> downside. It's only one user that'll be using this
> function.

If so - why bother if the user can/cannot use untrusted
language? You write the function one time as superuser
and ready you are.

This isnt different if many users are going to use this
function. But they dont have to write or change it to use it.
>  
-- 
Tino Wildenhain

Re: untrusted languages and non-global superusers?

От:

Tino Wildenhain <tino@wildenhain.de>

Дата:

4 августа 2005 г. в 06:44:27

Am Donnerstag, den 04.08.2005, 02:24 -0700 schrieb CSN:
> 
> --- Tino Wildenhain  wrote:
> > > Convenience! I want the email sent whether the
> > member
> > > is added via the web interface, directly in the
> > > database, from the command line, etc. I don't see
> > any
> > 
> > Well, I also do such things with a small script
> > which 
> > basically LISTENs to notify from database, spools
> > the
> > mails and go sleep again.
> 
> Could you elaborate how you do this? IIRC, there's an
> example in the docs using C, but I'd prefer using a
> scripting language.
> 

Yes, I'm using a scripting language too:

#!/usr/bin/python
import smtplib, select
from pyPgSQL import PgSQL

db=PgSQL.connect(host=..,user=..,password=..,database=..)

cur=db.cursor()
cur.execute("LISTEN table_where_you_notify")
db.commit()

while True:
    rlist,wlist,xlist=select.select([db.conn.socket],[],[],600)
    
    if db.conn.socket in rlist:
        db.conn.consumeInput()
        notifies=db.conn.notifies()
        if notifies:
            print "Backend with pid %s used notify" % n.be_pid
            ... here do something interesting, like selecting
            from the spool table and format your mails...

            db.commit()

 
-- 
Tino Wildenhain

Re: untrusted languages and non-global superusers?

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

3 августа 2005 г. в 23:56:30

CSN  writes:
> I'm using plphpu and I'd like to allow the regular
> database user to use it, but since it's "untrusted" it
> requires users to be superusers. If I have to do this,
> I don't want the user to be a superuser for all
> databases. Is it possible to grant superuser status to
> a user for a specific database?

Exactly how would you prevent him from converting that into global
access?  Especially if you're going to give him use of an untrusted
language?  He could easily rewrite any configuration file you might
think is going to lock him out of your other databases.

> (The function uses mail(), so IIRC that necessitates
> using plphpu).

Sending mail from a database function (or doing anything else that
involves external side-effects) is generally A Bad Idea, for reasons
that have been covered many times in the list archives.

			regards, tom lane

untrusted languages and non-global superusers?

От:

CSN <cool_screen_name90001@yahoo.com>

Дата:

3 августа 2005 г. в 23:45:51

I'm using plphpu and I'd like to allow the regular
database user to use it, but since it's "untrusted" it
requires users to be superusers. If I have to do this,
I don't want the user to be a superuser for all
databases. Is it possible to grant superuser status to
a user for a specific database? All I'm familiar with
is "alter user joe createuser." Or is there a better
way of handling this language and permission issue?
(The function uses mail(), so IIRC that necessitates
using plphpu).

Thanks,
CSN

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

Re: untrusted languages and non-global superusers?

От:

CSN <cool_screen_name90001@yahoo.com>

Дата:

4 августа 2005 г. в 01:30:14



--- Tom Lane  wrote:

> CSN  writes:
> > I'm using plphpu and I'd like to allow the regular
> > database user to use it, but since it's
> "untrusted" it
> > requires users to be superusers. If I have to do
> this,
> > I don't want the user to be a superuser for all
> > databases. Is it possible to grant superuser
> status to
> > a user for a specific database?
> 
> Exactly how would you prevent him from converting
> that into global
> access?  Especially if you're going to give him use
> of an untrusted
> language?  He could easily rewrite any configuration
> file you might
> think is going to lock him out of your other
> databases.

You lost me - how is any of that possible?

> 
> > (The function uses mail(), so IIRC that
> necessitates
> > using plphpu).
> 
> Sending mail from a database function (or doing
> anything else that
> involves external side-effects) is generally A Bad
> Idea, for reasons
> that have been covered many times in the list
> archives.

Why, exactly? In this situation I just set up a
trigger that sends a welcome email to newly inserted
members. Very convenient.

CSN


		
____________________________________________________
Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs

Re: untrusted languages and non-global superusers?

От:

CSN <cool_screen_name90001@yahoo.com>

Дата:

4 августа 2005 г. в 05:35:51

--- Tino Wildenhain  wrote:
> > > > (The function uses mail(), so IIRC that
> > > necessitates
> > > > using plphpu).
> > > 
> > > Sending mail from a database function (or doing
> > > anything else that
> > > involves external side-effects) is generally A
> Bad
> > > Idea, for reasons
> > > that have been covered many times in the list
> > > archives.
> > 
> > Why, exactly? In this situation I just set up a
> > trigger that sends a welcome email to newly
> inserted
> > members. Very convenient.
> 
> Why cant your application handle this?
> Otoh, why dont you provide a function to send mail,
> which takes some parameters and just let your users
> use them? No need for everybody to write her own
> mail function.

Convenience! I want the email sent whether the member
is added via the web interface, directly in the
database, from the command line, etc. I don't see any
downside. It's only one user that'll be using this
function.

CSN

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

Re: untrusted languages and non-global superusers?

От:

CSN <cool_screen_name90001@yahoo.com>

Дата:

4 августа 2005 г. в 06:24:44

--- Tino Wildenhain  wrote:
> > Convenience! I want the email sent whether the
> member
> > is added via the web interface, directly in the
> > database, from the command line, etc. I don't see
> any
> 
> Well, I also do such things with a small script
> which 
> basically LISTENs to notify from database, spools
> the
> mails and go sleep again.

Could you elaborate how you do this? IIRC, there's an
example in the docs using C, but I'd prefer using a
scripting language.

Thanks,
CSN

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

Re: untrusted languages and non-global superusers?

От:

Richard Huxton <dev@archonet.com>

Дата:

4 августа 2005 г. в 06:00:48

CSN wrote:
>>Why cant your application handle this?
>>Otoh, why dont you provide a function to send mail,
>>which takes some parameters and just let your users
>>use them? No need for everybody to write her own
>>mail function.
> 
> 
> Convenience! I want the email sent whether the member
> is added via the web interface, directly in the
> database, from the command line, etc. I don't see any
> downside. It's only one user that'll be using this
> function.

But it does mean that user is running code as the database user, so if 
you don't trust them with that, don't do it.

What I use with this is a "message_queue" table and a cron-job that runs 
once a minute (or every 5). It keeps the two processes separate (decide 
to send a message and assemble the message and send it) and also lets 
you get more complicated (in one example I need to build a report, 
produce a PDF and then attach it to the email I generate).

--
   Richard Huxton
   Archonet Ltd

Re: untrusted languages and non-global superusers?

От:

Chris Travers <chris@metatrontech.com>

Дата:

5 августа 2005 г. в 02:47:45

CSN wrote:

>
>
>Convenience! I want the email sent whether the member
>is added via the web interface, directly in the
>database, from the command line, etc. I don't see any
>downside. It's only one user that'll be using this
>function.
>
>  
>
Well, there are three issues with doing complex things like sending mail 
from your backend.

The first is security.  Personally, I think that this is a concern that 
can be mitigated if there are only a few people who can create untrusted 
language functions in an organization and if there is code review of 
them by others before they are added.

The second is not so easily mitigated.  This is latency.  If you are 
inserting a large number of users at once, then the trigger will fire 
when the new member is added and you will need to wait for it.  For 
performance reasons, it is often a good idea to separate this from the 
backend so that inserts can return quickly.

The third though is the biggie--- transactional control.  Lets say I 
have a trigger that sends the email.  Lets say the transaction aborts.  
The user still gets the email even though they were never added to the 
system.  So lets say you try to deal with this by deferring the trigger 
until the transaction completes.  It is still possible that (perhaps at 
some later date) another trigger will be deferred which will run after 
this one.  The result is fantom emails.

Personally, I would not mind using email-from-db functions under limited 
circumstances.  Some sort of security-related trigger might be used when 
something happens worth notifying the database admins immediately under 
certain circumstances (NEW USER CREATED.  Login: ....).

I am not going to say "never do it" but I think that in most cases, 
NOTIFY is more flexible and will perform better.  Here you can use a 
trigger to insert into a message queue and use NOTIFY to let a listening 
process know that some action needs to be taken.

Best Wishes,
Chris Travers
Metatron Technology Consulting