On Wed, 26 Mar 2025, 11:10 Andy Fan, <zhihuifan1...@163.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 > >
