I made a little PostgreSQL trigger with Plpython. This triggers plays a bit with the file system, creates and delete some files of mine. Created files are owned by the "postgres" unix user, but I would like them to be owned by another user, let's say foobar. Triggers are installed with user "foobar" and executed with user "foobar" too.
Is there a way to execute the SQL trigger with the unix user 'foobar' with PostgreSQL or Plpython?
Should I use SET ROLE foobar ?
Playing with SECURITY INVOKER and SECURITY DEFINER does not seem to be good enough.
You're confusing operating system users and PostgreSQL users.
SECURITY DEFINER lets you run a function as the defining postgresql user. But no matter what PostgreSQL user is running the operating system user the back-end server runs as is always the same - usually the operating system user postgres.
By design, the PostgreSQL server cannot run operating system commands or system calls as other operating system users. That would be a nasty security hole.
However, if you want to permit that, you can. You could:
postgres user sudo rights to run some or all commands as other users; orsetuid rights to do what you want and grant the postgres user the right to execute it.In either case, the only way to run these programs is by launching them from an untrusted procedural language like plpython or plperl, or from a C extension.'
It isn't clear why you want to set the file ownership like this in the first place, but I suspect it's probably not a great idea. What if the PostgreSQL client and server aren't even on the same computer? What if there's no operating system user for that PostgreSQL user, or the usernames are different? etc.