pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDROP ROLE: how to detect active sessions?
3+ messages / 2 participants
[nested] [flat]
* DROP ROLE: how to detect active sessions?
@ 2010-10-21 18:04 Ken Lalonde <kenl@gap.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Ken Lalonde @ 2010-10-21 18:04 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DROP ROLE: how to detect active sessions?
@ 2010-10-21 18:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ken Lalonde <kenl@gap.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2010-10-21 18:18 UTC (permalink / raw)
To: Ken Lalonde <kenl@gap.ca>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DROP ROLE: how to detect active sessions?
@ 2010-10-21 18:46 Ken Lalonde <kenl@gap.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Ken Lalonde @ 2010-10-21 18:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-10-21 18:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-21 18:04 DROP ROLE: how to detect active sessions? Ken Lalonde <kenl@gap.ca>
2010-10-21 18:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-21 18:46 ` Ken Lalonde <kenl@gap.ca>
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