Re: [PATCH] postgres_fdw connection caching - cause remote sessions linger till the local session exit

On 2021/01/25 18:13, Bharath Rupireddy wrote:
> On Mon, Jan 25, 2021 at 1:20 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>>> Yeah, connections can be discarded by non-super users using
>>> postgres_fdw_disconnect_all and postgres_fdw_disconnect. Given the
>>> fact that a non-super user requires a password to access foreign
>>> tables [1], IMO a non-super user changing something related to a super
>>> user makes no sense at all. If okay, we can have a check in
>>> disconnect_cached_connections something like below:
>>
>> Also like pg_terminate_backend(), we should disallow non-superuser to disconnect the connections established by other non-superuser if the requesting user is not a member of the other? Or that's overkill because the target to discard is just a connection and it can be established again if necessary?
>
> Yes, if required backends can establish the connection again. But my
> worry is this - a non-super user disconnecting all or a given
> connection created by a super user?

Yes, I was also worried about that. But I found that there are other similar cases, for example,

- a cursor that superuser declared can be closed by non-superuser (set by SET ROLE or SET SESSION AUTHORIZATION) in the same session.
- a prepared statement that superuser created can be deallocated by non-superuser in the same session.

This makes me think that it's OK even for non-superuser to disconnect the connections established by superuser in the same session. For now I've not found any real security issue by doing that yet. Thought? Am I missing something?

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION