Note: This web site is only kept up to date for OSG Software 1.2 (VDT 2.0.0). If you are looking for information for the most recent release, the RPM-based OSG Software 3.0, please see the OSG documentation web site
(or… how Tim got lost in RT and eventually found his way out again)
In early June, 2006, Alain discovered a problem with our RT instance: If he
sent email from alain.roy@pobox.com, RT rejected the email. This
happened whether he sent the email to vdt-support@ivdgl.org or
to vdt-support@cs.wisc.edu. The rejection email included minimal
diagnostic text:
From: vdt-support@ivdgl.org To: alain.roy@pobox.com Subject: Ticket creation failed No permission to create tickets in the queue 'vdt-support'
Initially, we were unable to find other email addresses that exhibited the same problem, but we were concerned that RT might reject some emails out-of-hand, and we would never be the wiser.
I learned a lot about how RT handles tickets. There is too much to present here, but I will go over a few of the highlights. Maybe someday this will save someone hours of digging.
What happens to an email sent to vdt-support@ivdgl.org? Well,
first it goes through the iVDGL mail system, where it picks up
a Precedence email header. But that does not matter for this
story. When it gets to us, the email is processed by the Perl script
at
/p/condor/home/rust/scripts/vdt-support
Update (February 2012): Apparently for some time, the actual script(s) and symlinks are located in
/p/condor/public/aliases
It is checked for spam using Spam Assassin and if it’s OK, the email message is piped into an RT Perl script with
/p/vdt/workspace/rt/bin/rt-mailgate --queue $queue --action $action --url http://vdt.cs.wisc.edu/rt
Update: As of August, 2006, vdt-support also edits the subject line of emails that come from the GOC, have an ISSUE=N in them, and don't have a [vdt-support #N] in them. It does this by querying rt. See Ticket 2064 for more details.
The rt-mailgate script does very little except for passing the
email, queue, and action to RT using HTTP via the magic URL:
http://vdt.cs.wisc.edu/rt/REST/1.0/NoAuth/mail-gateway
This RT/Mason page
(/p/vdt/public/html/rt/REST/1.0/NoAuth/mail-gateway) really just
hands off the message to the innards of RT itself with this line of Perl code:
my ( $status, $error, $Ticket ) = RT::Interface::Email::Gateway(\%ARGS);
The Gateway method is found in
/p/vdt/workspace/rt/lib/RT/Interface/Email.pm
The method validates its arguments, parses the email message, and decides
whether the message is a new ticket or a reply to an existing ticket (i.e.,
has a ticket number in the Subject: header). Then,
the From: header is used to try to find an existing RT user: the
email address is parsed out and searched for in the emailaddress
field and, if not found, in the name field. If there is no
match, a new user account is created. Then, the user is checked agaist the
authentication tables to see if they are allowed to use RT.
Next are a bunch of steps to check for bad things like authentication failures and email loops (within RT). If the message makes it through all the checks and if it’s a new ticket, the ticket itself is created:
my ( $id, $Transaction, $ErrStr ) = $Ticket->Create(
Queue => $SystemQueueObj->Id,
Subject => $Subject,
Requestor => \@Requestors,
Cc => \@Cc,
MIMEObj => $Message
);
if ( $id == 0 ) {
MailError(
To => $ErrorsTo,
Subject => "Ticket creation failed",
Explanation => $ErrStr,
MIMEObj => $Message
);
$RT::Logger->error("Create failed: $id / $Transaction / $ErrStr ");
return ( 0, "Ticket creation failed", $Ticket );
}
The call to MailError is what generated our rejection message, so
it was $Ticket->Create() that failed. Why?
Most of the interesting code for the Ticket class is in
/p/vdt/workspace/rt/lib/RT/Ticket_Overlay.pm
(The regular Ticket.pm code is all about database access and
basic attribute use.) In the Create method, there are checks for
valid arguments and a valid queue in which to create the ticket, and then
#Now that we have a queue, Check the ACLS
unless (
$self->CurrentUser->HasRight(
Right => 'CreateTicket',
Object => $QueueObj
)
)
{
return (
0, 0,
$self->loc( "No permission to create tickets in the queue '[_1]'", $QueueObj->Name));
}
So there is our problem: The user — identified from the
email’s From: header — does not have permissions
to create tickets in the vdt-support queue. But why not?
I looked for Alain’s user record in the users table and
found this:
vdt_tickets=> SELECT id, name, emailaddress FROM users WHERE emailaddress LIKE '%pobox.com%';
id | name | emailaddress
-------+---------------------------------------+---------------------------------------
9 | alain.roy@pobox.com |
22888 | Alain Aslag Roy <alain.roy@pobox.com> | Alain Aslag Roy <alain.roy@pobox.com>
(2 rows)
Because of the email address extraction, the search hits the first entry, user ID = 9. I believe the latter entry was created as part of the RUST ticket conversion, but I am not 100% sure. However, it is worth noting that the first time RT sees an email address, it does split it correctly into a name and email address, so seeing the unsplit format in both fields is suspect.
In the RT system, each user is associated with a principal. A quick query yields the principal ID associated with user ID = 9:
vdt_tickets=> SELECT * FROM principals WHERE principaltype = 'User' and objectid = 9; id | principaltype | objectid | disabled -----+---------------+----------+---------- 231 | User | 9 | 0 (1 row)
The next part is tricky. The Principals::HasRight method runs
two complicated SQL queries to see whether the given principal ID (231 in
Alain’s case) has the given rights (CreateTicket here).
The first query checks for the queue (vdt-support is queue 3) and
rights:
SELECT
acl.id
FROM
acl, groups, principals, cachedgroupmembers
WHERE
( acl.rightname = 'SuperUser' OR acl.rightname = 'CreateTicket' )
AND principals.disabled = 0
AND cachedgroupmembers.disabled = 0
AND principals.id = groups.id
AND principals.id = cachedgroupmembers.groupid
AND cachedgroupmembers.memberid = '231'
AND ( acl.objecttype = 'RT::System'
OR ( acl.objecttype = 'RT::Queue' AND acl.objectid = '3' ))
AND (( acl.principalid = principals.id
AND acl.principaltype = 'Group'
AND ( groups.domain = 'SystemInternal'
OR groups.domain = 'UserDefined'
OR groups.domain = 'ACLEquivalence'
OR groups.domain = 'Personal' )))
LIMIT 1;
The second query is similar and checks for queue-specific role rights (I think).
The problem is that principal ID 231 does not belong to any groups and thus cannot get group rights to do anything:
vdt_tickets=> SELECT * FROM cachedgroupmembers WHERE memberid = 231; id | groupid | memberid | via | immediateparentid | disabled ----+---------+----------+-----+-------------------+---------- (0 rows)
Our primary concern was to figure out if there were other email addresses that would fail just like Alain’s. This question is tricky to answer. I will outline the process I used for group authorization; the role-based authorization steps are similar.
First, I modified the groups query to simply show all of the groups that are authorized to create tickets. Here is what I ended up with:
SELECT
DISTINCT groups.id
FROM
acl, groups, principals, cachedgroupmembers
WHERE
( acl.rightname = 'SuperUser' OR acl.rightname = 'CreateTicket' )
AND principals.disabled = 0
AND cachedgroupmembers.disabled = 0
AND principals.id = groups.id
AND principals.id = cachedgroupmembers.groupid
AND ( acl.objecttype = 'RT::System'
OR ( acl.objecttype = 'RT::Queue' AND acl.objectid = '3' ))
AND (( acl.principalid = principals.id
AND acl.principaltype = 'Group'
AND ( groups.domain = 'SystemInternal'
OR groups.domain = 'UserDefined'
OR groups.domain = 'ACLEquivalence'
OR groups.domain = 'Personal' )));
This query and the other big ones I used are in ~cat/rt on AFS.
Anyway, the query returned:
id ---- 2 3 13 28 (4 rows)
That result looked fairly good — here are the full group entries
(user 1 is the RT_System account and user 12 is the
RT root account):
vdt_tickets=> SELECT * FROM groups WHERE id IN (2, 3, 13, 28); id | name | description | domain | type | instance ----+-----------+------------------------------+----------------+-----------+---------- 2 | User 1 | ACL equiv. for user 1 | ACLEquivalence | UserEquiv | 1 3 | | Pseudogroup for internal use | SystemInternal | Everyone | 0 13 | User 12 | ACL equiv. for user 12 | ACLEquivalence | UserEquiv | 12 28 | VDT Staff | VDT staffers at Wisconsin | UserDefined | | 0 (4 rows)
Next, I wanted to see every user account that was not in any of those groups. Mmm… nested SELECTs.
SELECT
id, name, emailaddress
FROM
users
WHERE
users.id NOT IN (
SELECT
DISTINCT users.id
FROM
users
JOIN principals ON users.id = principals.objectid
JOIN cachedgroupmembers ON principals.id = cachedgroupmembers.memberid
WHERE
principals.principaltype = 'User'
AND cachedgroupmembers.groupid IN (2, 3, 13, 28)
)
ORDER BY
id;
Fortunately, this produced a small list, especially considering that we only care about users identified by their email addresses:
id | name | emailaddress ----+--------------------------+-------------- 2 | ckireyev | 3 | pfc | 4 | adesmet | 5 | parag | 6 | weber | 7 | roy@cs.wisc.edu | 8 | oxana.smirnova@cern.ch | 9 | alain.roy@pobox.com | 11 | voeckler@cs.uchicago.edu | (6 rows)
Seeing Alain’s CS address in there reminded me to cross off all entries
in which an email address in the name field occurred in another
record in the emailaddress field. I did that manually and was
left with just the last three addresses. Going through a similar process with
the roles query resulted in no addresses at all.
What to do about these three accounts? I did not want to delete them, for fear of messing up existing tickets or other records. And a bit of further poking around turned up places where the accounts were being used. For example, this group of queries can be used to dump out places where an account is referenced:
SELECT * FROM users WHERE id = 9; SELECT * FROM principals WHERE id = 231; SELECT * FROM cachedgroupmembers WHERE memberid = 231; SELECT * FROM groupmembers WHERE memberid = 231; SELECT * FROM tickets WHERE owner = 9 OR lastupdatedby = 9 OR creator = 9 ORDER BY id;
My first thought was to modify the users table and modify the
offending email addresses with the users’ names instead. That way, the
next incoming email with the email address would create a new account and all
would be well with existing tickets. I tried this hack on Alain’s
account and it worked, but I wasn’t happy with the results. I wanted to
preserve the existing account and just make it useful again.
The solution I ended up with quite simple: Just add each offending user
account to the Everyone group, which is authorized to do basic
stuff. It takes two INSERTs:
INSERT INTO groupmembers (groupid, memberid) VALUES (3, 228);
INSERT INTO cachedgroupmembers (groupid, memberid, via, immediateparentid, disabled)
VALUES (3, 228, 3, 3, 0);
This solution is a hack, because I do not know exactly what all the fields
in cachedgroupmembers mean, but it seems to work. Alain was able
to send in a new ticket with his pobox.com account and it
attached itself to the old account.