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:

http://www.postgresql.org/message-id/[email protected]...

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.

I was hoping for something like mssql's "EXECUTE AS... NO REVERT" where reverting back to the previous role can be prevented.

Or in UNIX terms, this is simply "sudo -u". Seems like something that would be pretty useful.

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.