Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package tests
import (
"context"
"testing"
"time"
"github.com/stretchr/testify/require"
"github.com/bytebase/bytebase/backend/common/testcontainer"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
)
// TestCollision_LoginAttempt is the composite-PK collision gate for
// login_attempt (PRIMARY KEY (identity, kind)) — see
// backend/store/AGENTS.md and docs/pre-pr-checklist.md step 3. The table is not project-scoped: identity
// is itself the scope column, so the shared project fixture does not apply.
// What collision safety means here is that no store operation may cross
// buckets: rows sharing an identity (other kind) or sharing a kind (other
// identity) must be untouched by claims, clears, and purges on a neighbor.
func TestCollision_LoginAttempt(t *testing.T) {
t.Parallel()
ctx := context.Background()
metadataDB, s, _ := testcontainer.NewMetadataDB(t)
const window = 10 * time.Minute
const victim = "[email protected]"
const neighbor = "[email protected]"
// Fill the victim's PASSWORD bucket to its limit and park one attempt in
// each neighboring bucket that shares one PK column with it.
for range 3 {
granted, err := s.ClaimAttempt(ctx, victim, storepb.LoginAttemptKind_PASSWORD, 3, window)
require.NoError(t, err)
require.True(t, granted)
}
for _, seed := range []struct {
identity string
kind storepb.LoginAttemptKind
}{
{victim, storepb.LoginAttemptKind_MFA}, // same identity, other kind
{neighbor, storepb.LoginAttemptKind_PASSWORD}, // same kind, other identity
} {
granted, err := s.ClaimAttempt(ctx, seed.identity, seed.kind, 3, window)
require.NoError(t, err)
require.True(t, granted)
}
attempts := func(identity string, kind storepb.LoginAttemptKind) int {
t.Helper()
var count int
require.NoError(t, metadataDB.QueryRowContext(ctx, `
SELECT COALESCE((SELECT attempts FROM login_attempt WHERE identity = $1 AND kind = $2), 0)
`, identity, kind.String()).Scan(&count))
return count
}
// The victim's lock must not leak into either neighbor.
granted, err := s.ClaimAttempt(ctx, victim, storepb.LoginAttemptKind_PASSWORD, 3, window)
require.NoError(t, err)
require.False(t, granted, "the victim bucket is at its limit")
require.Equal(t, 1, attempts(victim, storepb.LoginAttemptKind_MFA))
require.Equal(t, 1, attempts(neighbor, storepb.LoginAttemptKind_PASSWORD))
// Clearing the victim must delete exactly the victim's row.
require.NoError(t, s.ClearLoginAttempt(ctx, victim, storepb.LoginAttemptKind_PASSWORD))
require.Zero(t, attempts(victim, storepb.LoginAttemptKind_PASSWORD))
require.Equal(t, 1, attempts(victim, storepb.LoginAttemptKind_MFA))
require.Equal(t, 1, attempts(neighbor, storepb.LoginAttemptKind_PASSWORD))
// The send budget writes the same table under a different kind and a
// different window rule, so it must respect the same boundaries: charge its
// own (identity, kind) and leave every neighbor sharing one column alone.
granted, err = s.ClaimAttempt(ctx, victim, storepb.LoginAttemptKind_EMAIL_CODE_SEND, 3, window)
require.NoError(t, err)
require.True(t, granted)
require.Equal(t, 1, attempts(victim, storepb.LoginAttemptKind_EMAIL_CODE_SEND))
require.Equal(t, 1, attempts(victim, storepb.LoginAttemptKind_MFA), "a send bucket must not reach a credential bucket sharing its identity")
require.Equal(t, 1, attempts(neighbor, storepb.LoginAttemptKind_PASSWORD))
require.Zero(t, attempts(neighbor, storepb.LoginAttemptKind_EMAIL_CODE_SEND), "nor another identity sharing its kind")
// The time-scoped purge must not use the lock as a shortcut: backdate only
// the neighbor and confirm the purge takes it and nothing else.
_, err = metadataDB.ExecContext(ctx, `
UPDATE login_attempt SET last_attempt_at = last_attempt_at - make_interval(secs => $3)
WHERE identity = $1 AND kind = $2
`, neighbor, storepb.LoginAttemptKind_PASSWORD.String(), (2 * time.Hour).Seconds())
require.NoError(t, err)
deleted, err := s.DeleteStaleLoginAttempts(ctx, time.Hour)
require.NoError(t, err)
require.EqualValues(t, 1, deleted)
require.Zero(t, attempts(neighbor, storepb.LoginAttemptKind_PASSWORD))
require.Equal(t, 1, attempts(victim, storepb.LoginAttemptKind_MFA))
}