Admins disable, enable and delete accounts 6a1780558353

-- Disabled and deleted accounts (oxygenhub#81). A disabled account is

-- refused like a banned one, with its own message, and can be enabled

-- again. A deleted account's row goes (its tokens, keys and memberships

-- with it); its username is retired so nobody can take it over.

ALTER TABLE accounts ADD COLUMN disabled INTEGER NOT NULL DEFAULT 0;

CREATE TABLE retired_usernames (

    username   TEXT PRIMARY KEY COLLATE NOCASE,

    retired_ms INTEGER NOT NULL

);

-- A review keeps its author's name when the account is deleted, as issues

-- and comments do, rather than going with it.

CREATE TABLE proposal_reviews_kept (

    id          INTEGER PRIMARY KEY,

    proposal_id INTEGER NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    author_id   INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    author      TEXT NOT NULL DEFAULT '',

    verdict     TEXT NOT NULL CHECK (verdict IN ('comment', 'changes', 'approve')),

    body        TEXT NOT NULL DEFAULT '',

    created_ms  INTEGER NOT NULL

);

INSERT INTO proposal_reviews_kept (id, proposal_id, author_id, author, verdict, body, created_ms)

    SELECT id, proposal_id, author_id, author, verdict, body, created_ms FROM proposal_reviews;

DROP TABLE proposal_reviews;

ALTER TABLE proposal_reviews_kept RENAME TO proposal_reviews;

CREATE INDEX proposal_reviews_by_proposal ON proposal_reviews (proposal_id, created_ms);

- **Disable and delete accounts (#81).** Administration offers Disable and Enable beside Ban: a disabled account is refused like a banned one (sign-in, sessions, tokens, pushes), and signing in says "Your account has been disabled. Contact the hub's administrator." Delete removes an account for good after its username is typed again: its tokens, keys and memberships go, what it wrote stays under its name, and the name stays retired so nobody can take it over. It is refused, naming them, while the account owns repositories or is the only owner of an organisation; nobody disables or deletes themselves or the last admin who can sign in. A deleted account's proposal reviews now stay, as its comments do.

            let account = db::accounts::account_by_id(&app.db, tok.account_id).await?.filter(|a| !a.blocked());

    if account.blocked() {

    if account.blocked() {

    if account.blocked() {

    /// Disabled by an admin: refused like a ban, and enabled again (0031).

    #[sqlx(default)]

    pub disabled: bool,

    /// Whether the account may not sign in or act: banned or disabled.

    pub fn blocked(&self) -> bool {

        self.banned || self.disabled

    }

    Ok(())

}

pub async fn set_disabled(pool: &Pool, id: i64, disabled: bool) -> sqlx::Result<()> {

    sqlx::query("UPDATE accounts SET disabled = ? WHERE id = ?").bind(disabled).bind(id).execute(pool).await?;

}

/// Delete an account (oxygenhub#81): its row goes, and with it (by the

/// schema's cascades) its tokens, keys, sessions' salt, memberships, stars

/// and notifications; what it wrote keeps its author's name. The username

/// is retired so nobody can register it and pass for the account.

pub async fn delete_account(pool: &Pool, id: i64, username: &str) -> sqlx::Result<()> {

    let mut tx = super::begin_write(pool).await?;

    sqlx::query("INSERT OR IGNORE INTO retired_usernames (username, retired_ms) VALUES (?, ?)")

        .bind(username)

        .bind(now_ms())

        .execute(&mut *tx)

        .await?;

    sqlx::query("DELETE FROM accounts WHERE id = ?").bind(id).execute(&mut *tx).await?;

    tx.commit().await

}

/// The organisations whose only owner is this account: deleting it would

/// leave them with nobody to manage them.

pub async fn orgs_owned_only_by(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<String>> {

    sqlx::query_scalar(

        "SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id \

         WHERE m.account_id = ? AND m.role = 'owner' \

           AND (SELECT COUNT(*) FROM org_members x WHERE x.org_id = o.id AND x.role = 'owner') = 1 \

         ORDER BY o.name",

    )

    .bind(account_id)

    .fetch_all(pool)

    .await

}

/// Whether `name` belonged to a deleted account.

pub async fn username_retired(pool: &Pool, name: &str) -> sqlx::Result<bool> {

    Ok(sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM retired_usernames WHERE username = ?").bind(name).fetch_one(pool).await? > 0)

    /// `None` once the author's account was deleted (its name stays).

    pub author_id: Option<i64>,

                    "{:<24} {:<32} {}{}{}",

                    if a.banned { "banned " } else { "" },

                    if a.disabled { "disabled" } else { "" }

        if account.disabled {

            return Err(Failure(StatusCode::UNAUTHORIZED, "this account is disabled; contact the hub's administrator".into()));

        }

    /// For delete: the username typed again.

    #[serde(default)]

    confirm: String,

    if target.id == me.id && matches!(f.action.as_str(), "ban" | "demote" | "disable" | "delete") {

    // The hub must keep an admin who can sign in (oxygenhub#81).

    if target.is_admin && matches!(f.action.as_str(), "disable" | "delete") {

        let others =

            accounts::accounts(&app.db).await.map_err(e)?.into_iter().filter(|a| a.is_admin && !a.blocked() && a.id != target.id).count();

        if others == 0 {

            return admin_page(&app, shell, Some(format!("{} is the last admin who can sign in.", target.username))).await;

        }

    }

    if f.action == "delete" {

        if f.confirm.trim() != target.username {

            return admin_page(&app, shell, Some(format!("Not deleted: type {} to confirm.", target.username))).await;

        }

        let repos = crate::db::repos::list_by_owner(&app.db, &target.username).await.map_err(e)?;

        let orgs = accounts::orgs_owned_only_by(&app.db, target.id).await.map_err(e)?;

        if !repos.is_empty() || !orgs.is_empty() {

            let mut held: Vec<String> = repos.iter().map(|r| r.slug()).collect();

            held.extend(orgs.iter().map(|o| format!("organisation {o} (sole owner)")));

            return admin_page(

                &app,

                shell,

                Some(format!("Not deleted: {} still owns {}. Transfer or delete them first.", target.username, held.join(", "))),

            )

            .await;

        }

        accounts::delete_account(&app.db, target.id, &target.username).await.map_err(e)?;

        accounts::audit(&app.db, Some(&me), "admin.account.delete", &target.username, "").await.ok();

        return admin_page(&app, shell, Some(format!("{} deleted; the name stays retired.", target.username))).await;

    }

        if target.blocked() {

            return admin_page(&app, shell, Some("A banned or disabled account cannot be reset.".into())).await;

        "disable" => accounts::set_disabled(&app.db, target.id, true).await.map_err(e)?,

        "enable" => accounts::set_disabled(&app.db, target.id, false).await.map_err(e)?,

        || accounts::org_by_name(&app.db, &name).await.map_err(|e| Error::from_db(shell.clone(), e))?.is_some()

        || accounts::username_retired(&app.db, &name).await.map_err(|e| Error::from_db(shell.clone(), e))?;

    if let Some(a) = account.filter(|a| a.email_verified && !a.email.is_empty() && !a.blocked()) {

    Ok(account.filter(|a| !a.blocked()).map(|a| (link, a)))

    if account.disabled {

        accounts::audit(&app.db, None, "login.disabled", &username, "").await.ok();

        return Ok(fail("Your account has been disabled. Contact the hub's administrator.", None));

    }

        || accounts::username_retired(&app.db, &username).await.map_err(|e| Error::from_db(shell.clone(), e))?

/// oxygenhub#81: an admin disables an account (sign-in says so, its session

/// and pushes stop) and enables it again; deletes one, refused while it

/// owns a repository, after which its name stays retired.

#[tokio::test(flavor = "multi_thread")]

async fn admins_disable_enable_and_delete_accounts() {

    let h = hub().await;

    let app = &h.app;

    let andy = register(app, "andy", "").await;

    let invite = |app: Shared, andy: String| async move {

        let csrf = csrf_of(&get(&app, "/admin", Some(&andy)).await.body);

        let r = post(&app, "/admin/invite", Some(&andy), &format!("_csrf={csrf}")).await;

        r.body.split("register?invite=").nth(1).unwrap().split('<').next().unwrap().trim().to_string()

    };

    let bob = register(app, "bob", &invite(app.clone(), andy.clone()).await).await;

    let act = |who: &'static str, action: &'static str, confirm: &'static str| {

        let (app, andy) = (app.clone(), andy.clone());

        async move {

            let csrf = csrf_of(&get(&app, "/admin", Some(&andy)).await.body);

            post(&app, "/admin/account", Some(&andy), &format!("_csrf={csrf}&username={who}&action={action}&confirm={confirm}")).await.body

        }

    };

    let login = |user: &'static str| {

        let app = app.clone();

        async move { post(&app, "/login", None, &format!("username={user}&password=correct-horse-battery")).await }

    };

    // Disabled: sign-in says so, the old session no longer counts.

    act("bob", "disable", "").await;

    assert!(db::accounts::account_by_name(&app.db, "bob").await.unwrap().unwrap().disabled);

    let r = login("bob").await;

    assert!(r.body.contains("Your account has been disabled"), "{}", r.body);

    assert!(r.cookie.is_none());

    assert_eq!(get(app, "/settings", Some(&bob)).await.status, StatusCode::SEE_OTHER, "the session is refused");

    assert!(act("andy", "disable", "").await.contains("Not on yourself"));

    // Enabled again: sign-in works.

    act("bob", "enable", "").await;

    assert_eq!(login("bob").await.status, StatusCode::SEE_OTHER);

    // Delete: confirmed by name, refused while Bob owns a repository.

    let bob = login("bob").await.cookie.unwrap();

    let csrf = csrf_of(&get(app, "/new", Some(&bob)).await.body);

    assert_eq!(

        post(app, "/new", Some(&bob), &format!("_csrf={csrf}&owner=bob&name=keep&visibility=public")).await.status,

        StatusCode::SEE_OTHER

    );

    assert!(act("bob", "delete", "bo").await.contains("type bob to confirm"));

    let page = act("bob", "delete", "bob").await;

    assert!(page.contains("still owns bob/keep"), "{page}");

    assert!(db::accounts::account_by_name(&app.db, "bob").await.unwrap().is_some());

    let row = db::repos::get(&app.db, "bob", "keep").await.unwrap().unwrap();

    db::repos::delete(&app.db, row.id).await.unwrap();

    let page = act("bob", "delete", "bob").await;

    assert!(page.contains("bob deleted"), "{page}");

    assert!(db::accounts::account_by_name(&app.db, "bob").await.unwrap().is_none());

    assert_eq!(get(app, "/settings", Some(&bob)).await.status, StatusCode::SEE_OTHER);

    // Its name stays retired.

    let code = invite(app.clone(), andy.clone()).await;

    let r = post(app, "/register", None, &format!("username=bob&email=b%40example.com&password=correct-horse-battery&invite={code}")).await;

    assert!(r.body.contains("That name is taken"), "{}", r.body);

    // The last admin can be neither disabled nor deleted.

    assert!(act("andy", "delete", "andy").await.contains("Not on yourself"));

}

  <tr><td><a href="/{{ a.username }}">{{ a.username }}</a></td><td>{{ a.email }}{% if !a.email.is_empty() %} <span class="small mute" title="{% if a.email_verified %}verified{% else %}not verified{% endif %}">{% if a.email_verified %}✔{% else %}○{% endif %}</span>{% endif %}</td><td>{% if a.is_admin %}<span class="tag">admin</span>{% endif %}{% if a.banned %} <span class="tag bad">banned</span>{% endif %}{% if a.disabled %} <span class="tag warn">disabled</span>{% endif %}{% if a.totp_enabled() %} <span class="tag">2fa</span>{% endif %}</td>

    {% if a.disabled %}<button name="action" value="enable">enable</button>{% else %}<button name="action" value="disable" title="Refuse sign-in, sessions, tokens and pushes until enabled again">disable</button>{% endif %}

    {% if !a.blocked() %}<button name="action" value="reset_link" title="A one-time password reset link to hand over">reset link</button>{% endif %}</form>

    <details class="tiny delete-account"><summary>delete</summary><form class="tiny" method="post" action="/admin/account"><input type="hidden" name="_csrf" value="{{ shell.csrf }}"><input type="hidden" name="username" value="{{ a.username }}">

    <label class="small">Deleting removes the account, its tokens, keys and memberships for good; what it wrote stays under its name, which stays retired. Type <strong>{{ a.username }}</strong> to confirm:

    <input name="confirm" autocomplete="off" required></label> <button class="danger" name="action" value="delete">delete {{ a.username }}</button></form></details></td></tr>