Re: Allowing to create LEAKPROOF functions to non-superuser

On Sun, Apr 25, 2021 at 02:40:54PM -0400, Stephen Frost wrote:
> * Noah Misch (noah(at)leadboat(dot)com) wrote:
> > On Mon, Apr 19, 2021 at 05:38:43PM -0400, Stephen Frost wrote:
> > > > > > On Fri, Apr 16, 2021 at 3:57 AM Noah Misch <noah(at)leadboat(dot)com> wrote:
> > > > > >> Hence, I do find it reasonable to let pg_read_all_data be sufficient for
> > > > > >> setting LEAKPROOF.
> >
> > > I'm not really sure that attaching it to pg_read_all_data makes sense..
> > >
> > > In general, I've been frustrated by the places where we lump privileges
> > > together rather than having a way to distinctly GRANT capabilities
> > > independently- that's more-or-less exactly what lead me to work on
> > > implementing the role system in the first place, and later the
> > > predefined roles.
> >
> > This would be no more lumpy than e.g. pg_read_all_stats. However, I could
> > live with a separate pg_change_leakproof (or whatever name).
>
> There's been already some disagreements about pg_read_all_stats, so I
> don't think that is actually a good model to look at.
>
> I have doubts about users generally being able to write actually
> leakproof functions (this case being an example of someone writing a
> function that certainly wasn't leakproof but marking it as such
> anyway...), though I suppose it's unlikely that it's any worse than the
> cases of people writing SECURITY DEFINER functions that aren't careful
> enough, of which I've seen plenty of.

Making "it's hard to do well" imply "only superusers get to try" doesn't
mitigate a risk; it multiplies risks.

> I would think the role/capability would be 'pg_mark_function_leakproof'
> or similar though, and allow a user who had that role to either create
> leakproof functions (provided they have access to create the function in
> the first place) or to mark an existing function as leakproof (but
> requiring them to be a member of the role which owns the function).

That's fine.