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/
