Re: System commands

Syd Alsobrook wrote:
>
> So tell me, how does one exec system commands (ie. scripts) from inside
> the database, and is it possible to pass the command arguments.

Use an untrusted procedural language, such as 'C' along with system().
Since the backend is running as user postgres, however, creating such
stored procedures requires PostgreSQL super-user privileges. Example:

Here's some C:

int getfile(text *arg1) {

char filename[_POSIX_PATH_MAX];
int length;

length = VARSIZE(arg1) - VARHDRSZ;
if ((length <= 0) || (length >= _POSIX_PATH_MAX)) return 0;
strncpy(filename, VARDATA(arg1), length);
filename[length] = 0;
if (access(filename, F_OK) != 0) return 0;
return 1;

}

Here's the SQL to create the function:

CREATE FUNCTION getfile(text) RETURNS
int4 AS '/opt/mascari/lib/dbfunctions.so' LANGUAGE 'c';

Here's a SELECT which calls it. This SELECT would return 1:

SELECT getfile("/etc/motd");

You should check the docs since this is an old-style function. Newer
ones use a macro declaration which allows the code to test for the
prescence of NULLs, but you get the idea. Obviously you can call scripts
via system(). But be careful to note that transactions can be rolled
back. One might not want to call a script which notifies a client of a
stock trade via email only to have their transaction roll back in the
database at a later moment in time...

Hope that helps,

Mike Mascari
mascarm(at)mascari(dot)com