Re: Logging conflicted queries on deadlocks

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
>> Here is a patch to log conflicted queries on deadlocks. Queries are dumped
>> at CONTEXT in the same sorting order as DETAIL messages. Those queries are
>> picked from pg_stat_get_backend_activity, as same as pg_stat_activity,
>> so that users cannot see other user's queries.
>
> Applied with revisions. It's a cute idea --- I first thought it
> couldn't work reliably because of race conditions, but actually we
> haven't aborted our own transaction at this point, so everyone else
> involved in the deadlock is still waiting and it should be safe to
> grab their activity strings.

There's no way the other transaction's timeout could fire while we're doing
this is there? Are we still holding the lw locks at this point which would
prevent that?

> One thing that I worried about for a little bit is that you can imagine
> privilege-escalation scenarios. Suppose that the user is allowed to
> call some SECURITY DEFINER function that runs as superuser, and a
> deadlock occurs inside that. As the patch stands, every query involved
> in the deadlock will be reported, which might be undesirable. We could
> make the test use the outermost session user's ID instead of current
> ID, but that might only move the security risks someplace else.
> Thoughts?

Perhaps we should only do this if the current user's ID is the same as the
outermost session user's ID?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!