Re: Runnning operating system commands from an SPL

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