Re: sudo-like behavior

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(at)themactionfaction(dot)com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬