1

I would like to call a RESTful Web Services from PostgreSQL procedure/function using "plperl". I can 'not' use plperlu, plpython2u, JOOQ or Hibernate.

So I would like to use something "like" the "plperlu" PostgreSQL-proc example from the following website, but for plperl (or something like plperl that is 'not' plperlu, 'not' plpython2u, 'not' JOOQ and 'not' Hibernate): Calling RESTful Web Services from PostgreSQL procedure/function

I tried the following code but it did not work for plperl, because it might only work for plperlu & not for plperl (but there is no way for me to get AWS to install plperlu on my AWS-PostgreSQL-RDS):

CREATE OR REPLACE FUNCTION restful.get()
RETURNS text
LANGUAGE plperl
SECURITY DEFINER
AS $function$
use REST::Client;
use Encode qw(encode);
my $client = REST::Client->new(); 
$client->getUseragent()->proxy( 'https', 'https://xdmactive.maxarmdm.com:8443/semarchy/api/rest/query/test/test' ); -- # use4 proxy authentication
$client->addHeader('Content-Type', 'application/json');  --   # headers
$client->addHeader('Api-Key', '9Yw_jmh.ey3rUlkzjsdueliokT1A_kh');  # headers
$client->GET('https://xdmactive.maxarmdm.com:8443/semarchy/api/rest/query/test/test'); -- # encoding
return $client->responseContent();
$function$

This is the error I get, & it looks like 'maybe' a permissions error (but might be plperl vs plperlu): SQL Error [42601]: ERROR: Unable to load REST/Client.pm into plperl at line 2. BEGIN failed--compilation aborted at line 2. Where: compilation of PL/Perl function "get"

The following is a list of all of my "pg_available_extensions" that are available for me to use in my AWS-PostgreSQL-RDS:

address_standardizer,  
address_standardizer_data_us,  
amcheck,  
apg_plan_mgmt,  
aurora_stat_utils,  
aws_commons,  
aws_ml,  
aws_s3,  
bloom,  
btree_gin,  
btree_gist,  
citext,  
cube,  
dblink,  
dict_int,  
dict_xsyn,  
earthdistance,  
fuzzystrmatch,  
hll,  
hstore,  
hstore_plperl,  
intagg,  
intarray,  
ip4r,  
isn,  
jsonb_plperl,  
log_fdw,  
ltree,  
orafce,  
pg_buffercache,  
pg_freespacemap
pg_hint_plan
pg_prewarm
pg_repack
pg_similarity
pg_stat_statements
pg_trgm
pg_visibility
pgaudit
pgcrypto
pgrouting
pgrowlocks
pgstattuple
pgtap
plcoffee
plls
plperl
plpgsql
plprofiler
pltcl
plv8
postgis
postgis_tiger_geocoder
postgis_topology
postgres_fdw
prefix
rds_activity_stream
sslinfo
tablefunc
test_parser
tsm_system_rows
tsm_system_time
unaccent
uuid-ossp
clamp
  • 2,552
  • 1
  • 6
  • 16
Bryan-Bailey
  • 11
  • 1

1 Answers1

0

Loading a module requires reading the file which implements that module from disk. Trusted pl languages can't read from disk, which is one of main things that goes into them being trusted.

You can pre-read the files before the code switches to trusted mode, effectively whitelisting the module.

plperl.on_init='require REST::Client;'

But the chances of getting RDS to let you do this is pretty low, for the same reason as that they don't let you use untrusted pl languages in the first place.

Overall, the only solution to hosted products not letting you do the things you want to do is not to use them.

jjanes
  • 37,812
  • 5
  • 27
  • 34