Re: Runnning operating system commands from an SPL

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.

Thanks

Atif

Atif Jung <atifjung(at)gmail(dot)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(at)spamfence(dot)net> writes:
> Atif Jung <atifjung(at)gmail(dot)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

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

On 17 June 2010 15:51, Andreas Kretschmer <akretschmer(at)spamfence(dot)net> wrote:

> Atif Jung <atifjung(at)gmail(dot)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°
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

Atif Jung <atifjung(at)gmail(dot)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(at)gmail(dot)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...

Thanks Adreas, I understand now. Like I mentioned I have several pgSQL
stored procedures which access the OS and need to be rewritten in perlu.

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?
Thanks again

Atif

--
Words of Wisdom
=============

George Bernard Shaw, the British playwright said:

“The world is in dire need of a man with the mind of Muhammad; religious
people in the Middle Ages, due to their ignorance and prejudice, had
pictured him in a very dark way as they used to consider him the enemy of
Christianity. But after looking into the story of this man I found it to be
an amazing and a miraculous one and I came to the conclusion that he was
never an enemy of Christianity, and must be called instead the saviour of
humanity. In my opinion, if he was to be given control over the world today,
he would solve our problems and secure the peace and happiness for which the
world is longing.”

On 17 June 2010 16:37, Andreas Kretschmer <akretschmer(at)spamfence(dot)net> wrote:

> Atif Jung <atifjung(at)gmail(dot)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°
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

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