Thread: plperl user function
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;
>
> 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/
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
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/
> > 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/
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/
<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
> > 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
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
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