On Tue, Mar 15, 2016 at 5:21 PM, Oskari Saarenmaa <o...@ohmu.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
