Re: Fixing insecure security definer functions

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> I would like to fix this for 8.3. I don't have a patch yet but want
> to get buy-in on a design before feature freeze. I propose the
> following, fully-backward-compatible design:
[...]
> Comments?

In talking w/ Jeff Davis on IRC today, another thought occurred to us at
about the same time when thinking about this PATH option to CREATE
FUNCTION in relation to packages (in this specific case, PostGIS).

It would be useful to have a function which could be passed a relative
(to the caller's search path) object name and would return the fully
qualified name of that object. In this way, functions could be written
which take relative arguments from the user but *only* those explicitly
checked for.

ie:

CREATE FUNCTION myfunc(text) RETURNS NULL AS
$_$
declate
relative_name alias for $1;
full_name text;
myval int;

begin;
full_name := pg_getfullpath(relative_name);

myval := select val from full_name;

end;
$_$
WITH PATH 'pkg_schema,pg_catalog'
LANG 'plpgsql';

Personally, I really like this idea and it'd handle the specific
use-cases I was expecting to have to use 'PATH NONE' for, but in a much
better, cleaner and clearer way.

On the flip side, I'm not sure how easy that's going to be to get given
the implementation you were describing...

Thanks,

Stephen