Re: Show dropped users' backends in pg_stat_activity

On Tue, Mar 15, 2016 at 5:21 PM, Oskari Saarenmaa <os(at)ohmu(dot)fi> wrote:
> I was looking into some issues we recently had when dropping db users and
> was surprised to see that dropped users' sessions and transactions
continue
> to work after the role is dropped.
>
> Since dropping a role requires dropping all grants it has (using DROP
OWNED
> BY ...) the dropped role can't start new transactions that do a whole lot
> unless there are objects with access granted to PUBLIC, but any running
> transactions remain running and can write to the database. They can also
> hold locks which interfere with other backends without showing up in most
> activity or lock monitoring tools as they won't appear in
pg_stat_activity.
>
> IMO any open sessions for a dropped user should be automatically
terminated
> when the role is dropped, but that would probably be a bigger change so
> attached a proposed patch for using left joins in pg_stat_activity and
> pg_stat_replication to show activity by dropped roles.

Gee, I would have expected the DROP to be blocked until the user
disconnected, like we do for DROP DATABASE.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company