The example would be even better if it used the built in functions to correctly escape and cast input types.

CREATE FUNCTION public.realplusplus(integer) RETURNS integer LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS 'RETURN EXECUTE format(''SELECT %L + 1'', $1)';

However the search path limitation is still required due to the ability to over-ride basic operators; something that I consider an anti-feature from a security standpoint since it does not LOOK to the casual observer that an overridable function exists.

https://www.postgresql.org/docs/9.6/functions-string.html#FU...

Interesting, this sounds very much like SUID binaries in Unixes.

People want them, but there are so many things to worry about -- LD_PRELOAD, PATH, untrusted input, file open races.

I am surprised that postgres is still vulnerable to to those things. I'd think it is natural that those scripts should ignore "search path", just like Perl requires user-specified PATH in the taint mode, or how Linux SUID binaries ignore LD_PRELOAD variable.

This reminds me of the LD_PRELOAD security measure [1]. However the solution there, to always set it to a sane default, appears superior. Can't you set the search path for SECURITY DEFINER to a saner default unless specified?

[1] https://manpages.debian.org/wheezy/manpages/ld-linux.so.8.en...

    LD_PRELOAD
        A whitespace-separated list of additional, 
        user-specified, ELF shared libraries to be 
        loaded before all others. This can be used
        to selectively override functions in other
        shared libraries. For setuid/setgid ELF
        binaries, only libraries in the standard
        search directories that are also setgid
        will be loaded.