Re: a pool for parallel worker

Andy Fan <zhihuifan1213(at)163(dot)com> writes:

> Hi,
>>> The boring thing for the pool is it is [dbid + userId] based, which
>>> I mean if the dbid or userId is different with the connection in pool,
>>> they can't be reused. To reduce the effect of UserId, I think if we can
>>> start the pool with a superuser and then switch the user information
>>> with 'SET ROLE xxx'. and the pool can be created lazily.
>>
>> I don't think this is secure. Currently, if your postgresql process
>> had started under superuser role, there is no way to undo that.
>> Consider a worker in a pool running a user query, which uses UDF. In
>> this UDF, one can simply RESET SESSION AUTHORIZATION and process with
>> anything under superuser rights.
>
> oh.. yes! "RESET SESSION AUTHORIZATION" does the very bad thing for
> me. Per my testing, UDF is not essential, just a sql command can unset
> the role. I am not sure why do we design like this. Currently I want
> some knowledge:
>
> (a). Is there any well known "RESET SESSION AUTHORIZATION" usage. I
> didn't realize this command before. and if it is not common used, I even
> want to forbid this command in our internal version.

Besides the "RESET SESSION AUTHORIZATION", "SET ROLE x" does some bad
thing as well. e.g.

=# set role x; -- as superuser.
=> set role y; -- as role x. it switch to role y easily.

I can understand why we need the above behavior now. Just that I don't
want to discard this goal(sharing the same connection to the same
database amongo the different users) so quickly. I hope we have other
clean method to do it. If no, do you think will disable"re/reset
session authorization" "set role x" in the normal backend, and only
allowing them in some internal connections (like the connection in
parallel worker pool) work.

For now, it is unclear for me what is the purpose for "set role x"
command due to it can be undo so easily.

--
Best Regards
Andy Fan