Re: Question about stored procedures

On Thu, Oct 13, 2005 at 04:20:39AM -0700,
Josephine E. de Castro <jedecastro23(at)yahoo(dot)com> wrote
a message of 49 lines which said:

> Is there no 'trusted' way of doing this?

By definition, certainly not. A "trusted" procedure can be installed
by an ordinary user so it MUST NOT play outside of the sandbox.

> How about creating a trigger using C?

Yes, C extensions can do anything.

> Or should i stick with something like PL/pgSQL and look for its
> 'untrusted' flavors?

I am afraid there is no "untrusted" PL/pgSQL. (And no file
manipulation primitives in PL/pgSQL.)

Note that a common trick, when you want to do X and you cannot do it
directly from PostgreSQL (or are unwilling to force the sysadmin to
install stuff like plWhatever - for instance, I was never able to make
plPython run on my NetBSD machines), is to put data in a table and to
have an auxiliary daemon which connects to the database and read in
the table what it must do.

For a more complete example, let's assume that your application must
delete files. You create a table Files_to_delete (name TEXT, deleted
BOOLEAN default False), your trigger writes the file names, and you
write a program (running with any UID you choose) which SELECT name
FROM WHERE deleted = False and then performs the rm (and UPDATE SET
deleted = True).