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!
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.