feat(commands): add webauthn device commands (#3671)

4 files changed, 60 insertions, 7 deletions

diff --git a/internal/storage/provider.go b/internal/storage/provider.go
index 7696966a5..fdf415a89 100644
--- a/internal/storage/provider.go
+++ b/internal/storage/provider.go
@@ -39,6 +39,8 @@ type Provider interface {
 
 	SaveWebauthnDevice(ctx context.Context, device model.WebauthnDevice) (err error)
 	UpdateWebauthnDeviceSignIn(ctx context.Context, id int, rpid string, lastUsedAt *time.Time, signCount uint32, cloneWarning bool) (err error)
+	DeleteWebauthnDevice(ctx context.Context, kid string) (err error)
+	DeleteWebauthnDeviceByUsername(ctx context.Context, username, description string) (err error)
 	LoadWebauthnDevices(ctx context.Context, limit, page int) (devices []model.WebauthnDevice, err error)
 	LoadWebauthnDevicesByUsername(ctx context.Context, username string) (devices []model.WebauthnDevice, err error)
 
diff --git a/internal/storage/sql_provider.go b/internal/storage/sql_provider.go
index bb5ac9895..6991e7021 100644
--- a/internal/storage/sql_provider.go
+++ b/internal/storage/sql_provider.go
@@ -56,6 +56,10 @@ func NewSQLProvider(config *schema.Configuration, name, driverName, dataSourceNa
 		sqlUpdateWebauthnDeviceRecordSignIn:           fmt.Sprintf(queryFmtUpdateWebauthnDeviceRecordSignIn, tableWebauthnDevices),
 		sqlUpdateWebauthnDeviceRecordSignInByUsername: fmt.Sprintf(queryFmtUpdateWebauthnDeviceRecordSignInByUsername, tableWebauthnDevices),
 
+		sqlDeleteWebauthnDevice:                         fmt.Sprintf(queryFmtDeleteWebauthnDevice, tableWebauthnDevices),
+		sqlDeleteWebauthnDeviceByUsername:               fmt.Sprintf(queryFmtDeleteWebauthnDeviceByUsername, tableWebauthnDevices),
+		sqlDeleteWebauthnDeviceByUsernameAndDescription: fmt.Sprintf(queryFmtDeleteWebauthnDeviceByUsernameAndDescription, tableWebauthnDevices),
+
 		sqlUpsertDuoDevice: fmt.Sprintf(queryFmtUpsertDuoDevice, tableDuoDevices),
 		sqlDeleteDuoDevice: fmt.Sprintf(queryFmtDeleteDuoDevice, tableDuoDevices),
 		sqlSelectDuoDevice: fmt.Sprintf(queryFmtSelectDuoDevice, tableDuoDevices),
@@ -169,6 +173,10 @@ type SQLProvider struct {
 	sqlUpdateWebauthnDeviceRecordSignIn           string
 	sqlUpdateWebauthnDeviceRecordSignInByUsername string
 
+	sqlDeleteWebauthnDevice                         string
+	sqlDeleteWebauthnDeviceByUsername               string
+	sqlDeleteWebauthnDeviceByUsernameAndDescription string
+
 	// Table: duo_devices.
 	sqlUpsertDuoDevice string
 	sqlDeleteDuoDevice string
@@ -841,6 +849,34 @@ func (p *SQLProvider) UpdateWebauthnDeviceSignIn(ctx context.Context, id int, rp
 	return nil
 }
 
+// DeleteWebauthnDevice deletes a registered Webauthn device.
+func (p *SQLProvider) DeleteWebauthnDevice(ctx context.Context, kid string) (err error) {
+	if _, err = p.db.ExecContext(ctx, p.sqlDeleteWebauthnDevice, kid); err != nil {
+		return fmt.Errorf("error deleting webauthn device with kid '%s': %w", kid, err)
+	}
+
+	return nil
+}
+
+// DeleteWebauthnDeviceByUsername deletes registered Webauthn devices by username or username and description.
+func (p *SQLProvider) DeleteWebauthnDeviceByUsername(ctx context.Context, username, description string) (err error) {
+	if len(username) == 0 {
+		return fmt.Errorf("error deleting webauthn device with username '%s' and description '%s': username must not be empty", username, description)
+	}
+
+	if len(description) == 0 {
+		if _, err = p.db.ExecContext(ctx, p.sqlDeleteWebauthnDeviceByUsername, username); err != nil {
+			return fmt.Errorf("error deleting webauthn devices for username '%s': %w", username, err)
+		}
+	} else {
+		if _, err = p.db.ExecContext(ctx, p.sqlDeleteWebauthnDeviceByUsernameAndDescription, username, description); err != nil {
+			return fmt.Errorf("error deleting webauthn device with username '%s' and description '%s': %w", username, description, err)
+		}
+	}
+
+	return nil
+}
+
 // LoadWebauthnDevices loads Webauthn device registrations.
 func (p *SQLProvider) LoadWebauthnDevices(ctx context.Context, limit, page int) (devices []model.WebauthnDevice, err error) {
 	devices = make([]model.WebauthnDevice, 0, limit)
diff --git a/internal/storage/sql_provider_backend_postgres.go b/internal/storage/sql_provider_backend_postgres.go
index 3b98e64e4..06fc40c78 100644
--- a/internal/storage/sql_provider_backend_postgres.go
+++ b/internal/storage/sql_provider_backend_postgres.go
@@ -61,6 +61,9 @@ func NewPostgreSQLProvider(config *schema.Configuration) (provider *PostgreSQLPr
 	provider.sqlUpdateWebauthnDevicePublicKeyByUsername = provider.db.Rebind(provider.sqlUpdateWebauthnDevicePublicKeyByUsername)
 	provider.sqlUpdateWebauthnDeviceRecordSignIn = provider.db.Rebind(provider.sqlUpdateWebauthnDeviceRecordSignIn)
 	provider.sqlUpdateWebauthnDeviceRecordSignInByUsername = provider.db.Rebind(provider.sqlUpdateWebauthnDeviceRecordSignInByUsername)
+	provider.sqlDeleteWebauthnDevice = provider.db.Rebind(provider.sqlDeleteWebauthnDevice)
+	provider.sqlDeleteWebauthnDeviceByUsername = provider.db.Rebind(provider.sqlDeleteWebauthnDeviceByUsername)
+	provider.sqlDeleteWebauthnDeviceByUsernameAndDescription = provider.db.Rebind(provider.sqlDeleteWebauthnDeviceByUsernameAndDescription)
 
 	provider.sqlSelectDuoDevice = provider.db.Rebind(provider.sqlSelectDuoDevice)
 	provider.sqlDeleteDuoDevice = provider.db.Rebind(provider.sqlDeleteDuoDevice)
diff --git a/internal/storage/sql_provider_queries.go b/internal/storage/sql_provider_queries.go
index 7dc44e156..eedfca30a 100644
--- a/internal/storage/sql_provider_queries.go
+++ b/internal/storage/sql_provider_queries.go
@@ -122,13 +122,13 @@ const (
 
 const (
 	queryFmtSelectWebauthnDevices = `
-		SELECT id, created_at, last_used_at, rpid, username, description, kid, public_key, attestation_type, transport, aaguid, sign_count, clone_warning 
+		SELECT id, created_at, last_used_at, rpid, username, description, kid, public_key, attestation_type, transport, aaguid, sign_count, clone_warning
 		FROM %s
 		LIMIT ?
 		OFFSET ?;`
 
 	queryFmtSelectWebauthnDevicesByUsername = `
-		SELECT id, created_at, last_used_at, rpid, username, description, kid, public_key, attestation_type, transport, aaguid, sign_count, clone_warning 
+		SELECT id, created_at, last_used_at, rpid, username, description, kid, public_key, attestation_type, transport, aaguid, sign_count, clone_warning
 		FROM %s
 		WHERE username = ?;`
 
@@ -144,14 +144,14 @@ const (
 
 	queryFmtUpdateWebauthnDeviceRecordSignIn = `
 		UPDATE %s
-		SET 
+		SET
 			rpid = ?, last_used_at = ?, sign_count = ?,
 			clone_warning = CASE clone_warning WHEN TRUE THEN TRUE ELSE ? END
 		WHERE id = ?;`
 
 	queryFmtUpdateWebauthnDeviceRecordSignInByUsername = `
 		UPDATE %s
-		SET 
+		SET
 			rpid = ?, last_used_at = ?, sign_count = ?,
 			clone_warning = CASE clone_warning WHEN TRUE THEN TRUE ELSE ? END
 		WHERE username = ? AND kid = ?;`
@@ -165,6 +165,18 @@ const (
 		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
 			ON CONFLICT (username, description)
 			DO UPDATE SET created_at = $1, last_used_at = $2, rpid = $3, kid = $6, public_key = $7, attestation_type = $8, transport = $9, aaguid = $10, sign_count = $11, clone_warning = $12;`
+
+	queryFmtDeleteWebauthnDevice = `
+		DELETE FROM %s
+		WHERE kid = ?;`
+
+	queryFmtDeleteWebauthnDeviceByUsername = `
+		DELETE FROM %s
+		WHERE username = ?;`
+
+	queryFmtDeleteWebauthnDeviceByUsernameAndDescription = `
+		DELETE FROM %s
+		WHERE username = ? AND description = ?;`
 )
 
 const (
@@ -232,7 +244,7 @@ const (
 		SELECT id, challenge_id, client_id, subject, authorized, granted, requested_at, responded_at, expires_at,
 		form_data, requested_scopes, granted_scopes, requested_audience, granted_audience
 		FROM %s
-		WHERE client_id = ? AND subject = ? AND 
+		WHERE client_id = ? AND subject = ? AND
 			  authorized = TRUE AND granted = TRUE AND expires_at IS NOT NULL AND expires_at >= CURRENT_TIMESTAMP;`
 
 	queryFmtInsertOAuth2ConsentSession = `
@@ -263,8 +275,8 @@ const (
 		WHERE signature = ? AND revoked = FALSE;`
 
 	queryFmtInsertOAuth2Session = `
-		INSERT INTO %s (challenge_id, request_id, client_id, signature, subject, requested_at, 
-		requested_scopes, granted_scopes, requested_audience, granted_audience, 
+		INSERT INTO %s (challenge_id, request_id, client_id, signature, subject, requested_at,
+		requested_scopes, granted_scopes, requested_audience, granted_audience,
 		active, revoked, form_data, session_data)
 		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`