3 ms·
This is great for systems like ours that have thousands of 'users' and setting up Roles is a pain. I do see an issue in that if an sql injection is found, then
by dsp1234 11y ago
This is great for systems like ours that have thousands of 'users' and setting up Roles is a pain.
I do see an issue in that if an sql injection is found, then it's trivial for the attacker to use set_foo or set session themselves.
Do you know if it is possible to run to get the system to a point where the initial connection role doesn't have permission to 'set session' itself, but does have permission to run set_foo. Where set_foo can set the session, then set a role that does not have access to execute set_foo again.
Said differently, could this be adapted so that:
1.) at the beginning of a connection, the session is unset
2.) the only way to set the session is via function
3.) once the function has been called once, it cannot be called again on the same connection
- Jweb_Guru 11y agoI'm pretty sure it's not possible to prevent a role from setting session parameters. I believe it is not possible to SET ROLE or SET SESSION AUTHORIZATION with code executed within a SECURITY DEFINER function (which is what you're asking to do), though as Tom Lane points out one shouldn't rely on that: http://www.postgresql.org/message-id/10703.1417480773@sss.pgh.pa.us http://www.postgresql.org/message-id/10703.1417480773@sss.pg... Given arbitrary SQLi, it's hard to see how one can do better than setting up an untrusted sandbox like that and executing your untrusted SQL there, but being able to prevent setting a session parameter would still be useful within that context.
- badalex 11y agoSure, use a language that has access to some kind of shared global variable. For example %_SHARED in plperl: Something like: CREATE OR REPLACE FUNCTION set_foo(name text) returns void as $$ my $name = shift; die "set_foo() has already been called" if ($_SHARED{'set_foo'}); $_SHARED{'set_foo'} = $name; $$ LANGUAGE plperl; CREATE OR REPLACE FUNCTION get_foo() returns text as $$ my $name = shift; return $_SHARED{'set_foo'} || 'nobody'; $$ It would be fairly trivial to extend that to support calling set_foo() once per transaction by checking against txid_current(). But if users can create plperl functions and the worry is sql injection-- It should be easy to write the same thing in C.