fix(sql): handle SQLite text password groups

Fix/implement id token hint rp logout

storage/sql/crud.go

@@ -64,8 +64,13 @@ func (j jsonDecoder) Scan(dest interface{}) error {
 	if dest == nil {
 		return errors.New("nil value")
 	}
-	b, ok := dest.([]byte)
-	if !ok {
+	var b []byte
+	switch v := dest.(type) {
+	case []byte:
+		b = v
+	case string:
+		b = []byte(v)
+	default:
 		return fmt.Errorf("expected []byte got %T", dest)
 	}
 	if err := json.Unmarshal(b, &j.i); err != nil {

storage/sql/crud_test.go

@@ -4,7 +4,9 @@
 package sql
 
 import (
+	"context"
 	"database/sql"
+	"log/slog"
 	"reflect"
 	"testing"
 )
@@ -32,6 +34,45 @@ func TestDecoder(t *testing.T) {
 	}
 }
 
+func TestGetPasswordWithTextGroups(t *testing.T) {
+	logger := slog.New(slog.DiscardHandler)
+	conn, err := (&SQLite3{File: ":memory:"}).open(logger)
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer conn.Close()
+
+	email := "text-groups@example.com"
+	_, err = conn.Exec(`
+		insert into password (
+			email, hash, username, preferred_username, user_id, groups, name, email_verified
+		)
+		values (
+			$1, $2, $3, $4, $5, CAST('[]' AS TEXT), $6, $7
+		);`,
+		email, []byte("hash"), "username", "", "user-id", "", false,
+	)
+	if err != nil {
+		t.Fatal(err)
+	}
+
+	var storageClass string
+	if err := conn.QueryRow(`select typeof(groups) from password where email = $1;`, email).Scan(&storageClass); err != nil {
+		t.Fatal(err)
+	}
+	if storageClass != "text" {
+		t.Fatalf("expected groups storage class text, got %q", storageClass)
+	}
+
+	p, err := conn.GetPassword(context.Background(), email)
+	if err != nil {
+		t.Fatal(err)
+	}
+	if len(p.Groups) != 0 {
+		t.Fatalf("expected empty groups, got %q", p.Groups)
+	}
+}
+
 func TestEncoder(t *testing.T) {
 	db, err := sql.Open("sqlite3", ":memory:")
 	if err != nil {

storage/sql/migrate.go

@@ -473,4 +473,12 @@ var migrations = []migration{
 				add column connector_data bytea;`,
 		},
 	},
+	// Convert password groups values backfilled from the original default '[]'
+	// from SQLite TEXT storage class to BLOB.
+	{
+		stmts: []string{
+			`update password set groups = CAST(groups as BLOB);`,
+		},
+		flavor: &flavorSQLite3,
+	},
 }