Re: Show dropped users' backends in pg_stat_activity
Re: Show dropped users' backends in pg_stat_activity
От:
Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Дата:
Re: Show dropped users' backends in pg_stat_activity
От:
Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Дата:
Show dropped users' backends in pg_stat_activity
От:
Oskari Saarenmaa <os@ohmu.fi>
Дата:
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
Re: Show dropped users' backends in pg_stat_activity
От:
Robert Haas <robertmhaas@gmail.com>
Дата:
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.com
The Enterprise PostgreSQL Company
> 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
