Show dropped users' backends in pg_stat_activity

From: Oskari Saarenmaa <os(at)ohmu(dot)fi>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Show dropped users' backends in pg_stat_activity
Date: 2016-03-15 21:21:28
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

/ Oskari

Attachment Content-Type Size
0001-pg_stat_activity-display-backends-for-dropped-roles.patch text/x-patch 4.0 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2016-03-15 21:34:37 Re: Combining Aggregates
Previous Message Corey Huinker 2016-03-15 21:10:50 Re: Soliciting Feedback on Improving Server-Side Programming Documentation