1277269 - Copy the stage credentials over to the new Heroku stage RDS instance

Status: RESOLVED FIXED

(Tree Management :: Treeherder: Infrastructure, defect, P2)

Description

[Ed Morley [:emorley]]

The new Heroku RDS stage instance was created as a copy of prod in bug 1176486.

Now that replication has been switched off, we need to sync the existing SCL3 instance Hawk credentials over to this new instance, such that on switchover day, all of the existing clients submitting to https://treeherder.allizom.org/api/ can continue to use the same credentials.

Comment 1

[Ed Morley [:emorley]]

So for all fields of the existing credentials apart from owner_id, we can do a straight copy. For owner_id, the ids are different between stage and production, since not everyone has logged into both (or in the same order).

I looked briefly into scripting this, but since there are only 12 clients it's not really worth it.

As such, I initially exported the existing credentials from SCL3 using an owner_id of '8' (my account on both stage and prod):

SELECT
  id,
  client_id,
  secret,
  description,
  authorized,
  created,
  modified,
  '8' as owner_id
FROM treeherder_stage.credentials;

Then imported that into the Heroku stage RDS instance, having truncated the table prior.

The client_id<->email mappings were exported from SCL3 using:

SELECT
  c.client_id,
  u.email
FROM treeherder_stage.credentials as c 
LEFT JOIN treeherder_stage.auth_user as u
ON c.owner_id = u.id;

And then the new ids retrieved from the new stage DB (that's based on prod) using:

SELECT email, id
FROM treeherder.auth_user
WHERE email in (<results from query above>)

I was able to update all but two users - they have only logged into SCL3 stage and not SCL3 prod, so the new RDS DB doesn't have a user account for them. I've left those two with my owner_id for now, and have asked the owners to log in once to the stage heroku app to create their accounts (bug 1268802 comment 7 and bug 1263644 comment 2).

Even without them doing so, the client_id and secret combinations will still work in the meantime.

Comment 2

[Ed Morley [:emorley]]

I've exported the credentials from both old and new stage DB using:

SELECT
  c.id,
  c.client_id,
  c.secret,
  c.description,
  c.authorized,
  c.created,
  c.modified,
  u.email
FROM treeherder.credentials as c 
LEFT JOIN treeherder.auth_user as u
ON c.owner_id = u.id;

(old stage requires a s/treeherder/treeherder_stage/ since using different DB name)

And verified that apart from the owners mentioned in comment 1, everything else is identical.

Comment 3

[Ed Morley [:emorley]]

I've also copied across the new stage credentials added in bug 1281631.

Comment 4

[Ed Morley [:emorley]]

Cam - I've just spotted you added some credentials on SCL3 stage for gaia-taskcluster?

Execute:
> SELECT user.username, log.action_time, log.object_repr, log.change_message
FROM treeherder_stage.django_admin_log as log
LEFT JOIN treeherder_stage.credentials as creds ON log.object_id = creds.id
LEFT JOIN treeherder_stage.auth_user as user ON log.user_id = user.id
WHERE creds.client_id = 'gaia-taskcluster'

+ ------------- + ---------------- + ---------------- + ------------------- +
| username      | action_time      | object_repr      | change_message      |
+ ------------- + ---------------- + ---------------- + ------------------- +
| cdawson       | 2016-06-28 10:45:35 | Credentials object | Changed authorized. |
+ ------------- + ---------------- + ---------------- + ------------------- +
1 rows

I don't remember seeing any bugmail for this?

It's just we need to keep the new Heroku stage instance credentials in sync, and so need to manually add any new ones to Heroku at the same time - but without any bug activity it's harder to keep track.

I've manually added the credentials now (after adjusting Greg's user id to match that in the prod DB copy vs the SCL3 stage one).

Comment 5

[Ed Morley [:emorley]]

Two users had deleted their SCL3 stage credentials, so I've removed those from Heroku stage too. There was one additional row in the SCL3 table from the testing of bug 1303928, which I've removed.

Whilst I was there, I also removed the mozilla-taskcluster credentials from both, since it's no longer used after the switch to Pulse (verified using last 7 days of Insights stats).

The credentials are now in sync again.