coder · kylecarbs · Jun 22, 2022 · Jun 22, 2022 · Jun 22, 2022 · Jun 22, 2022
diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
@@ -1382,6 +1382,7 @@ func (q *fakeQuerier) InsertAPIKey(_ context.Context, arg database.InsertAPIKeyP
 		ID:                arg.ID,
 		LifetimeSeconds:   arg.LifetimeSeconds,
 		HashedSecret:      arg.HashedSecret,
+		IPAddress:         arg.IPAddress,
 		UserID:            arg.UserID,
 		ExpiresAt:         arg.ExpiresAt,
 		CreatedAt:         arg.CreatedAt,
@@ -1802,6 +1803,7 @@ func (q *fakeQuerier) UpdateAPIKeyByID(_ context.Context, arg database.UpdateAPI
 		}
 		apiKey.LastUsed = arg.LastUsed
 		apiKey.ExpiresAt = arg.ExpiresAt
+		apiKey.IPAddress = arg.IPAddress
 		apiKey.OAuthAccessToken = arg.OAuthAccessToken
 		apiKey.OAuthRefreshToken = arg.OAuthRefreshToken
 		apiKey.OAuthExpiry = arg.OAuthExpiry

diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000027_apikey_ip.down.sql b/coderd/database/migrations/000027_apikey_ip.down.sql
@@ -0,0 +1,2 @@
+ALTER TABLE ONLY api_keys
+    DROP COLUMN IF EXISTS ip_address;
diff --git a/coderd/database/migrations/000027_apikey_ip.up.sql b/coderd/database/migrations/000027_apikey_ip.up.sql
@@ -0,0 +1,2 @@
+ALTER TABLE ONLY api_keys
+    ADD COLUMN IF NOT EXISTS ip_address inet NOT NULL DEFAULT '0.0.0.0';
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/apikeys.sql b/coderd/database/queries/apikeys.sql
@@ -17,6 +17,7 @@ INSERT INTO
 		id,
 		lifetime_seconds,
 		hashed_secret,
+		ip_address,
 		user_id,
 		last_used,
 		expires_at,
@@ -35,17 +36,18 @@ VALUES
 	     WHEN 0 THEN 86400
 		 ELSE @lifetime_seconds::bigint
 	 END
-	 , @hashed_secret, @user_id, @last_used, @expires_at, @created_at, @updated_at, @login_type, @oauth_access_token, @oauth_refresh_token, @oauth_id_token, @oauth_expiry) RETURNING *;
+	 , @hashed_secret, @ip_address, @user_id, @last_used, @expires_at, @created_at, @updated_at, @login_type, @oauth_access_token, @oauth_refresh_token, @oauth_id_token, @oauth_expiry) RETURNING *;
 
 -- name: UpdateAPIKeyByID :exec
 UPDATE
 	api_keys
 SET
 	last_used = $2,
 	expires_at = $3,
-	oauth_access_token = $4,
-	oauth_refresh_token = $5,
-	oauth_expiry = $6
+	ip_address = $4,
+	oauth_access_token = $5,
+	oauth_refresh_token = $6,
+	oauth_expiry = $7
 WHERE
 	id = $1;
 

diff --git a/coderd/database/sqlc.yaml b/coderd/database/sqlc.yaml
@@ -29,3 +29,4 @@ rename:
   userstatus: UserStatus
   gitsshkey: GitSSHKey
   rbac_roles: RBACRoles
+  ip_address: IPAddress
diff --git a/coderd/httpmw/apikey.go b/coderd/httpmw/apikey.go
@@ -7,12 +7,15 @@ import (
 	"database/sql"
 	"errors"
 	"fmt"
+	"net"
 	"net/http"
 	"strings"
 	"time"
 
 	"golang.org/x/oauth2"
 
+	"github.com/tabbed/pqtype"
+
 	"github.com/coder/coder/coderd/database"
 	"github.com/coder/coder/coderd/httpapi"
 )
@@ -164,6 +167,17 @@ func ExtractAPIKey(db database.Store, oauth *OAuth2Configs) func(http.Handler) h
 			// Only update LastUsed once an hour to prevent database spam.
 			if now.Sub(key.LastUsed) > time.Hour {
 				key.LastUsed = now
+				remoteIP := net.ParseIP(r.RemoteAddr)
+				if remoteIP == nil {
+					remoteIP = net.IPv4(0, 0, 0, 0)
+				}
+				key.IPAddress = pqtype.Inet{
+					IPNet: net.IPNet{
+						IP:   remoteIP,
+						Mask: remoteIP.DefaultMask(),
+					},
+					Valid: true,
+				}
 				changed = true
 			}
 			// Only update the ExpiresAt once an hour to prevent database spam.
@@ -178,6 +192,7 @@ func ExtractAPIKey(db database.Store, oauth *OAuth2Configs) func(http.Handler) h
 					ID:                key.ID,
 					LastUsed:          key.LastUsed,
 					ExpiresAt:         key.ExpiresAt,
+					IPAddress:         key.IPAddress,
 					OAuthAccessToken:  key.OAuthAccessToken,
 					OAuthRefreshToken: key.OAuthRefreshToken,
 					OAuthExpiry:       key.OAuthExpiry,

diff --git a/coderd/httpmw/apikey_test.go b/coderd/httpmw/apikey_test.go
@@ -402,6 +402,41 @@ func TestAPIKey(t *testing.T) {
 		require.Equal(t, token.Expiry, gotAPIKey.ExpiresAt)
 		require.Equal(t, token.AccessToken, gotAPIKey.OAuthAccessToken)
 	})
+
+	t.Run("RemoteIPUpdates", func(t *testing.T) {
+		t.Parallel()
+		var (
+			db         = databasefake.New()
+			id, secret = randomAPIKeyParts()
+			hashed     = sha256.Sum256([]byte(secret))
+			r          = httptest.NewRequest("GET", "/", nil)
+			rw         = httptest.NewRecorder()
+			user       = createUser(r.Context(), t, db)
+		)
+		r.RemoteAddr = "1.1.1.1"
+		r.AddCookie(&http.Cookie{
+			Name:  httpmw.SessionTokenKey,
+			Value: fmt.Sprintf("%s-%s", id, secret),
+		})
+
+		sentAPIKey, err := db.InsertAPIKey(r.Context(), database.InsertAPIKeyParams{
+			ID:           id,
+			HashedSecret: hashed[:],
+			LastUsed:     database.Now().AddDate(0, 0, -1),
+			ExpiresAt:    database.Now().AddDate(0, 0, 1),
+			UserID:       user.ID,
+		})
+		require.NoError(t, err)
+		httpmw.ExtractAPIKey(db, nil)(successHandler).ServeHTTP(rw, r)
+		res := rw.Result()
+		defer res.Body.Close()
+		require.Equal(t, http.StatusOK, res.StatusCode)
+
+		gotAPIKey, err := db.GetAPIKeyByID(r.Context(), id)
+		require.NoError(t, err)
+
+		require.NotEqual(t, sentAPIKey.IPAddress, gotAPIKey.IPAddress)
+	})
 }
 
 func createUser(ctx context.Context, t *testing.T, db database.Store) database.User {

diff --git a/coderd/telemetry/telemetry.go b/coderd/telemetry/telemetry.go
@@ -7,6 +7,7 @@ import (
 	"encoding/json"
 	"errors"
 	"fmt"
+	"net"
 	"net/http"
 	"net/url"
 	"runtime"
@@ -428,13 +429,17 @@ func (r *remoteReporter) createSnapshot() (*Snapshot, error) {
 
 // ConvertAPIKey anonymizes an API key.
 func ConvertAPIKey(apiKey database.APIKey) APIKey {
-	return APIKey{
+	a := APIKey{
 		ID:        apiKey.ID,
 		UserID:    apiKey.UserID,
 		CreatedAt: apiKey.CreatedAt,
 		LastUsed:  apiKey.LastUsed,
 		LoginType: apiKey.LoginType,
 	}
+	if apiKey.IPAddress.Valid {
+		a.IPAddress = apiKey.IPAddress.IPNet.IP
+	}
+	return a
 }
 
 // ConvertWorkspace anonymizes a workspace.
@@ -616,6 +621,7 @@ type APIKey struct {
 	CreatedAt time.Time          `json:"created_at"`
 	LastUsed  time.Time          `json:"last_used"`
 	LoginType database.LoginType `json:"login_type"`
+	IPAddress net.IP             `json:"ip_address"`
 }
 
 type User struct {
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE ONLY api_keys
		DROP COLUMN IF EXISTS ip_address;
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE ONLY api_keys
		ADD COLUMN IF NOT EXISTS ip_address inet NOT NULL DEFAULT '0.0.0.0';