To: "Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk>
cc: "Oscar Serrano" <oserra@fondos.net>,
	"pgsql-interfaces@postgreSQL.org" <pgsql-interfaces@postgresql.org>
Subject: Re: a trigger that sends an email 
In-reply-to: <00ac01bf9fe2$1d4bdec0$760e01a3@oucs.ox.ac.uk> 
References: <NDBBKNFDELOHICEHIMGGMELGCCAA.oserra@fondos.net>
	<26948.955032666@sss.pgh.pa.us>
	<00ac01bf9fe2$1d4bdec0$760e01a3@oucs.ox.ac.uk>
Comments: In-reply-to "Moray McConnachie"
	<moray.mcconnachie@computing-services.oxford.ac.uk>
	message dated "Thu, 06 Apr 2000 17:06:49 +0100"
Date: Thu, 06 Apr 2000 13:52:44 -0400
Message-ID: <27815.955043564@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:
>> programming languages we offer are "trusted", which means you can't do
>> anything that would affect files or programs outside the database.
>> Sending email is right out.

> Could one do a rule that used a dummy function, where the function called
> the Perl routine which generates the email?

If it's in plperl then it's going to be running in a "safe" interpreter,
which should mean that you won't be able to do anything to send mail.

> CREATE RULE emailer AS ON INSERT TO new_accounts
>     DO SELECT new_account_email_function(NEW.emailaddress);

> or something like that? Or is this subject to the same restrictions?

Wouldn't be much of a security system if it were that easy to get
around, would it?

The problem here is that anything done by the backend is done with the
full permissions of user postgres (or whatever the backend is running
as).  So we don't want ordinary users to have access to programming
languages that will allow them to muck around with postgres' files.
The only way to escape the restrictions is to write a C-language
extension.  That has to be installed by the database admin, and we
expect him to exercise some caution in what he will install...

			regards, tom lane