On Apr 22, 2006, at 1:13 PM, Florian G. Pflug wrote:
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.
Unfortunately, it is possible:
agentm=# CREATE OR REPLACE FUNCTION testacl() RETURNS void AS $$ RESET
SESSION AUTHORIZATION; $$ LANGUAGE SQL;
CREATE FUNCTION
agentm=# select current_user;
current_user
--------------
agentm
(1 row)
agentm=# set session authorization test;
SET
agentm=> select current_user;
current_user
--------------
test
(1 row)
agentm=> select testacl();
testacl
---------
(1 row)
agentm=# select current_user;
current_user
--------------
agentm
(1 row)
So, currently, there is a security limitation in postgresql which
effectively prohibits switching roles midstream unless you can control
the statements of that role, i.e. there is no sandbox feature
available. (Such a feature would also be great for pooled connections,
but that has already been discussed as well.)
-M
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@xxxxxxxxxxxxxxxxxxxxx
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
