From: Ken Lalonde <kenl@gap.ca>
To: pgsql-admin@postgresql.org
Subject: DROP ROLE: how to detect active sessions?
Date: Thu, 21 Oct 2010 14:04:11 -0400
Message-ID: <61A975CD-61CC-40CF-87CA-46BEEC1CEEAF@gap.ca> (raw)
I need to track db activity by role. All web-related connections to the db use the same user name.
When a user logs in via the web, the application code runs:
SET ROLE "n"
where n is the ID value for the user in the "users" table.
(These numeric roles are created dynamically as needed).
We can then use the value of "current_user" to track activity.
So far so good.
The problem happens when we periodically drop roles for inactive users.
If I run DROP ROLE "n", and there happens to be an active session for that role,
we have a problem: in that session, "select current_user" fails with "invalid role OID: nnnn".
Is there any way to determine if a given role has any active sessions?
Thanks a bunch,
Ken Lalonde
Gap Adventures, Toronto
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kenl@gap.ca
Subject: Re: DROP ROLE: how to detect active sessions?
In-Reply-To: <61A975CD-61CC-40CF-87CA-46BEEC1CEEAF@gap.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox