On Tue, Mar 15, 2016 at 5:21 PM, Oskari Saarenmaa <
os@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.comThe Enterprise PostgreSQL Company
