Thread: Runnning operating system commands from an SPL

Atif Jung <atifjung@gmail.com> wrote:

> How do I run an operating system command from a stored procedure? In INFORMIX I
> would use the SYSTEM command, can't see to find the equivalent for POSTGRES.

You can use a un-trusted programming language, for instance pl/perlu or
plsh.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Atif Jung <atifjung@gmail.com> wrote:
>> How do I run an operating system command from a stored procedure? In INFORMIX I
>> would use the SYSTEM command, can't see to find the equivalent for POSTGRES.

> You can use a un-trusted programming language, for instance pl/perlu or
> plsh.

Keep in mind that more often than not the answer is "you shouldn't do
that".  Commands run in that way will execute as the postgres user,
not as your client user.

            regards, tom lane

Atif Jung <atifjung@gmail.com> wrote:

> Sorry I don't understand. I am porting over some stored procedures from
> INFORMIX to POSTGRES. Some seem to port over OK, but the first problem I am
> encountering is the lack of SYSTEM command in POSTGRES.

That's right.

>
> I don't understand what you mean by using an untrusted programming language.

We have several internal languages, for instance plpgsql, plperl and
plperlu. Some of them are so called 'trusted languages', for instance
plpgsql. With a trusted language you can't operate outside the pg-system
(no access to the underlaying operating system), with untrusted
languages like plperlu you have access.


> The stored procedure is written in PL/pgSQL, are you suggesting to rewrite it
> in PERL, excuse me for my ignorance.

Right, if you really needs that. But you should reconsider your idea,
see Tom's answer.



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On 2010-06-17, Atif Jung <atifjung@gmail.com> wrote:
> --0016e6db2979ca8a9d04893aae3a
> Content-Type: text/plain; charset=ISO-8859-1
>
> How do I run an operating system command from a stored procedure? In
> INFORMIX I would use the SYSTEM command, can't see to find the equivalent
> for POSTGRES.
>
> I'm running AIX6.1.

PLPGSQL doesn't allow that. use one of the untrusted languages:
C, PLPERLU, PLTCLU, etc...

http://docstore.mik.ua/orelly/perl/index.htm

They may be pirate, but they're good.

John


On Jun 18, 2010, at 12:52 PM, Atif Jung wrote:

> The problem is I have no knowledge of perl at all. Can you direct me
> to a quick and easy to get to grips with resource online?

Oh, and also, moderately obviously, there is:

http://perldoc.perl.org/

The Perl Monks will answer questions:

http://www.perlmonks.org/

Perl, very, very unfortunately, has an initially extremely steep
learning curve.  But it manipulates text wonderfully.

Friedl's Regular Expressions is mostly a Perl text.

• ISBN-10: 0596528124
• ISBN-13: 978-0596528126

John


On Jun 18, 2010, at 12:52 PM, Atif Jung wrote:

> The problem is I have no knowledge of perl at all. Can you direct me
> to a quick and easy to get to grips with resource online?

Atif Jung wrote:
> Sorry I don't understand. I am porting over some stored procedures
> from INFORMIX to POSTGRES. Some seem to port over OK, but the first
> problem I am encountering is the lack of SYSTEM command in POSTGRES.
>
> I don't understand what you mean by using an untrusted programming
> language. The stored procedure is written in PL/pgSQL, are you
> suggesting to rewrite it in PERL, excuse me for my ignorance.
>
> Thanks
>
> Atif
>

Atif, PostgreSQL has several possibilities for writing stored
procedures. Among other things, you can use languages like Perl and
Python from within the database. Here is a little test function that I
wrote just to see how can I write stored procedures for PgSQL in The
Only True Programming Language (TM):

    create or replace function external_table(varchar(255))
    returns setof text
    as $$
    my $file=shift;
    open(FILE,"<$file")||die("Cannot open file $file for reading:$!\n");
    while(<FILE>) {
        chomp;
        return_next($_);
    }
    return(undef);
    $$ language plperlu;

If you are interfacing with the OS, you have to use the "untrusted
version". Only a superuser can define such functions but he can also
grant the execute privilege to other people. It's called "untrusted
Perl" because it was developed at BP.
Having said that, if you are porting from one database to another, from
one OS to another, you should probably not port the routine that
executes a OS command. Make sure that nobody will invoke something like
"rm -rf" or the situation can get rather dramatic, and quickly. I would
advise creating a "black hole" function which does nothing (see the
"NULL" statement here:
http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html),
just to be on the safe side:

    create or replace function system(varchar(255))
    returns integer
    as $$
    begin
    return(0);
    end;
    $$ language plpgsql;

    mgogala=# select system('rm -rf /');
     system
    --------
          0
    (1 row)

    Time: 11.932 ms

This function should get you through the hoops. That is much safer than
allowing users to actually execute OS commands on the DB server. I know
that I would get extremely excited if someone attempted that on one of
my servers.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions