untrusted languages and non-global superusers?

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