Re: logical replication worker can't find postgis function

On Fri, 2022-04-22 at 15:26 +0200, Willy-Bas Loos wrote:
> On Fri, Apr 22, 2022 at 3:20 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> >
> > The trigger function is bad and dangerous, because it relies on the current setting of "search_path".
> >
> > You notice that with logical replication, because "search_path" is empty to avoid security problems.
>
> Thanks a lot!
> Do you mean that all trigger functions are bad and dangerous, or just mine?
> Do you have any suggestions for an alternative?

There is nothing wrong per se with using trigger functions.

But, to attempt a generic statement, any function that fails if you change "search_path"
is a potential problem.

If your application makes sure that "search_path" is always set correctly, the problem
is smaller.

If highly privileged processes call the function, the problem becomes worse, because the
potential damage is bigger.

The best way to make sure nothing can happen is to create all functions with a
hard-wired "search_path". Then nothing can go wrong.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com