I think the article is wrong on assiming and calling out that RLS is only useful if you use only the DB roles for security mechanism.
When you create a RLS policy you specify a predicate (the USING and WITH CHECK parts) that is checked for each accessed row (read or write). The predicate is not in any way restricted to refer to a DB role it can compare for example a field with a parameter variable.
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
I'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:
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.
Sure, 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.
When you create a RLS policy you specify a predicate (the USING and WITH CHECK parts) that is checked for each accessed row (read or write). The predicate is not in any way restricted to refer to a DB role it can compare for example a field with a parameter variable.
EDIT: Here is a gist how to not use it without roles for the permissions: https://gist.github.com/luben/4ab60b0dbda66ecf4b6601b88c8522...