www.howardism.org
Babblings of an aging geek in love with the Absurd, his family, and his own hubris.... oh, and Lisp.

Literate Database Example

MySQL Analysis

Not knowing anything about the token properties in the Keystone database structure, I jumped into the database to expose a bit of the schema. What follows is a summary of my exploration as well as some recommendations we can use to ascertain its health.

First, here are the tables associated with the keystone database:

SHOW tables;
Tables_in_keystone
credential
domain
endpoint
group
group_domain_metadata
group_project_metadata
migrate_version
policy
project
role
service
token
trust
trust_role
user
user_domain_metadata
user_group_membership
user_project_metadata

The user table has the following schema:

SHOW columns FROM user;
Field Type Null Key Default Extra
id varchar(64) NO PRI NULL  
name varchar(255) NO   NULL  
extra text YES   NULL  
password varchar(128) YES   NULL  
enabled tinyint(1) YES   NULL  
domain_id varchar(64) NO MUL NULL  
default_project_id varchar(64) YES   NULL  

Each person that connects to the Keystone service is given an entry in the user database. How many have logged in?

SELECT count(*) AS users FROM user;
users
40

Keep in mind that some of these entries are for the other OpenStack components and our CI accounts:

SELECT COUNT(*) as non_users FROM user WHERE extra LIKE '{"email": ""}';
non_users
21

An active user is given a token during the authentication process, and that record is stored in the token table. Here is its schema:

SHOW columns FROM token;
Field Type Null Key Default Extra
id varchar(64) NO PRI NULL  
expires datetime YES MUL NULL  
extra mediumtext YES   NULL  
valid tinyint(1) NO   NULL 1 or 0
trust_id varchar(64) YES   NULL  
user_id varchar(64) YES   NULL  

How many token entries are there at this time?

SELECT count(*) AS tokens FROM token;
tokens
570

Is the expires timestamp something that can be in the past?

SELECT count(*) AS expired
FROM token
WHERE expires < NOW();
expired
217

Clearly that is a lot of expired tokens. How old is the oldest expired token?

SELECT expires,
 (UNIX_TIMESTAMP(expires) - UNIX_TIMESTAMP(NOW()))/60 AS minutes_ago,
 (UNIX_TIMESTAMP(expires) - UNIX_TIMESTAMP(NOW()))/60/60 AS hours_ago
FROM token
ORDER BY expires DESC
LIMIT 1
expires minutes_ago hours_ago
2015-04-10 20:14:49 1435.3667 23.92277778

That is almost 24 hours ago. Is that our policy? Actually, it is indeed a configurable policy. The default value is set to 24 hours, in case long running stories cache that token. If we think data storage is still an issue, we could lower that policy in order to force the excise of those expire tokens. If a system does cache it, the token can be renewed, or the script could be easily reset.

What is the token duration? According to the database entries, this would be the value with the greatest expires entry:

SELECT expires,
 (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(expires))/60/60 AS hours
FROM token
ORDER BY expires ASC
LIMIT 1
expires hours
2015-04-07 06:23:21 15.44972222

Since we can easily re-new a token, why is that value so large? How many tokens are expiring in the next ten minutes? Are those tokens from normal users or from the other cluster components?

SELECT user.name AS soon_to_expire
FROM token, user
WHERE
   user.id = token.user_id AND
   expires > NOW() AND expires < DATE_ADD(NOW(), INTERVAL 10 MINUTE);
soon_to_expire
johnny.thunder
johnny.thunder
casey.jones

Clearly, this will depend on the activity of our system. We have someone here with two tokens. How many tokens are assigned to active user account?

SELECT user.name, count(token.id) AS tokens
FROM token LEFT JOIN user
ON user.id = token.user_id
GROUP BY token.user_id
ORDER BY tokens DESC
name tokens
neutron 498
jazz.ci 214
johnny.thunder 113
wpc-buildadm 106
paul.mccartney 89
jazz.ci.gold 68
howard.abrams 49
megane.smith 32
mini.song 29
ravi.shankar 18
guido-ci 12
admin 6
adrian.smith 4
glance 2
casey.jones 2
nova 1

I guess I can see why the neutron account has the most tokens, but normal user accounts seem to have quite a bit. Why? Are they expired?

SELECT user.name, count(token.id) AS expired_tokens
FROM token LEFT JOIN user
ON user.id = token.user_id
WHERE token.expires < NOW()
GROUP BY token.user_id
ORDER BY expired_tokens DESC
name expired_tokens
neutron 247
jazz.ci 102
johnny.thunder 99
paul.mccartney 84
wpc-buildadm 53
howard.abrams 49
jazz.ci.gold 39
megane.smith 32
mini.song 10
ravi.shankar 8
admin 6
adam.smith 4

While it appears that most of the tokens are indeed expired, the non-expired tokens seem to be more than needed. But I guess that is just the OpenStack way.

The token table has a valid field that is either 1 or 0. How many tokens are invalid (equal to zero instead of one)? The OpenStack documentation describes this field as revoked:

SELECT count(*) AS invalid
FROM token
WHERE valid = 0;
invalid
6

Which user has these invalid tokens. Are invalid tokens also expired? No, since the expires field can be either greater or less than the current time:

SELECT name, expires,
   (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(expires))/60 AS minutes_when
FROM token, user
WHERE user.id = token.user_id AND valid = 0;
name expires minutes_when
howard.abrams 2015-04-09 17:48:44 166.2000
howard.abrams 2015-04-09 17:48:52 166.0667
ravi.shankar 2015-04-09 16:03:14 271.7000
ravi.shankar 2015-04-09 20:40:05 -5.1500
howard.abrams 2015-04-09 17:48:42 166.2333
ravi.shankar 2015-04-09 17:40:52 174.0667

The revoked tokens can come about due to actually calling the Delete API on the token (even though I don’t remember doing anything like that with my account):

DELETE: /token/{token_id}

This API sets the valid field of token to false in the database (1). Then the token is now called a revoked token. The code for accessing and managing the revoked tokens are in keystoneclient.middleware.auth_token, so we might want to read the code to see the details.

Note: Token revocation is often a side effect of some other operation. For example, changing a password revokes all tokens issued prior to the password change. Removing a role assignment from a user revokes all tokens that have that role assignment.

Does someone with invalid or revoked tokens also have valid tokens?

SELECT count(*) as howards_tokens
FROM token, user
WHERE  token.user_id = user.id AND valid = 1
  AND  user.name = "howard.abrams"
howards_tokens
46

I guess so….