Re: BUG #17487: Parallel execution fails when original user is removed

Hey Tom,
Thanks for the response. I agree that this implementation looks shady, and
I would rather have postgres to disallow role drops in such situations.
Currently, the same behaviour can be observed even without the "proxy" role
that I used in my example. I think that would be consistent with the way
SQLServer/Oracle deals with it - not allowing the context to be removed
while in use.

Kirill.

On Thu, May 19, 2022 at 1:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > We're using temporary credentials to access the DB. The temporary
> > credentials are granted a role and automatically become that role on
> > connect. As soon as temporary credential is removed while the connection
> is
> > still alive, any regular query would continue to work, however, a
> parallel
> > query would cause an error:
> > ERROR: role with OID XXXXXX does not exist
> > CONTEXT: parallel worker
>
> I don't think this is particularly a bug. You are relying on an
> undocumented and un-guaranteed implementation artifact that it's
> possible to drop a role at all while it's still in use in some
> session. If we were to make any effort in this area, the end result
> would certainly be that the DROP ROLE would be rejected, not that the
> parallel query case would start to work. One reason why we'd want
> to go in that direction is that there are almost certainly many other
> cases where such an "orphaned" session misbehaves.
>
> (Doing that has in fact been discussed, but nothing's been done
> about it so far AFAIK.)
>
> regards, tom lane
>