4 ms·

Postgres: Abusing Security Definer Functions

52 points by lhenk 7y ago

mjevans 7y ago
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#FUNCTIONS-STRING-FORMAT https://www.postgresql.org/docs/9.6/functions-string.html#FU...
- taffer 7y ago
  The OP query was static SQL, you changed it to dynamic SQL. How is that supposed to be better?
lixtra 7y ago
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.html 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.
theamk 7y ago
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.

52 points by lhenk 7y ago

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#FUNCTIONS-STRING-FORMAT https://www.postgresql.org/docs/9.6/functions-string.html#FU...

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

lixtra 7y ago

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

theamk 7y ago

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.