Skip site navigation (1) Skip section navigation (2)

Re: Runnning operating system commands from an SPL

From: Mladen Gogala <mgogala(at)vmsinfo(dot)com>
To: Atif Jung <atifjung(at)gmail(dot)com>
Cc: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Runnning operating system commands from an SPL
Date: 2010-06-18 20:12:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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>) {
    $$ 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:, 
just to be on the safe side:

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

    mgogala=# select system('rm -rf /');
    (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 
The Leader in Integrated Media Intelligence Solutions

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2010-06-21 19:36:07
Subject: Re: ilike not using index.
Previous:From: Rob RichardsonDate: 2010-06-18 17:45:14
Subject: FW: mysterious sortorder issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group