From korry@starband.net Thu Apr  21 21:34:51 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9ED9F53B9F
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 18:34:51 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 00670-07
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 21:32:07 +0000 (GMT)
Received: from cepheus.email.starband.net (cepheus.email.starband.net
	[148.78.247.123])
	by svr1.postgresql.org (Postfix) with ESMTP id E67F553B94
	for <pgsql-interfaces@postgresql.org>;
	Thu, 21 Apr 2005 18:07:41 -0300 (ADT)
Received: from 192.168.0.184 (vsat-148-63-169-95.c189.t7.mrt.starband.net
	[148.63.169.95]) (authenticated bits=0)
	by cepheus.email.starband.net (8.12.11/8.12.11) with ESMTP id
	j3LL5YZg019238; Thu, 21 Apr 2005 17:05:39 -0400
Subject: Re: plperl user function
From: Korry <korry@starband.net>
To: "Joshua D. Drake" <jd@commandprompt.com>
Cc: KeithW@narrowpathinc.com,
	PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
In-Reply-To: <42680F29.4030907@commandprompt.com>
References: <20050421194438.M68704@narrowpathinc.com>
	<42680480.7080805@commandprompt.com>
	<20050421200109.M30783@narrowpathinc.com>
	<42680F29.4030907@commandprompt.com>
Content-Type: multipart/alternative; boundary="=-/STdrquV7thzgP8W+l67"
Date: Thu, 21 Apr 2005 17:07:26 +0000
Message-Id: <1114103246.7513.19.camel@sakai>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-3) 
X-Virus-Scanned: ClamAV 0.80/816/Fri Apr  8 20:46:45 2005
	clamav-milter version 0.80j on cepheus
X-Virus-Status: Clean
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.144 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200504/71
X-Sequence-Number: 5487


--=-/STdrquV7thzgP8W+l67
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

> > Another question:  Is there an issue with using the untrusted perl language? 
> 
> IIRC you have to be a postgresql superuser (postgres) to execute an 
> untrusted language function.


I think you meant "you have to be a postgresql superuser to create an
untrusted language function".  IIRC, a normal user can execute a
function written in an untrusted language (assuming that s/he holds the
proper EXECUTE privileges), but only a cluster superuser can create a
function written in an untrusted language.  Otherwise, it would be
difficult to create a trigger in an untrusted language. 

        -- Korry

--=-/STdrquV7thzgP8W+l67
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.3.2">
</HEAD>
<BODY>
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">&gt; Another question:  Is there an issue with using the untrusted perl language? </FONT>

<FONT COLOR="#000000">IIRC you have to be a postgresql superuser (postgres) to execute an </FONT>
<FONT COLOR="#000000">untrusted language function.</FONT>
</PRE>
</BLOCKQUOTE>
<BR>
I think you meant &quot;<FONT COLOR="#000000">you have to be a postgresql superuser to </FONT><FONT COLOR="#000000"><I><U>create</U></I></FONT><FONT COLOR="#000000"> an untrusted language function&quot;.&nbsp; </FONT>IIRC, <FONT COLOR="#000000">a normal user can </FONT><FONT COLOR="#000000"><I>execute</I></FONT><FONT COLOR="#000000"> a function written in an untrusted language (assuming that s/he holds the proper EXECUTE privileges), but only a cluster superuser can </FONT><FONT COLOR="#000000"><I>create</I></FONT><FONT COLOR="#000000"> a function written in an untrusted language.&nbsp; Otherwise, it would be difficult to create a trigger in an untrusted language. </FONT><BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Korry
</BODY>
</HTML>

--=-/STdrquV7thzgP8W+l67--




From keithw@narrowpathinc.com Thu Apr  21 19:46:16 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 80FBF5363B
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 16:46:16 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 81229-01
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 19:46:07 +0000 (GMT)
Received: from mail.99main.com (mail.99main.com [208.28.184.11])
	by svr1.postgresql.org (Postfix) with ESMTP id 66A365390D
	for <pgsql-interfaces@postgresql.org>;
	Thu, 21 Apr 2005 16:46:06 -0300 (ADT)
Received: from 99main.com (localhost [127.0.0.1])
	by mail.99main.com (8.12.8+Sun/8.12.8) with ESMTP id j3LJk8rX022439
	for <pgsql-interfaces@postgresql.org>;
	Thu, 21 Apr 2005 15:46:08 -0400 (EDT)
From: "Keith Worthington" <keithw@narrowpathinc.com>
To: "PostgreSQL Interfaces" <pgsql-interfaces@postgresql.org>
Reply-To: KeithW@narrowpathinc.com
Subject: plperl user function
Date: Thu, 21 Apr 2005 15:46:08 -0400
Message-Id: <20050421194438.M68704@narrowpathinc.com>
X-Mailer: Open WebMail 2.50 20050106
X-OriginatingIP: 68.235.22.242 (npkeithw)
MIME-Version: 1.0
Content-Type: text/plain;
	charset=iso-8859-1
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/65
X-Sequence-Number: 5481

Hi All,

Three days ago I figured out that the text processing that I needed to do was
not going to be easy and perhaps even impossible in plpgsql.  Undaunted I set
out to learn perl, write a program, add plperl to my database and create a
plperl function.  As I said that was 3 days ago. 8-0  But now I am back and I
have only the last item to accomplish.  The function is written out below and
an example call to it is here

SELECT * FROM func_extract_size(quote_literal('3000 HTPP Black 4in sq Border:
WNY200BK Size:  14\'8.5" x 16\'7" Tag:  None'));

First run through I get
ERROR:  creation of Perl function failed: 'require' trapped by operation mask
at (eval 12) line 4.

Hmmm, so I lose the "use strict;"  On the next run I get
ERROR:  creation of Perl function failed: 'require' trapped by operation mask
at (eval 14) line 4.

Okay, so I put the "use strict;" back in and delete "use warnings;"  On the
next run I get
ERROR:  creation of Perl function failed: 'require' trapped by operation mask
at (eval 16) line 4.

So now I figure well I can't use either of them.  Naturally I delete "use
warnings;" again so that neither of them are in the function.  On the next run
I get
ERROR:  error from Perl function: Can't call method "RETURN" without a package
or object reference at (eval 18) line 96.

This leaves me with two questions.
1)  Why can I not use "use strict;" or "use warnings;" as they are apparently
good perl programming practice.  I say apparently because if you remember I
started learning this language 3 days ago and must be considered a neophyte. :-)
2)  What is wrong with the use of RETURN?

Kind Regards,
Keith

-- Function: func_extract_size(varchar)

-- DROP FUNCTION func_extract_size("varchar");

CREATE OR REPLACE FUNCTION func_extract_size("varchar")
 RETURNS int AS
$BODY$
#  A function to extract the net size.
#  One input argument.  description  Case insensitive.
 
#  Define a subroutine that accepts a dimension string (xx' xx") and returns
#  feet and inch strings.
  sub sub_parse_dimension{
#     Initialize local variables.
     my $v_feet_str = "";
     my $v_inch_str = "";
     my $v_dim_str = shift(@_);
#     Split the dimension into feet and inch parts using pattern matching
#     and parentheses to capture the desired parts.
     $v_dim_str =~ /(?:([\d\.\s\-\/]+)')?\s*(?:([\d\.\s\-\/]+)")?/;
     $v_feet_str = defined $1 ? $1 : 0;
     $v_inch_str = defined $2 ? $2 : 0;
     return ($v_feet_str, $v_inch_str);
  }
 
#  Define a subroutine that accepts a mixed number string and returns
#  a decimal number.
  sub sub_xform_mixed_number{
#     Initialize local variables.
     my $v_decimal = 0;
     my $v_dim_str = shift(@_);
#     Check for fraction in dimension string.
     if ($v_dim_str =~ /\//){
#        There is a fraction to deal with.
#        Parse the fraction using whitespace or a hyphen (-) and the forward
#        slash (/) character.
        $v_dim_str =~ /(?:([\d\.]+))?[\-\s]*(?:(\d+))?\/(?:(\d+))?/;
        my $v_whole = defined $1 ? $1 : 0;
        my $v_numer = defined $2 ? $2 : 0;
        my $v_denom = defined $3 ? $3 : 0;
        $v_decimal = $v_whole + $v_numer/$v_denom;
     } else {
#        There is no fraction present.  Set the output equal to the input.
        $v_decimal = $v_dim_str;
     }
     return $v_decimal;
  }

#  Begining of the program.
  my $v_description = shift(@_);
  my $v_border_id = "";
  my $v_dim1_total = 0;
  my $v_dim2_total = 0;
  my $v_tag = "";
 
#  Perform a case insensitive check for the proper data format.  Capture the
#  desired parts of the data using parentheses.
  if (/.*border:\s*(.*)\s*size:\s*(.*)\s*tag:\s*(.*)\s*/i){
#     Store the capture patterns in variables to avoid unpredictable results.
     my ($v_border_str, $v_size_str, $v_tag_str) = ($1, $2, $3);
#     Check for no border.
     if ($v_border_str =~ /none/i){
        $v_border_id = "";
     } else {
        $v_border_id = $v_border_str;
     }
#     Parse up the size string.
     if ($v_size_str =~ /\d+\s*['"]\s*x\s*\d+\s*['"]/i){
#        It looks like a size string so continue to process.
        my $v_dim1_str = "";
        my $v_dim2_str = "";
        my $v_feet_str = "";
        my $v_inch_str = "";
#        Split the size string into its two parts.
        ($v_dim1_str, $v_dim2_str) = split(/\s*x\s*/i, $v_size_str);
#        Now split dimension one into feet and inch parts.
        ($v_feet_str, $v_inch_str) = sub_parse_dimension($v_dim1_str);
#        Merge the components of the dimension into a single value.
        $v_dim1_total = ( ( 12 * sub_xform_mixed_number($v_feet_str) )
                          + sub_xform_mixed_number($v_inch_str)
                        );
#        Now split dimension two into feet and inch parts.
        ($v_feet_str, $v_inch_str) = sub_parse_dimension($v_dim2_str);
#        Merge the components of the dimension into a single value.
        $v_dim2_total = ( ( 12 * sub_xform_mixed_number($v_feet_str) )
                          + sub_xform_mixed_number($v_inch_str)
                        );
     }
#     Check for no tag.
     if ($v_tag_str =~ /none/i){
        $v_tag = "";
     } else {
        $v_tag = $v_tag_str;
     }
  } else {
     $v_border_id = "";
     $v_dim1_total = 0;
     $v_dim2_total = 0;
     $v_tag = "";
  }

  RETURN $v_dim1_total;
$BODY$
 LANGUAGE 'plperl' STABLE STRICT;



From jd@commandprompt.com Thu Apr  21 19:52:56 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 74575538A7
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 16:52:56 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 83183-02
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 19:52:33 +0000 (GMT)
Received: from hosting.commandprompt.com (128.commandprompt.com
	[207.173.200.128])
	by svr1.postgresql.org (Postfix) with ESMTP id 402CF5363B
	for <pgsql-interfaces@postgresql.org>;
	Thu, 21 Apr 2005 16:52:33 -0300 (ADT)
Received: from [192.168.1.52] (fc1smp [66.93.38.87]) (authenticated bits=0)
	by hosting.commandprompt.com (8.12.8/8.12.8) with ESMTP id
	j3LJl5ma023828; Thu, 21 Apr 2005 12:47:06 -0700
Message-ID: <42680480.7080805@commandprompt.com>
Date: Thu, 21 Apr 2005 12:52:32 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: KeithW@narrowpathinc.com
Cc: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Subject: Re: plperl user function
References: <20050421194438.M68704@narrowpathinc.com>
In-Reply-To: <20050421194438.M68704@narrowpathinc.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.025 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/66
X-Sequence-Number: 5482


> 
> This leaves me with two questions.
> 1)  Why can I not use "use strict;" or "use warnings;" as they are apparently
> good perl programming practice.

Actually it is excellent practice so don't feel bad. The reason you 
can't use them is that in order to support them in plPerl you have to 
acces the module. THe module itself is a file on the filesystem.

You can not open files on the filesystem via a plperl function unless 
you install it as untrusted.

Try installing plperlU and then running your function.


   I say apparently because if you remember I
> started learning this language 3 days ago and must be considered a neophyte. :-)
> 2)  What is wrong with the use of RETURN?

Somebody else is going to have to help you with that one.

Sincerely,

Joshua D. Drake


> 
> Kind Regards,
> Keith
> 
> -- Function: func_extract_size(varchar)
> 
> -- DROP FUNCTION func_extract_size("varchar");
> 
> CREATE OR REPLACE FUNCTION func_extract_size("varchar")
>  RETURNS int AS
> $BODY$
> #  A function to extract the net size.
> #  One input argument.  description  Case insensitive.
>  
> #  Define a subroutine that accepts a dimension string (xx' xx") and returns
> #  feet and inch strings.
>   sub sub_parse_dimension{
> #     Initialize local variables.
>      my $v_feet_str = "";
>      my $v_inch_str = "";
>      my $v_dim_str = shift(@_);
> #     Split the dimension into feet and inch parts using pattern matching
> #     and parentheses to capture the desired parts.
>      $v_dim_str =~ /(?:([\d\.\s\-\/]+)')?\s*(?:([\d\.\s\-\/]+)")?/;
>      $v_feet_str = defined $1 ? $1 : 0;
>      $v_inch_str = defined $2 ? $2 : 0;
>      return ($v_feet_str, $v_inch_str);
>   }
>  
> #  Define a subroutine that accepts a mixed number string and returns
> #  a decimal number.
>   sub sub_xform_mixed_number{
> #     Initialize local variables.
>      my $v_decimal = 0;
>      my $v_dim_str = shift(@_);
> #     Check for fraction in dimension string.
>      if ($v_dim_str =~ /\//){
> #        There is a fraction to deal with.
> #        Parse the fraction using whitespace or a hyphen (-) and the forward
> #        slash (/) character.
>         $v_dim_str =~ /(?:([\d\.]+))?[\-\s]*(?:(\d+))?\/(?:(\d+))?/;
>         my $v_whole = defined $1 ? $1 : 0;
>         my $v_numer = defined $2 ? $2 : 0;
>         my $v_denom = defined $3 ? $3 : 0;
>         $v_decimal = $v_whole + $v_numer/$v_denom;
>      } else {
> #        There is no fraction present.  Set the output equal to the input.
>         $v_decimal = $v_dim_str;
>      }
>      return $v_decimal;
>   }
> 
> #  Begining of the program.
>   my $v_description = shift(@_);
>   my $v_border_id = "";
>   my $v_dim1_total = 0;
>   my $v_dim2_total = 0;
>   my $v_tag = "";
>  
> #  Perform a case insensitive check for the proper data format.  Capture the
> #  desired parts of the data using parentheses.
>   if (/.*border:\s*(.*)\s*size:\s*(.*)\s*tag:\s*(.*)\s*/i){
> #     Store the capture patterns in variables to avoid unpredictable results.
>      my ($v_border_str, $v_size_str, $v_tag_str) = ($1, $2, $3);
> #     Check for no border.
>      if ($v_border_str =~ /none/i){
>         $v_border_id = "";
>      } else {
>         $v_border_id = $v_border_str;
>      }
> #     Parse up the size string.
>      if ($v_size_str =~ /\d+\s*['"]\s*x\s*\d+\s*['"]/i){
> #        It looks like a size string so continue to process.
>         my $v_dim1_str = "";
>         my $v_dim2_str = "";
>         my $v_feet_str = "";
>         my $v_inch_str = "";
> #        Split the size string into its two parts.
>         ($v_dim1_str, $v_dim2_str) = split(/\s*x\s*/i, $v_size_str);
> #        Now split dimension one into feet and inch parts.
>         ($v_feet_str, $v_inch_str) = sub_parse_dimension($v_dim1_str);
> #        Merge the components of the dimension into a single value.
>         $v_dim1_total = ( ( 12 * sub_xform_mixed_number($v_feet_str) )
>                           + sub_xform_mixed_number($v_inch_str)
>                         );
> #        Now split dimension two into feet and inch parts.
>         ($v_feet_str, $v_inch_str) = sub_parse_dimension($v_dim2_str);
> #        Merge the components of the dimension into a single value.
>         $v_dim2_total = ( ( 12 * sub_xform_mixed_number($v_feet_str) )
>                           + sub_xform_mixed_number($v_inch_str)
>                         );
>      }
> #     Check for no tag.
>      if ($v_tag_str =~ /none/i){
>         $v_tag = "";
>      } else {
>         $v_tag = $v_tag_str;
>      }
>   } else {
>      $v_border_id = "";
>      $v_dim1_total = 0;
>      $v_dim2_total = 0;
>      $v_tag = "";
>   }
> 
>   RETURN $v_dim1_total;
> $BODY$
>  LANGUAGE 'plperl' STABLE STRICT;
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org


-- 
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



From keithw@narrowpathinc.com Thu Apr  21 20:07:35 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id AD12653A2E
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 17:07:35 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 86681-07
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 20:07:28 +0000 (GMT)
Received: from mail.99main.com (mail.99main.com [208.28.184.11])
	by svr1.postgresql.org (Postfix) with ESMTP id D127A538AF
	for <pgsql-interfaces@postgresql.org>;
	Thu, 21 Apr 2005 17:07:27 -0300 (ADT)
Received: from 99main.com (localhost [127.0.0.1])
	by mail.99main.com (8.12.8+Sun/8.12.8) with ESMTP id j3LK7RrX026847;
	Thu, 21 Apr 2005 16:07:27 -0400 (EDT)
From: "Keith Worthington" <keithw@narrowpathinc.com>
To: "Joshua D. Drake" <jd@commandprompt.com>
Cc: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Reply-To: KeithW@narrowpathinc.com
Subject: Re: plperl user function
Date: Thu, 21 Apr 2005 16:07:27 -0400
Message-Id: <20050421200109.M30783@narrowpathinc.com>
In-Reply-To: <42680480.7080805@commandprompt.com>
References: <20050421194438.M68704@narrowpathinc.com>
	<42680480.7080805@commandprompt.com>
X-Mailer: Open WebMail 2.50 20050106
X-OriginatingIP: 68.235.22.242 (npkeithw)
MIME-Version: 1.0
Content-Type: text/plain;
	charset=iso-8859-1
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/67
X-Sequence-Number: 5483

On Thu, 21 Apr 2005 12:52:32 -0700, Joshua D. Drake wrote
> > 
> > This leaves me with two questions.
> > 1)  Why can I not use "use strict;" or "use warnings;" as they
> > are apparently good perl programming practice.
> [snip]
> > Kind Regards,
> > Keith
> 
> Actually it is excellent practice so don't feel bad. The reason you 
> can't use them is that in order to support them in plPerl you have 
> to access the module. The module itself is a file on the filesystem.
> 
> You can not open files on the filesystem via a plperl function 
> unless you install it as untrusted.
> 
> Try installing plperlU and then running your function.
[snip]
> Sincerely,
> 
> Joshua D. Drake

Well, I am blown away by how this stuff works.  After a createlang command I
put the two use commands back into my function and changed the language.  Now
all I have to figure out is the RETURN problem.

Another question:  Is there an issue with using the untrusted perl language? 
If the code is tested and working is there any real reason to continue to have
"use strict;" and "use warnings;" in the function?

Kind Regards,
Keith



From jd@commandprompt.com Thu Apr  21 20:37:06 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 85B4553A93
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 17:37:06 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 93876-05
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 20:36:59 +0000 (GMT)
Received: from hosting.commandprompt.com (128.commandprompt.com
	[207.173.200.128])
	by svr1.postgresql.org (Postfix) with ESMTP id 8365253AB9
	for <pgsql-interfaces@postgresql.org>;
	Thu, 21 Apr 2005 17:36:58 -0300 (ADT)
Received: from [192.168.1.52] (fc1smp [66.93.38.87]) (authenticated bits=0)
	by hosting.commandprompt.com (8.12.8/8.12.8) with ESMTP id
	j3LKVVma002622; Thu, 21 Apr 2005 13:31:31 -0700
Message-ID: <42680EEA.7060404@commandprompt.com>
Date: Thu, 21 Apr 2005 13:36:58 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: "Joshua D. Drake" <jd@commandprompt.com>
Cc: KeithW@narrowpathinc.com,
	PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Subject: Re: plperl user function
References: <20050421194438.M68704@narrowpathinc.com>
	<42680480.7080805@commandprompt.com>
In-Reply-To: <42680480.7080805@commandprompt.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.025 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/68
X-Sequence-Number: 5484

Joshua D. Drake wrote:
>>      $v_border_id = "";
>>      $v_dim1_total = 0;
>>      $v_dim2_total = 0;
>>      $v_tag = "";
>>   }
>>
>>   RETURN $v_dim1_total;
^^^^^^^^^^^^^^^^^^^^^^^^^^^

I "think" that needs to be lowercase.

Sincerely,

Joshua D. Drake




>> $BODY$
>>  LANGUAGE 'plperl' STABLE STRICT;
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>>                http://archives.postgresql.org
> 
> 
> 


-- 
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



From jd@commandprompt.com Thu Apr  21 20:38:42 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 77D3053A7B
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 17:38:42 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 94806-05
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 20:38:12 +0000 (GMT)
Received: from hosting.commandprompt.com (128.commandprompt.com
	[207.173.200.128])
	by svr1.postgresql.org (Postfix) with ESMTP id B25CE53B2E
	for <pgsql-interfaces@postgresql.org>;
	Thu, 21 Apr 2005 17:38:00 -0300 (ADT)
Received: from [192.168.1.52] (fc1smp [66.93.38.87]) (authenticated bits=0)
	by hosting.commandprompt.com (8.12.8/8.12.8) with ESMTP id
	j3LKWYma003010; Thu, 21 Apr 2005 13:32:34 -0700
Message-ID: <42680F29.4030907@commandprompt.com>
Date: Thu, 21 Apr 2005 13:38:01 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: KeithW@narrowpathinc.com
Cc: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Subject: Re: plperl user function
References: <20050421194438.M68704@narrowpathinc.com>
	<42680480.7080805@commandprompt.com>
	<20050421200109.M30783@narrowpathinc.com>
In-Reply-To: <20050421200109.M30783@narrowpathinc.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/69
X-Sequence-Number: 5485


> 
> Another question:  Is there an issue with using the untrusted perl language? 

IIRC you have to be a postgresql superuser (postgres) to execute an 
untrusted language function.


> If the code is tested and working is there any real reason to continue to have
> "use strict;" and "use warnings;" in the function?

Only for manageability in the future.

Sincerely,

Joshua D. Drake


> 
> Kind Regards,
> Keith
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match


-- 
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



From mike@fuhr.org Thu Apr  21 20:43:08 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 0043753B3D
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 17:43:08 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 94458-09
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 20:42:56 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 3807F53B17
	for <pgsql-interfaces@postgresql.org>;
	Thu, 21 Apr 2005 17:42:55 -0300 (ADT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j3LKgo0O034100
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 21 Apr 2005 14:42:53 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j3LKgoo3043361;
	Thu, 21 Apr 2005 14:42:50 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j3LKgoMt043360;
	Thu, 21 Apr 2005 14:42:50 -0600 (MDT) (envelope-from mfuhr)
Date: Thu, 21 Apr 2005 14:42:50 -0600
From: Michael Fuhr <mike@fuhr.org>
To: "Joshua D. Drake" <jd@commandprompt.com>
Cc: KeithW@narrowpathinc.com,
	PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Subject: Re: plperl user function
Message-ID: <20050421204250.GA43299@winnie.fuhr.org>
References: <20050421194438.M68704@narrowpathinc.com>
	<42680480.7080805@commandprompt.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <42680480.7080805@commandprompt.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/70
X-Sequence-Number: 5486

On Thu, Apr 21, 2005 at 12:52:32PM -0700, Joshua D. Drake wrote:
> >2)  What is wrong with the use of RETURN?
> 
> Somebody else is going to have to help you with that one.

In Perl "return" is lower case.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From keithw@narrowpathinc.com Fri Apr  22 12:29:46 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id C1BAA53884
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Fri, 22 Apr 2005 09:29:46 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 13718-10
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Fri, 22 Apr 2005 12:29:38 +0000 (GMT)
Received: from mail.99main.com (mail.99main.com [208.28.184.11])
	by svr1.postgresql.org (Postfix) with ESMTP id B05DA536D4
	for <pgsql-interfaces@postgresql.org>;
	Fri, 22 Apr 2005 09:29:36 -0300 (ADT)
Received: from 99main.com (localhost [127.0.0.1])
	by mail.99main.com (8.12.8+Sun/8.12.8) with ESMTP id j3MCTdrX019475
	for <pgsql-interfaces@postgresql.org>;
	Fri, 22 Apr 2005 08:29:39 -0400 (EDT)
From: "Keith Worthington" <keithw@narrowpathinc.com>
To: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Reply-To: KeithW@narrowpathinc.com
Subject: Re: plperl user function
Date: Fri, 22 Apr 2005 08:29:39 -0400
Message-Id: <20050422121739.M32137@narrowpathinc.com>
In-Reply-To: <42680F29.4030907@commandprompt.com>
References: <20050421194438.M68704@narrowpathinc.com>
	<42680480.7080805@commandprompt.com>
	<20050421200109.M30783@narrowpathinc.com>
	<42680F29.4030907@commandprompt.com>
X-Mailer: Open WebMail 2.50 20050106
X-OriginatingIP: 68.235.22.242 (npkeithw)
MIME-Version: 1.0
Content-Type: text/plain;
	charset=iso-8859-1
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/72
X-Sequence-Number: 5488


> > This leaves me with two questions.
> > 1)  Why can I not use "use strict;" or "use warnings;" as they are
> >     apparently good perl programming practice.  I say apparently
> >     because if you remember I started learning this language 3 days
> >     ago and must be considered a neophyte. :-)

> You can't use "use" instruction for security reason. All disk access
> are denied and use need a disk access.
> 
> I think you need to use plperlu.

Yes.  That fixed my use problems.  I am not sure I like the idea of using an
untrusted language.  I wonder if it causes a potential security issue.

> 2)  What is wrong with the use of RETURN?

According to Michael (whose email I can't access at the moment) it has to be
lowercase.  I never dreamed Perl would be case sensitive but after changing
the case, voila! :-)
 
> > Another question:  Is there an issue with using the untrusted perl
> > language?
> 
> IIRC you have to be a postgresql superuser (postgres) to execute an 
> untrusted language function.

I just tested the untrusted function using a normal user and it worked.

> > If the code is tested and working is there any real reason to
> > continue to have "use strict;" and "use warnings;" in the function?
> 
> Only for manageability in the future.

Hmmm well I have this gut feeling that it is better to use a trusted language
than an untrusted one but I have no idea if that is supported by fact.  My
inclination is that now that the function is working and tested that I should
comment out the two "use" commands and recreate the function with the trusted
version.

Kind Regards,
Keith



From ktm@it.is.rice.edu Fri Apr  22 12:41:41 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D12CC53913
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Fri, 22 Apr 2005 09:41:41 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 16452-10
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Fri, 22 Apr 2005 12:41:32 +0000 (GMT)
Received: from is.rice.edu (is.rice.edu [128.42.42.24])
	by svr1.postgresql.org (Postfix) with ESMTP id 15EB553884
	for <pgsql-interfaces@postgresql.org>;
	Fri, 22 Apr 2005 09:41:31 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by is.rice.edu (Postfix) with ESMTP id AFF184182B;
	Fri, 22 Apr 2005 07:41:34 -0500 (CDT)
Received: from is.rice.edu ([127.0.0.1])
	by localhost (it.is.rice.edu [127.0.0.1]) (amavisd-new,
	port 10024) with LMTP
	id 14653-01-33; Fri, 22 Apr 2005 07:41:31 -0500 (CDT)
Received: by is.rice.edu (Postfix, from userid 18612)
	id 2A85B41822; Fri, 22 Apr 2005 07:41:31 -0500 (CDT)
Date: Fri, 22 Apr 2005 07:41:30 -0500
From: Kenneth Marshall <ktm@it.is.rice.edu>
To: Keith Worthington <keithw@narrowpathinc.com>
Cc: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Subject: Re: plperl user function
Message-ID: <20050422124130.GB14229@it.is.rice.edu>
References: <20050421194438.M68704@narrowpathinc.com>
	<42680480.7080805@commandprompt.com>
	<20050421200109.M30783@narrowpathinc.com>
	<42680F29.4030907@commandprompt.com>
	<20050422121739.M32137@narrowpathinc.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050422121739.M32137@narrowpathinc.com>
User-Agent: Mutt/1.4.2i
X-Virus-Scanned: by amavis-2.2.1 at is.rice.edu
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200504/75
X-Sequence-Number: 5491

Keith,

The untrusted language function is not neccessarily any less
secure than the trusted language function. You just need to
vet the function more carefully because it is allowed access
to the local filesystem and other system functions. Once the
function is created, only those who are given execute permission
can run it.

Ken

On Fri, Apr 22, 2005 at 08:29:39AM -0400, Keith Worthington wrote:
> 
> > > This leaves me with two questions.
> > > 1)  Why can I not use "use strict;" or "use warnings;" as they are
> > >     apparently good perl programming practice.  I say apparently
> > >     because if you remember I started learning this language 3 days
> > >     ago and must be considered a neophyte. :-)
> 
> > You can't use "use" instruction for security reason. All disk access
> > are denied and use need a disk access.
> > 
> > I think you need to use plperlu.
> 
> Yes.  That fixed my use problems.  I am not sure I like the idea of using an
> untrusted language.  I wonder if it causes a potential security issue.
> 
> > 2)  What is wrong with the use of RETURN?
> 
> According to Michael (whose email I can't access at the moment) it has to be
> lowercase.  I never dreamed Perl would be case sensitive but after changing
> the case, voila! :-)
>  
> > > Another question:  Is there an issue with using the untrusted perl
> > > language?
> > 
> > IIRC you have to be a postgresql superuser (postgres) to execute an 
> > untrusted language function.
> 
> I just tested the untrusted function using a normal user and it worked.
> 
> > > If the code is tested and working is there any real reason to
> > > continue to have "use strict;" and "use warnings;" in the function?
> > 
> > Only for manageability in the future.
> 
> Hmmm well I have this gut feeling that it is better to use a trusted language
> than an untrusted one but I have no idea if that is supported by fact.  My
> inclination is that now that the function is working and tested that I should
> comment out the two "use" commands and recreate the function with the trusted
> version.
> 
> Kind Regards,
> Keith
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



From sdavis2@mail.nih.gov Fri Apr  22 13:18:18 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D1AA153A0D
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Fri, 22 Apr 2005 10:18:18 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 27883-02
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Fri, 22 Apr 2005 13:18:10 +0000 (GMT)
Received: from mx5.net.nih.gov (mailfwd.nih.gov [165.112.130.36])
	by svr1.postgresql.org (Postfix) with ESMTP id 4B46753B17
	for <pgsql-interfaces@postgresql.org>;
	Fri, 22 Apr 2005 10:18:07 -0300 (ADT)
Received: from mx5.net.nih.gov (localhost [127.0.0.1])
	by mx5.net.nih.gov (8.12.10/8.11.6) with ESMTP id j3MDIAas020644
	for <pgsql-interfaces@postgresql.org>; Fri, 22 Apr 2005 09:18:11 -0400
Received: from [128.231.145.14] (holmes.nhgri.nih.gov [128.231.145.14])
	by mx5.net.nih.gov (8.12.10/8.11.6) with ESMTP id j3MDIAKd020641;
	Fri, 22 Apr 2005 09:18:10 -0400
In-Reply-To: <20050422121739.M32137@narrowpathinc.com>
References: <20050421194438.M68704@narrowpathinc.com>
	<42680480.7080805@commandprompt.com>
	<20050421200109.M30783@narrowpathinc.com>
	<42680F29.4030907@commandprompt.com>
	<20050422121739.M32137@narrowpathinc.com>
Mime-Version: 1.0 (Apple Message framework v622)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <3e610801c25516f2be4dcaa14c2b8a5e@mail.nih.gov>
Content-Transfer-Encoding: 7bit
Cc: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
From: Sean Davis <sdavis2@mail.nih.gov>
Subject: Re: plperl user function
Date: Fri, 22 Apr 2005 09:18:10 -0400
To: KeithW@narrowpathinc.com
X-Mailer: Apple Mail (2.622)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/73
X-Sequence-Number: 5489


On Apr 22, 2005, at 8:29 AM, Keith Worthington wrote:

>
>>> This leaves me with two questions.
>>> 1)  Why can I not use "use strict;" or "use warnings;" as they are
>>>     apparently good perl programming practice.  I say apparently
>>>     because if you remember I started learning this language 3 days
>>>     ago and must be considered a neophyte. :-)
>
>> You can't use "use" instruction for security reason. All disk access
>> are denied and use need a disk access.
>>
>> I think you need to use plperlu.
>
> Yes.  That fixed my use problems.  I am not sure I like the idea of 
> using an
> untrusted language.  I wonder if it causes a potential security issue.
>
>> 2)  What is wrong with the use of RETURN?
>
> According to Michael (whose email I can't access at the moment) it has 
> to be
> lowercase.  I never dreamed Perl would be case sensitive but after 
> changing
> the case, voila! :-)

Yep.  Perl is case-sensitive throughout.

>
>>> Another question:  Is there an issue with using the untrusted perl
>>> language?
>>
>> IIRC you have to be a postgresql superuser (postgres) to execute an
>> untrusted language function.
>
> I just tested the untrusted function using a normal user and it worked.
>
>>> If the code is tested and working is there any real reason to
>>> continue to have "use strict;" and "use warnings;" in the function?
>>
>> Only for manageability in the future.
>
> Hmmm well I have this gut feeling that it is better to use a trusted 
> language
> than an untrusted one but I have no idea if that is supported by fact. 
>  My
> inclination is that now that the function is working and tested that I 
> should
> comment out the two "use" commands and recreate the function with the 
> trusted
> version.
>

The security concerns are real, but I PERSONALLY write most of my 
pl/perl functions using the untrusted version.  Execution of the 
untrusted function should generally not be a security concern, I don't 
think.  There are some very specific situations where you could imagine 
things going bad.  However, in general, I think the ability to "use 
strict" and have all of the modules on CPAN available (perl 
mantra--"Use the CPAN") for use FAR outweigh the very slightly more 
energy it takes for me to create a safe function that does not destroy 
enviroment variables, write to user-specified directories, or execute 
system-level commands (like rm, as a nasty example) without safeguards 
in place.

All that said, I lead a pretty sheltered life (behind THREE firewalls 
with only three users on a dedicated postgres server box, all of whom 
are sitting within earshot of me), so you may need to be more paranoid 
than I am.

Sean