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 area of 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 to add 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 exit code 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, 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?

Cheers,
--
Willy-Bas Loos

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

OK thanks for the help, have a nice weekend!

On Fri, Apr 22, 2022 at 3:39 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

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

--
Willy-Bas Loos