2

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?

Bianca
  • 322
  • 1
  • 10
  • 1
    As a hint - if you `print Dumper \%ENV` then you will get a list of key-value pairs, rather than a big blob. – Sobrique Jan 22 '18 at 12:44
  • Where's the trigger being called from? `HOME` may be defined in terminal, but it doesn't seem to be there in your environment, and that implies that it's not being inherited by the perl process. – Sobrique Jan 22 '18 at 12:45
  • Would imagine you'd need to pass in such things as the database server won't have access to them otherwise – Chris Turner Jan 22 '18 at 12:59
  • Do you know how I could pass them to postgres? I set an ENV variable to denote the name of another database I need to get data from, but now I'm not sure that getting that value is possible – Bianca Jan 22 '18 at 13:02
  • Plain and simple, server has no idea about client environment. You can write to some table first and have a trigger to read from it. – mpapec Jan 22 '18 at 19:41

1 Answers1

0

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.

nickcrabtree
  • 358
  • 1
  • 10