Literate Database Example

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   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 1243

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

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

expired 735

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 1417.0000 23.61666667

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-09 05:59:28 14.63916667

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 casey.jones johnny.thunder ravi.shankar ravi.shankar ravi.shankar ravi.shankar johnny.thunder neutron

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 adam.smith 4 casey.jones 2 glance 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 100 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 casey.jones 1

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 169.1000 howard.abrams 2015-04-09 17:48:52 168.9667 ravi.shankar 2015-04-09 16:03:14 274.6000 ravi.shankar 2015-04-09 20:40:05 -2.2500 howard.abrams 2015-04-09 17:48:42 169.1333 ravi.shankar 2015-04-09 17:40:52 176.9667

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….