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
>From 4632aa09fe82d80e378123ca46fdf8aecdda795f Mon Sep 17 00:00:00 2001
From: Oskari Saarenmaa <o...@ohmu.fi>
Date: Mon, 14 Mar 2016 18:34:24 +0200
Subject: [PATCH] pg_stat_activity: display backends for dropped roles
---
src/backend/catalog/system_views.sql | 13 ++++++-------
src/test/regress/expected/rules.out | 14 ++++++--------
2 files changed, 12 insertions(+), 15 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 84aa061..e0b583e 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -642,9 +642,9 @@ CREATE VIEW pg_stat_activity AS
S.backend_xid,
s.backend_xmin,
S.query
- FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
- WHERE S.datid = D.oid AND
- S.usesysid = U.oid;
+ FROM pg_stat_get_activity(NULL) AS S
+ JOIN pg_database AS D ON (S.datid = D.oid)
+ LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
CREATE VIEW pg_stat_replication AS
SELECT
@@ -664,10 +664,9 @@ CREATE VIEW pg_stat_replication AS
W.replay_location,
W.sync_priority,
W.sync_state
- FROM pg_stat_get_activity(NULL) AS S, pg_authid U,
- pg_stat_get_wal_senders() AS W
- WHERE S.usesysid = U.oid AND
- S.pid = W.pid;
+ FROM pg_stat_get_activity(NULL) AS S
+ JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid)
+ LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
CREATE VIEW pg_stat_wal_receiver AS
SELECT
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 22ea06c..54d7a7b 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1656,10 +1656,9 @@ pg_stat_activity| SELECT s.datid,
s.backend_xid,
s.backend_xmin,
s.query
- FROM pg_database d,
- pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn),
- pg_authid u
- WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
+ FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn)
+ JOIN pg_database d ON ((s.datid = d.oid)))
+ LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
pg_stat_all_indexes| SELECT c.oid AS relid,
i.oid AS indexrelid,
n.nspname AS schemaname,
@@ -1763,10 +1762,9 @@ pg_stat_replication| SELECT s.pid,
w.replay_location,
w.sync_priority,
w.sync_state
- FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn),
- pg_authid u,
- pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
- WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
+ FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn)
+ JOIN pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) ON ((s.pid = w.pid)))
+ LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
pg_stat_ssl| SELECT s.pid,
s.ssl,
s.sslversion AS version,
--
2.5.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
