
Postgres: Abusing Security Definer Functions - lhenk
https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/
======
mjevans
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...](https://www.postgresql.org/docs/9.6/functions-
string.html#FUNCTIONS-STRING-FORMAT)

~~~
taffer
The OP query was static SQL, you changed it to dynamic SQL. How is that
supposed to be better?

------
theamk
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.

------
lixtra
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...](https://manpages.debian.org/wheezy/manpages/ld-
linux.so.8.en.html)

    
    
        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.

