Re: Fixing insecure security definer functions

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > The 'special' bit here is that pg_getfullpath() would work relative to
> > the caller's search_path even inside of a function which has its 'PATH'
> > set.
>
> Eeek.  *Which* caller's search_path?  The string you're handed might've
> come from multiple levels up.

I would say the outer-most.  If people inbetween want to mess with
things, let them qualify it before handing it down.  Clearly, an
already-qualified object would be left alone.

> There might be some point in allowing the caller itself to fully qualify
> the name (before passing it down) with more ease than now.  We have
> regclass and so forth, but those make a point of stripping schema
> qualification when it's "unnecessary" according to the current search
> path.  And yet on the third hand --- how often would it be the case that
> this was an issue and yet the caller doesn't know which schema it has in
> mind?

At least at the moment in our application code the search_path is set
quite far apart from the function call.  Additionally, we depend on the
fact that we can set a multi-schema search_path with a specific order
and have the correct thing happen.  A function which qualified an object
based on the current search_path would probably work for us in this
application but seems quite counter-intuitive to a user who is calling
functions by hand (for whatever reason).

ie:

select error_scan(pg_getfullname('default_error_list'));

vs.

select error_scan('default_error_list');

As a user, it's pretty ingrained that unqualified table names follow the
current search_path and having to explicitly qualify tables when passing
them to functions (with a helper function or not) just comes across as
broken.
Thanks,
    Stephen