DROP ROLE: how to detect active sessions?

From: Ken Lalonde <kenl(at)gap(dot)ca>
To: pgsql-admin(at)postgresql(dot)org
Subject: DROP ROLE: how to detect active sessions?
Date: 2010-10-21 18:04:11
Message-ID: 61A975CD-61CC-40CF-87CA-46BEEC1CEEAF@gap.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2010-10-21 18:18:52 Re: DROP ROLE: how to detect active sessions?
Previous Message Vishnu S. 2010-10-21 04:02:15 PostgreSQL Replication Using slony [Rejoining a node after failover]