Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely

On Sun, Sep 11, 2022 at 09:46:47PM -0700, Noah Misch wrote:
> On Thu, Sep 08, 2022 at 01:20:31PM +0200, Peter Eisentraut wrote:
> > On 01.09.22 03:11, Bruce Momjian wrote:
> > >On Tue, Aug 16, 2022 at 03:38:13PM -0400, Bruce Momjian wrote:
> > >>On Tue, Aug 16, 2022 at 03:34:22PM -0400, Tom Lane wrote:
> > >>>Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > >>>>I have written the attached patch to mention this issue about sql_body
> > >>>>functions.
> > >>>
> > >>>Spell-check, please. Seems OK otherwise.
>
> > >Patch applied back to PG 10. Thanks.
> >
> > This feature is new in PG 14, so backpatching further than that doesn't make
> > sense.
>
> Even an sql_body function should override search_path, because it may call
> other code that reacts to search_path. Separately, the new sentence is near
> the start of a section that addresses more than just search_path. The section
> ends with the "revoke the default PUBLIC privileges" topic, which is no less
> relevant to sql_body functions.
>
> Documentation needn't explain cases that make a best practice optional, and it
> should explain only valuable ones. Omitting search_path on sql_body SECURITY
> DEFINER functions isn't that valuable. If it were valuable, the patch's
> sentence gives too little detail for the reader to decide what's safe for a
> given function. I think this section should not attempt such detail. It's
> enough to give the best practice, as the documentation did before this change.

Agreed, patch reverted in all branches. Thanks for the feedback.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson