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