System commands

using \!

From: Syd Alsobrook <syd@ittagteam.com>
To: pgsql-general@postgresql.org
Subject: [GENERAL] System commands
Date: Thu, 31 Jan 2002 16:21:25 -0500

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

Thanks,
Syd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

That's from psql, what about from a trigger or stored procedure.

Syd

omid omoomi wrote:

Syd Alsobrook wrote:

That's from psql, what about from a trigger or stored procedure.

Syd

omid omoomi wrote:

using \!

Don't know what you need that for, but are you aware of all
the side effects and implications such a functionality would
have? The command you'd be calling will be executed by the
database system owner, so it'd have access to all files of
the entire database instance without any access restrictions.

Also, the command will be called regardless and without
further notice of an eventually later happening ROLLBACK.
Does your filesystem have a ROLLBACK for unlink(2)?

If that is really what you want, you can write a C function
and use system(3) in there. But remember that this opens the
flood gate for everything that is owned by the unix user
running that postmaster, not only the databases you declare
the function in.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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@mascari.com

I want to be able to trigger an email to be sent. I understand the
security concerns, I have seen this done in the past with oracle. We had
our database doing the backups from a sql script. Would it have to be in
C or could I use another language?

Syd

Jan Wieck wrote:

pgmail won't do what you want? If nothing else, it might give you an idea
on how to roll your own...

http://sourceforge.net/projects/pgmail/

On Thu, 31 Jan 2002 19:23:35 -0500
Syd Alsobrook <syd@ittagteam.com> wrote:

I want to be able to trigger an email to be sent. I understand the
security concerns, I have seen this done in the past with oracle. We had
our database doing the backups from a sql script. Would it have to be in
C or could I use another language?

Syd

Jan Wieck wrote:

Syd Alsobrook wrote:

That's from psql, what about from a trigger or stored procedure.

Syd

omid omoomi wrote:

using \!

Don't know what you need that for, but are you aware of all
the side effects and implications such a functionality would
have? The command you'd be calling will be executed by the
database system owner, so it'd have access to all files of
the entire database instance without any access restrictions.

Also, the command will be called regardless and without
further notice of an eventually later happening ROLLBACK.
Does your filesystem have a ROLLBACK for unlink(2)?

If that is really what you want, you can write a C function
and use system(3) in there. But remember that this opens the
flood gate for everything that is owned by the unix user
running that postmaster, not only the databases you declare
the function in.

Jan

--

#======================================================================#>
# It's easier to get forgiveness for being wrong than for being right. #>
# Let's break this rule - forgive me. #>
#================================================== JanWieck@Yahoo.com #>

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Didn't know about that one. Thanks.

Syd

Brett Schwarz wrote: