Обсуждение: logical replication worker can't find postgis function

On Fri, 2022-04-22 at 13:25 +0200, Willy-Bas Loos wrote:
> I'm using logical replication on postgresql 13. On the subscriber, there's a trigger on a table that calculates the
areaof the geometry that's in another column. 
 
> I enabled the trigger with 
> ALTER TABLE atable ENABLE ALWAYS TRIGGER atrigger;
> 
> But the logical replication worker can't find st_area:
> 2022-04-22 13:14:11.244 CEST [1932237] LOG:  logical replication apply worker for subscription "ba_acc1" has started
> 2022-04-22 13:14:11.282 CEST [1932237] ERROR:  function st_area(public.geometry) does not exist at character 14
> 2022-04-22 13:14:11.282 CEST [1932237] HINT:  No function matches the given name and argument types. You might need
toadd explicit type casts.
 
> 2022-04-22 13:14:11.282 CEST [1932237] QUERY:  SELECT round(st_area(NEW.epsg28992_geom))
> 2022-04-22 13:14:11.282 CEST [1932237] CONTEXT:  PL/pgSQL function util.location_extras() line 3 at assignment
> 2022-04-22 13:14:11.285 CEST [1562110] LOG:  background worker "logical replication worker" (PID 1932237) exited with
exitcode 1
 
> 
> The trigger works well when I fire it in a normal update query.
> How can this happen and how can I resolve this?

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.

Fix your function:

  ALTER FUNCTION trigger_function() SET search_path = public;

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

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.albe@cybertec.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