Thread: System commands
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
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: > 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
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
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
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 >
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: > 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 >