X Tutup
Unverified Commit 71c713ca authored by Leonidas Spyropoulos's avatar Leonidas Spyropoulos 📦
Browse files

fix: add PRIMARY KEY to Sessions.UsersID to prevent duplicate rows 

Sessions.UsersID had no uniqueness constraint at the DB level, only an
ORM-declared primary_key. Under concurrent login requests for the same
user, both transactions could INSERT a new session row before either
committed, leaving duplicate rows that caused SQLAlchemy to raise
MultipleResultsFound (HTTP 500) on the next login. Fix with migration
and expire ORM session on IntegrityError
parent 1b445854

aurweb/models/user.py

+7 −4
Original line number Diff line number Diff line
@@ -123,16 +123,18 @@ class User(Base): 
                with db.begin():

                    self.LastLogin = now_ts

                    self.LastLoginIPAddress = util.get_client_ip(request)

                    if not self.session:



                    current_session = self.session

                    if not current_session:

                        sid = generate_unique_sid()

                        self.session = db.create(

                            Session, User=self, SessionID=sid, LastUpdateTS=now_ts

                        )

                    else:

                        last_updated = self.session.LastUpdateTS

                        last_updated = current_session.LastUpdateTS

                        if last_updated and last_updated < now_ts:

                            self.session.SessionID = generate_unique_sid()

                        self.session.LastUpdateTS = now_ts

                            current_session.SessionID = generate_unique_sid()

                        current_session.LastUpdateTS = now_ts



                    # Unset InactivityTS, we've logged in!

                    self.InactivityTS = 0
@@ -140,6 +142,7 @@ class User(Base): 
                    break

            except IntegrityError as exc_:

                exc = exc_

                db.get_session().expire(self, ["session"])



        if exc:

            raise exc

aurweb/schema.py

+6 −1
Original line number Diff line number Diff line
@@ -136,7 +136,12 @@ SSHPubKeys = Table( 
Sessions = Table(

    "Sessions",

    metadata,

    Column("UsersID", ForeignKey("Users.ID", ondelete="CASCADE"), nullable=False),

    Column(

        "UsersID",

        ForeignKey("Users.ID", ondelete="CASCADE"),

        nullable=False,

        primary_key=True,

    ),

    Column("SessionID", CHAR(32), nullable=False, unique=True),

    Column("LastUpdateTS", BIGINT(unsigned=True), nullable=False),

    mysql_engine="InnoDB",

migrations/versions/f2701a76f4a9_add_primary_key_to_sessions_usersid.py

0 → 100644
+44 −0
Original line number Diff line number Diff line 
"""Add PRIMARY KEY to Sessions.UsersID



Previously UsersID was only a plain KEY (index) with no uniqueness constraint,

allowing duplicate rows to be inserted under a concurrent-login race condition.

This migration:

1. Removes any duplicate rows, keeping the most recently updated session per user.

2. Drops the plain index on UsersID.

3. Promotes UsersID to PRIMARY KEY, enforcing one session per user at the DB level.



Revision ID: f2701a76f4a9

Revises: 38e5b9982eea

Create Date: 2026-02-20 00:00:00.000000



"""



from alembic import op



# revision identifiers, used by Alembic.

revision = "f2701a76f4a9"

down_revision = "38e5b9982eea"

branch_labels = None

depends_on = None





def upgrade():

    # Remove duplicate Sessions rows, keeping the one with the highest

    # SessionID for each UsersID (deterministic tiebreaker when timestamps match).

    op.execute(

        """

        DELETE s1 FROM Sessions s1

        JOIN Sessions s2

            ON s1.UsersID = s2.UsersID

           AND s1.SessionID < s2.SessionID

        """

    )

    # Drop the plain index now that we are promoting it to a PRIMARY KEY.

    op.execute("ALTER TABLE Sessions DROP KEY UsersID")

    # Add the PRIMARY KEY — enforces one session per user at the DB level.

    op.execute("ALTER TABLE Sessions ADD PRIMARY KEY (UsersID)")





def downgrade():

    op.execute("ALTER TABLE Sessions DROP PRIMARY KEY")

    op.execute("ALTER TABLE Sessions ADD KEY UsersID (UsersID)")
X Tutup