Thread: DROP ROLE: how to detect active sessions?
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
Ken Lalonde <kenl@gap.ca> writes:
> Is there any way to determine if a given role has any active sessions?
Not if you're using SET ROLE. pg_stat_activity will show you the
login role names for active sessions. Do you really need SET ROLE
rather than logging in with the appropriate username?
There are going to be race conditions in any case, of course.
Probably the best solution is to just not try to drop roles
except when the system is idle.
regards, tom lane
This is a web app, so the username is unknown until the user actually logs in. It would be ideal if pg_stat_activity contained the current role. Until then, I'll go with your second recommendation. Thanks for such a quick and useful reply. Ken On 2010-10-21, at 2:18 PM, Tom Lane wrote: > Ken Lalonde <kenl@gap.ca> writes: >> Is there any way to determine if a given role has any active sessions? > > Not if you're using SET ROLE. pg_stat_activity will show you the > login role names for active sessions. Do you really need SET ROLE > rather than logging in with the appropriate username? > > There are going to be race conditions in any case, of course. > Probably the best solution is to just not try to drop roles > except when the system is idle. > > regards, tom lane