Re: Show dropped users' backends in pg_stat_activity

I had the same problem and thought similar thing.

At Wed, 16 Mar 2016 11:48:10 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in <16068(dot)1458143290(at)sss(dot)pgh(dot)pa(dot)us>
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > Gee, I would have expected the DROP to be blocked until the user
> > disconnected, like we do for DROP DATABASE.

FWTW, I agree with Robert.

> Making that race-condition-free would require some notion of a lock on
> roles, I think. Seems pretty messy compared to the amount of actual
> value obtained. There are good reasons why you can't have a backend
> running in a nonexistent database; but a backend with a nonexistent
> user OID is not really going to be a problem for anything except
> monitoring queries that fail to use left joins where appropriate.
>
> Even if we maintained some interlock for a backend's login role identity,
> I hardly think it would be practical to e.g. lock during transient SET
> ROLE or security-definer-function-call operations. So it's not like we
> can let the permissions system assume that a role OID being inquired about
> always matches a live entry in pg_authid.

Even if blocking DROPs is not perfect for all cases,
unconditionally allowing to DROP a role still doesn't seem proper
behavior, especially for replication roles. And session logins
seem to me to have enough reason to be treated differently than
disguising as another role using SET ROLE or sec-definer.

The attached patch blocks DROP ROLE for roles that own active
sessions, and on the other hand prevents a session from being
activated if the login role is concurrently dropped.

Oskari's LEFT-Join patch is still desirable.

Is this still pointless?

regards,