Thread: plperl user function

plperl user function

From
"Keith Worthington"
Date:
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;


Re: plperl user function

From
"Joshua D. Drake"
Date:
> 
> 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/


Re: plperl user function

From
"Keith Worthington"
Date:
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


Re: plperl user function

From
"Joshua D. Drake"
Date:
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/


Re: plperl user function

From
"Joshua D. Drake"
Date:
> 
> 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/


Re: plperl user function

From
Michael Fuhr
Date:
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/


Re: plperl user function

From
Korry
Date:
<blockquote type="CITE"><pre>
<font color="#000000">> 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 "<font color="#000000">you have to be a postgresql superuser to
</font><fontcolor="#000000"><i><u>create</u></i></font><font color="#000000"> an untrusted language function". 
</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),
butonly a cluster superuser can </font><font color="#000000"><i>create</i></font><font color="#000000"> a function
writtenin an untrusted language.  Otherwise, it would be difficult to create a trigger in an untrusted language.
</font><br/><br />         -- Korry  

Re: plperl user function

From
"Keith Worthington"
Date:
> > 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


Re: plperl user function

From
Sean Davis
Date:
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



Re: plperl user function

From
Kenneth Marshall
Date:
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