PL/Perl trigger cannot use ENV variables defined in .bashrc

Question

I'm trying to use an environment variable (for example HOME) inside a PL/perl trigger in postgresql and it appears to be empty.

Running printenv | grep HOME in the terminal returns the desired path.

I am defining the trigger as

CREATE EXTENSION IF NOT EXISTS plperlu;

CREATE OR REPLACE FUNCTION update_solved() RETURNS trigger AS $update_solved$
  elog(NOTICE, "Hello");
  elog(NOTICE, $ENV{'HOME'});

  return;
$update_solved$ LANGUAGE plperlu;

When a query activates the trigger, I get the output

NOTICE:  Hello
NOTICE: 

I checked what %ENV contains inside the script(using use Data::Dumper; elog(NOTICE, Dumper(%ENV));:

$VAR1 = 'LANG';
$VAR2 = 'en_US.UTF-8';
$VAR3 = 'LC_TIME';
$VAR4 = 'C';
$VAR5 = 'LC_MONETARY';
$VAR6 = 'C';
$VAR7 = 'PGSYSCONFDIR';
$VAR8 = '/etc/postgresql-common';
$VAR9 = 'PG_GRANDPARENT_PID';
$VAR10 = '513';
$VAR11 = 'PGLOCALEDIR';
$VAR12 = '/usr/share/locale';
$VAR13 = 'LC_CTYPE';
$VAR14 = 'en_US.UTF-8';
$VAR15 = 'LC_COLLATE';
$VAR16 = 'en_US.UTF-8';
$VAR17 = 'PGDATA';
$VAR18 = '/var/lib/postgresql/9.6/main';
$VAR19 = 'LC_MESSAGES';
$VAR20 = 'en_US.UTF-8';
$VAR21 = 'LC_NUMERIC';
$VAR22 = 'C';
$VAR23 = 'PWD';
$VAR24 = '/var/lib/postgresql';
$VAR25 = 'PG_OOM_ADJUST_FILE';
$VAR26 = '/proc/self/oom_score_adj';

Do you know how I can set the env variables that I need that are defined in the terminal session, to be used inside the PL/perl trigger?

Answer 1

Although you are correctly calling the untrusted plperlu variant of PL/Perl, the initial environment is still the same restricted environment as for the normal "trusted" PL/Perl.

One solution would be to run a sub-shell which will then initialise its environment. Something like:

my $home = `bash -lc "echo \$HOME"`;

You may need to tweak the arguments to bash to get what you want. The above command explicitly runs bash as I'm not sure what default shell you are going to have, and you mentioned your .bashrc so the -l might be needed to ensure that gets read.

You could also explicitly read the information you want from an arbitrary text file on the system, which might be easier than messing with environment variables.