Add last_used field to API keys

cloudfoundry · richard-cox · Aug 25, 2020 · Aug 18, 2020 · Aug 19, 2020 · Aug 20, 2020
commit 96248c09ac3673426458c129e9dfe3d81227e2fd
diff --git a/src/jetstream/datastore/20200814140918_ApiKeys.go b/src/jetstream/datastore/20200814140918_ApiKeys.go
@@ -13,6 +13,7 @@ func init() {
 		apiTokenTable += "secret          VARCHAR(36) NOT NULL UNIQUE,"
 		apiTokenTable += "user_guid       VARCHAR(36) NOT NULL,"
 		apiTokenTable += "comment         VARCHAR(255) NOT NULL,"
+		apiTokenTable += "last_used       TIMESTAMP,"
 		apiTokenTable += "PRIMARY KEY (guid) );"
 
 		_, err := txn.Exec(apiTokenTable)

diff --git a/src/jetstream/middleware.go b/src/jetstream/middleware.go
@@ -319,7 +319,7 @@ func (p *portalProxy) apiKeyMiddleware(h echo.HandlerFunc) echo.HandlerFunc {
 	return func(c echo.Context) error {
 		log.Debug("apiKeyMiddleware")
 
-		apiKey, err := getAPIKeyFromHeader(c)
+		apiKeySecret, err := getAPIKeyFromHeader(c)
 		if err != nil {
 			log.Debugf("apiKeyMiddleware: %v", err)
 			return h(c)
@@ -331,26 +331,31 @@ func (p *portalProxy) apiKeyMiddleware(h echo.HandlerFunc) echo.HandlerFunc {
 			return h(c)
 		}
 
-		userID, err := apiKeysRepo.GetAPIKeyUserID(apiKey)
+		apiKey, err := apiKeysRepo.GetAPIKeyBySecret(apiKeySecret)
 		if err != nil {
 			switch {
 			case err == sql.ErrNoRows:
 				log.Debug("apiKeyMiddleware: Invalid API key supplied")
 			default:
-				log.Warnf("apiKeyMiddleware: %v", err)
+				log.Errorf("apiKeyMiddleware: %v", err)
 			}
 
 			return h(c)
 		}
 
 		c.Set(APIKeySkipperContextKey, true)
-		c.Set("user_id", userID)
+		c.Set("user_id", apiKey.UserGUID)
 
 		// some endpoints check not only the context store, but also the contents of the session store
 		sessionValues := make(map[string]interface{})
-		sessionValues["user_id"] = userID
+		sessionValues["user_id"] = apiKey.UserGUID
 		p.setSessionValues(c, sessionValues)
 
+		err = apiKeysRepo.UpdateAPIKeyLastUsed(apiKey.GUID)
+		if err != nil {
+			log.Errorf("apiKeyMiddleware: %v", err)
+		}
+
 		return h(c)
 	}
 }

diff --git a/src/jetstream/repository/apikeys/apikeys.go b/src/jetstream/repository/apikeys/apikeys.go
@@ -5,7 +5,8 @@ import "github.com/cloudfoundry-incubator/stratos/src/jetstream/repository/inter
 // Repository - API keys repository
 type Repository interface {
 	AddAPIKey(userID string, comment string) (*interfaces.APIKey, error)
-	GetAPIKeyUserID(keySecret string) (string, error)
+	GetAPIKeyBySecret(keySecret string) (*interfaces.APIKey, error)
 	ListAPIKeys(userID string) ([]interfaces.APIKey, error)
 	DeleteAPIKey(userGUID string, keyGUID string) error
+	UpdateAPIKeyLastUsed(keyGUID string) error
 }
diff --git a/src/jetstream/repository/apikeys/psql_apikeys.go b/src/jetstream/repository/apikeys/psql_apikeys.go
@@ -3,6 +3,7 @@ package apikeys
 import (
 	"database/sql"
 	"errors"
+	"time"
 
 	"github.com/cloudfoundry-incubator/stratos/src/jetstream/datastore"
 	"github.com/cloudfoundry-incubator/stratos/src/jetstream/repository/interfaces"
@@ -11,9 +12,12 @@ import (
 )
 
 var insertAPIKey = `INSERT INTO api_keys (guid, secret, user_guid, comment) VALUES ($1, $2, $3, $4)`
-var getAPIKeyUserID = `SELECT user_guid FROM api_keys WHERE secret = $1`
-var listAPIKeys = `SELECT guid, user_guid, comment FROM api_keys WHERE user_guid = $1`
+var getAPIKeyBySecret = `SELECT guid, user_guid, comment, last_used FROM api_keys WHERE secret = $1`
+var listAPIKeys = `SELECT guid, user_guid, comment, last_used FROM api_keys WHERE user_guid = $1`
 var deleteAPIKey = `DELETE FROM api_keys WHERE user_guid = $1 AND guid = $2`
+var updateAPIKeyLastUsed = `UPDATE api_keys SET last_used = $1 WHERE guid = $2`
+
+// UpdateAPIKeyLastUsed
 
 // PgsqlAPIKeysRepository - Postgresql-backed API keys repository
 type PgsqlAPIKeysRepository struct {
@@ -30,9 +34,10 @@ func NewPgsqlAPIKeysRepository(dcp *sql.DB) (Repository, error) {
 func InitRepositoryProvider(databaseProvider string) {
 	// Modify the database statements if needed, for the given database type
 	insertAPIKey = datastore.ModifySQLStatement(insertAPIKey, databaseProvider)
-	getAPIKeyUserID = datastore.ModifySQLStatement(getAPIKeyUserID, databaseProvider)
+	getAPIKeyBySecret = datastore.ModifySQLStatement(getAPIKeyBySecret, databaseProvider)
 	deleteAPIKey = datastore.ModifySQLStatement(deleteAPIKey, databaseProvider)
 	listAPIKeys = datastore.ModifySQLStatement(listAPIKeys, databaseProvider)
+	updateAPIKeyLastUsed = datastore.ModifySQLStatement(updateAPIKeyLastUsed, databaseProvider)
 }
 
 // AddAPIKey - Add a new API key to the datastore.
@@ -78,20 +83,24 @@ func (p *PgsqlAPIKeysRepository) AddAPIKey(userID string, comment string) (*inte
 	return apiKey, err
 }
 
-// GetAPIKeyUserID - gets user ID for an API key
-func (p *PgsqlAPIKeysRepository) GetAPIKeyUserID(keySecret string) (string, error) {
-	log.Debug("GetAPIKeyUserID")
+// GetAPIKeyBySecret - gets user ID for an API key
+func (p *PgsqlAPIKeysRepository) GetAPIKeyBySecret(keySecret string) (*interfaces.APIKey, error) {
+	log.Debug("GetAPIKeyBySecret")
+
+	var apiKey interfaces.APIKey
 
-	var (
-		err      error
-		userGUID string
+	err := p.db.QueryRow(getAPIKeyBySecret, keySecret).Scan(
+		&apiKey.GUID,
+		&apiKey.UserGUID,
+		&apiKey.Comment,
+		&apiKey.LastUsed,
 	)
 
-	if err = p.db.QueryRow(getAPIKeyUserID, keySecret).Scan(&userGUID); err != nil {
-		return "", err
+	if err != nil {
+		return nil, err
 	}
 
-	return userGUID, nil
+	return &apiKey, nil
 }
 
 // ListAPIKeys - list API keys for a given user GUID
@@ -107,7 +116,7 @@ func (p *PgsqlAPIKeysRepository) ListAPIKeys(userID string) ([]interfaces.APIKey
 	result := []interfaces.APIKey{}
 	for rows.Next() {
 		var apiKey interfaces.APIKey
-		err = rows.Scan(&apiKey.GUID, &apiKey.UserGUID, &apiKey.Comment)
+		err = rows.Scan(&apiKey.GUID, &apiKey.UserGUID, &apiKey.Comment, &apiKey.LastUsed)
 		if err != nil {
 			log.Errorf("Scan: %v", err)
 			return nil, err
@@ -136,3 +145,22 @@ func (p *PgsqlAPIKeysRepository) DeleteAPIKey(userGUID string, keyGUID string) e
 
 	return nil
 }
+
+// UpdateAPIKeyLastUsed - sets API key last_used field to current time
+func (p *PgsqlAPIKeysRepository) UpdateAPIKeyLastUsed(keyGUID string) error {
+	log.Debug("UpdateAPIKeyLastUsed")
+
+	result, err := p.db.Exec(updateAPIKeyLastUsed, time.Now(), keyGUID)
+	if err != nil {
+		return err
+	}
+
+	rowsUpdates, err := result.RowsAffected()
+	if err != nil {
+		return errors.New("unable to UPDATE api key: could not determine number of rows that were updated")
+	} else if rowsUpdates < 1 {
+		return errors.New("unable to UPDATE api key: no rows were updated")
+	}
+
+	return nil
+}
diff --git a/src/jetstream/repository/interfaces/apikeys.go b/src/jetstream/repository/interfaces/apikeys.go
@@ -1,8 +1,12 @@
 package interfaces
 
+import "time"
+
+// APIKey - represents API key DB entry
 type APIKey struct {
-	GUID     string `json:"guid"`
-	Secret   string `json:"secret"`
-	UserGUID string `json:"user_guid"`
-	Comment  string `json:"comment"`
+	GUID     string     `json:"guid"`
+	Secret   string     `json:"secret"`
+	UserGUID string     `json:"user_guid"`
+	Comment  string     `json:"comment"`
+	LastUsed *time.Time `json:"last_used"`
 }