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.
So, I am assuming you are running PostgreSQL with some private modifications, where you use this worker pool approach to speed up query execution? If so, here is my suggestion: do not use SET ROLE design. Currently in PG, if you did InitPostgres(...) under superuser, there is no way back. I'm pretty sure that there always will be a way to fool postgres and restore superuser access after SET ROLE, even if you forbit RESET command. There was just so many CVE s about this issue, that I don't this it is generally avoidable. The secure solution here need to do a major rework of how it is currently done, no fast path here
(b). Is there any other ways to allow different user with the same
database sharing the same connection? Current "SET ROLE x" is exciting
but "RESET SESSION AUTHORIZATION" is dispointing.
--
Best Regards
Andy Fan
