Why are some functions defined with SECURITY DEFINER?

[robintw]

A few of the functions defined in PGSTAC are defined with the SECURITY DEFINER modifier, which means that when they are executed they are executed with the permissions of the user that defined them rather than the user that is executing them.

The only ones that seem to be defined in this way are: search, item_by_id and get_items.

Is there a reason these particular functions, and only these, are defined with SECURITY DEFINER?

For context, I am playing with implementing per-item/per-collection security using Postgres Row Level Security, and found that these functions over-rode the security as they were defined in this way. For my current experimentation I've just redefined the function with SECURITY INVOKER - but I'm wondering if that is going to cause me problems elsewhere, and whether the SECURITY DEFINER definition was important?

[bitner]

This was done as search also updates some cache/logging tables (searches, search_wheres) and I didn't want to give the base read only pgstac_read role direct write permissions to those tables. We could either shift the SECURITY DEFINER definition down to the functions that are doing the logging/caching rather than on the search/item_by_id/get_items functions or open up the permissions to the search/search_wheres tables for insert/update. Supporting RLS is definitely something I'd like to accomodate!

[bitner]

@robintw In https://github.com/stac-utils/pgstac/pull/160 the functions marked as SECURITY DEFINER have been changed to the lowest level possible. Now the only functions that are marked as SECURITY DEFINER are those that need to manage the partition structure and constraints and the functions that populate the searches and search_wheres table rather than on the searches function.

[robintw]

That's wonderful, thank you very much!