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

Fixing RT Users

(or… how Tim got lost in RT and eventually found his way out again)

The Problem

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.

Some Discoveries

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.

The Path of an Email Message

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?

The Cause of the Rejection

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?

Digging in the Database

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)

Solutions

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.

Fixing the Accounts

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.