Serializing SAML Certificate Rotation with a PostgreSQL Row Lock

A concurrent integration test caught a failure in a SAML certificate-rotation endpoint. The API used Rust, axum, SeaORM, and PostgreSQL. Each request followed a reasonable sequence: find the active signing key, deactivate it, then insert its replacement.

That sequence was only correct while one request ran at a time.

When the test sent two rotation requests together, one of them returned 500 Internal Server Error with a PostgreSQL UniqueViolation. The database had protected the data, but the handler had not treated the read and the following writes as one concurrent operation.

The invariant spans more than one row

Each SAML service can retain old signing keys for historical verification, but only one key may be active. Rotation therefore has three steps:

1. Find the current active key.
2. Mark it inactive.
3. Insert the new active key.

My first handler expressed those steps directly:

let active_key = saml_keys::Entity::find()
    .filter(saml_keys::Column::ServiceId.eq(service_id))
    .filter(saml_keys::Column::IsActive.eq(true))
    .one(db)
    .await?;

if let Some(key) = active_key {
    let mut active = key.into_active_model();
    active.is_active = Set(false);
    active.update(db).await?;
}

let new_key = saml_keys::ActiveModel {
    service_id: Set(service_id),
    is_active: Set(true),
    ..Default::default()
};

new_key.insert(db).await?;

Nothing in a single execution makes the bug obvious. The problem appears between executions. Both requests can read the same state before either has completed its writes:

1. Request A reads the current active key.
2. Request B reads the same key, or the same absence of a key.
3. Request A deactivates the old key and inserts a new active one.
4. Request B repeats its decision and attempts another active insert.

The second insert conflicts with the rule that a service may have only one active key. This is the check-then-act race: the write depends on a fact that can become stale after it is read.

Lock a row that always exists

The operation needs a stable row on which competing requests can contend. Locking the current key is insufficient because a service may not have a key yet. All rotations do, however, share the parent service row.

I moved the rotation into a transaction and selected that parent row with SeaORM’s lock_exclusive() . On PostgreSQL, the exclusive select lock is rendered as SELECT ... FOR UPDATE.

use sea_orm::{
    sea_query::Expr, EntityTrait, QueryFilter, QuerySelect,
    Set, TransactionTrait,
};

db.transaction::<_, (), Error>(|txn| {
    Box::pin(async move {
        let _service = service::Entity::find_by_id(service_id)
            .lock_exclusive()
            .one(txn)
            .await?
            .ok_or(Error::ServiceNotFound)?;

        saml_keys::Entity::update_many()
            .col_expr(saml_keys::Column::IsActive, Expr::value(false))
            .filter(saml_keys::Column::ServiceId.eq(service_id))
            .filter(saml_keys::Column::IsActive.eq(true))
            .exec(txn)
            .await?;

        let new_key = saml_keys::ActiveModel {
            service_id: Set(service_id),
            is_active: Set(true),
            // Set the generated key and certificate fields here.
            ..Default::default()
        };

        new_key.insert(txn).await?;

        Ok(())
    })
})
.await?;

The missing-service check is part of the locking protocol. If the query returns no parent row, PostgreSQL has locked nothing, so the handler must not continue as though serialization succeeded.

When two requests target the same service, the first holds the row lock until its transaction commits or rolls back. PostgreSQL makes the second SELECT ... FOR UPDATE wait for that transaction to end. Requests for different service rows can proceed independently. The PostgreSQL locking documentation describes the exact conflicts and wait behaviour.

This works only if every code path that rotates a key follows the same protocol. The parent-row lock is not a global property of the child table; a different writer that skips the lock can still race with this handler.

Keep the uniqueness rule in the database

The lock coordinates the normal write path. A database constraint still needs to protect the invariant when code is wrong, old, or incomplete.

A composite constraint on (service_id, is_active) does not express the rule:

UNIQUE (service_id, is_active)

It permits one active row per service, but it also permits only one inactive row. That would prevent the service from retaining a history of rotated keys.

PostgreSQL can instead enforce uniqueness over only the active subset:

CREATE UNIQUE INDEX unique_active_saml_key
ON saml_keys (service_id)
WHERE is_active = true;

The index contains active rows and ignores inactive ones. PostgreSQL’s partial-index documentation describes this use of a unique predicate: rows matching the predicate must be unique, while rows outside it remain unconstrained.

The lock and the index have different jobs. The lock makes concurrent rotations wait so the multi-statement operation runs in order. The partial unique index is the final backstop that prevents two active keys from being committed.

Serialization is not idempotency

With the lock in place, two concurrent rotation requests can both succeed in sequence. The second request will deactivate the key created by the first and create another one. The database ends in a valid state, but the first certificate becomes obsolete almost immediately.

That may be acceptable for an administrative rotation operation. If a double-click or client retry should represent one logical rotation, the endpoint also needs idempotency—for example, a client-supplied request key stored with the result. A row lock answers “in what order may these writes run?” It does not answer “are these two requests the same operation?”

The cost is bounded here

Waiting for a row lock keeps a transaction and its database connection occupied. A heavily contended row can turn parallel requests into a queue and consume the connection pool while they wait.

Certificate rotation is a relatively infrequent administrative operation, and the lock is scoped to one service, so that trade-off fits this handler. I would not apply the same pattern to a high-frequency counter or timestamp update. An atomic UPDATE, or optimistic concurrency with a version column and a bounded retry policy, would usually be a better starting point there.

The useful design rule is narrower than “lock before every write.” When an invariant spans several statements and the row you want to protect might not exist, choose a stable parent row, lock it inside the same transaction as the writes, and keep a database constraint as the last line of defence.