Thread: untrusted languages and non-global superusers?
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
CSN <cool_screen_name90001@yahoo.com> 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
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > CSN <cool_screen_name90001@yahoo.com> 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
Am Mittwoch, den 03.08.2005, 21:29 -0700 schrieb CSN: > > --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > CSN <cool_screen_name90001@yahoo.com> 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.
--- Tino Wildenhain <tino@wildenhain.de> 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
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
Am Donnerstag, den 04.08.2005, 01:35 -0700 schrieb CSN: > > --- Tino Wildenhain <tino@wildenhain.de> 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 <tino@wildenhain.de>
--- Tino Wildenhain <tino@wildenhain.de> 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
Am Donnerstag, den 04.08.2005, 02:24 -0700 schrieb CSN:
>
> --- Tino Wildenhain <tino@wildenhain.de> 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 <tino@wildenhain.de>
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