Hi all
I'm working on improving my application auditing and I've run into an interesting challenge.
I need to obtain the role that was active at the time a SECURITY DEFINER audit trigger was called, ie 'x' in the sequence:
SET ROLE x;
SELECT some_security_definer_function(...);
I know I can get the login role with the "session_user" built-in information pseudo-function. Usually I'd get the active role with the current_role and current_user pseudo-functions, but their values change to reflect the active role within a SECURITY DEFINER function.
I can obtain it with a non-security-definer trigger that calls a security definer audit function, but that makes it a _lot_ harder (if it's possible at all) to stop the user producing bogus audit events.
Ideas? Is there any way to "look up the stack" of roles, or get the role that was active just before a security definer function was called?
Along similar lines I'm also interested in a way to find out the context of the statement that caused a trigger invocation. I can get the top level query with "SELECT current_query()" ... but if the trigger was invoked via, say, an INSERT in another trigger or a function, is there any way to get that contextual info from within PL/PgSQL?
I've read:
http://www.postgresql.org/docs/9.1/static/plpgsql.html http://www.postgresql.org/docs/9.1/static/functions-info.html Neither of these are that important, they're more nice-to-haves, it's just bugging me that I can't work out how to do them.
--
Craig Ringer
POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/