2

I'm trying to create a PostgreSQL trigger on Linux written in Perl which should execute code based on external libraries. The SQL script containing the trigger looks like this:

CREATE OR REPLACE FUNCTION notify_mytable_update() RETURNS trigger AS $$
  use lib "full_path_to_lib_dir";
  use MyModule;

  return;
$$ LANGUAGE plperlu
SECURITY DEFINER
SET search_path = myschema, public, pg_temp;

DROP TRIGGER IF EXISTS notify_mytable_update ON mytable;

CREATE TRIGGER notify_mytable_update AFTER UPDATE ON mytable
  FOR EACH ROW
  EXECUTE PROCEDURE notify_mytable_update();

The issue with this is that whenever I try to this script with psql I get a permission denied error in the Perl code for accessing MyModule. Giving full access to my home directory to postgres didn't help.

Thank you in advance!

George R.
  • 311
  • 2
  • 11

1 Answers1

1

Don't forget that to have access to a file, you not only need permissions to the file and the directory where it resides, but also to all directories in the path.

So if your module is /home/george/MyModule.pm, you need access to / and /home in addition to /home/george and the file itself.

You'll have to give these permissions to the operating system user running the PostgreSQL server process, commonly postgres.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • This solved the permission denied error. However, now Perl cannot find MyModule in \@INC even though in the error message \@INC contains the directory of MyModule. – George R. Apr 17 '18 at 15:27
  • Names are case sensitive. The `.pm` suffix is required. `strace` the PostgreSQL backend process to see which file it is looking for. If in doubt, start a new question. – Laurenz Albe Apr 17 '18 at 16:45