Agent M wrote:
Sorry, but you misunderstand- nowhere am I interested in the role's
password. My previous suggestion was to add a password to set session
authorization itself so that if the authorization were to be reset, it
would need to be done with that password; the password itself could be
machine-generated. It it would merely allow a secure sandbox to be
established between:
SET SESSION AUTHORIZATION somerole WITH PASSWORD 'abc';
--arbitrary SQL run as somerole
RESET SESSION AUTHORIZATION; --fails- requires password
RESET SESSION AUTHORIZATION WITH PASSWORD 'pass'; --fails
RESET SESSION AUTHORIZATION WITH PASSWORD 'abc'; --succeeds- we are done
with this role
Why don't you just use "SET SESSION AUTHORIZATION somerole", and then scan
the to-be-executel sql scripts for any occurence of "reset session authorization",
and ignore the script it matches.
Of course you'd need to be a bit carefull to catch all syntactially valid
variations (like ReSeT SeSsIoN AuToRiZaTiOn), but that should be doable.
If you design your "matched" carefully, the only way to defeat that protection
would be to wrap the "reset session authorization" command in a function, which
I believe is not possible.
greetings, Florian Pflug
