Re: Show dropped users' backends in pg_stat_activity

<div dir="ltr">On Tue, Mar 15, 2016 at 5:21 PM, Oskari Saarenmaa <<a href="mailto:os@ohmu.fi">os@ohmu.fi</a>>
wrote:<br/>> I was looking into some issues we recently had when dropping db users and<br />> was surprised to
seethat dropped users' sessions and transactions continue<br />> to work after the role is dropped.<br />><br
/>>Since dropping a role requires dropping all grants it has (using DROP OWNED<br />> BY ...) the dropped role
can'tstart new transactions that do a whole lot<br />> unless there are objects with access granted to PUBLIC, but
anyrunning<br />> transactions remain running and can write to the database. They can also<br />> hold locks
whichinterfere with other backends without showing up in most<br />> activity or lock monitoring tools as they won't
appearin pg_stat_activity.<br />><br />> IMO any open sessions for a dropped user should be automatically
terminated<br/>> when the role is dropped, but that would probably be a bigger change so<br />> attached a
proposedpatch for using left joins in pg_stat_activity and<br />> pg_stat_replication to show activity by dropped
roles.<br/><br />Gee, I would have expected the DROP to be blocked until the user disconnected, like we do for DROP
DATABASE.<br/><br />-- <br />Robert Haas<br />EnterpriseDB: <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br/>The Enterprise PostgreSQL Company<br /></div>