Re: a pool for parallel worker

On Wed, 26 Mar 2025, 11:10 Andy Fan, <zhihuifan1213(at)163(dot)com> wrote:

>
> 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
>
>