On 26/06/15, Lukas Fleischer wrote: > Attaching more than one SSH public key to the same account is useful, > e.g. if one uses different machines to access the AUR SSH interface. > Multiple keys can now be specified by adding multiple lines to the text > area on the account edit form. > > Implements FS#45469. > > Signed-off-by: Lukas Fleischer <[email protected]> > --- > Changes since v1: > > * Remove whitespace from SSH public keys before processing. > * Make sure SSH key fingerprints are unique. > * Only use one SQL query when checking for duplicates. > > git-interface/git-auth.py | 6 +- > schema/aur-schema.sql | 12 +++- > upgrading/4.0.0.txt | 10 ++- > web/html/account.php | 3 +- > web/lib/acctfuncs.inc.php | 153 > ++++++++++++++++++++++++++++++++++++++++------ > 5 files changed, 159 insertions(+), 25 deletions(-) > > diff --git a/git-interface/git-auth.py b/git-interface/git-auth.py > index b67d9de..c7de777 100755 > --- a/git-interface/git-auth.py > +++ b/git-interface/git-auth.py > @@ -47,8 +47,10 @@ db = mysql.connector.connect(host=aur_db_host, > user=aur_db_user, > unix_socket=aur_db_socket, buffered=True) > > cur = db.cursor() > -cur.execute("SELECT Username, AccountTypeID FROM Users WHERE SSHPubKey = %s > " + > - "AND Suspended = 0", (keytype + " " + keytext,)) > +cur.execute("SELECT Users.Username, Users.AccountTypeID FROM Users " + > + "INNER JOIN SSHPubKeys ON SSHPubKeys.UserID = Users.ID " > + "WHERE SSHPubKeys.PubKey = %s AND Users.Suspended = 0", > + (keytype + " " + keytext,)) > > if cur.rowcount != 1: > exit(1) > diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql > index 5a2e5c5..594a804 100644 > --- a/schema/aur-schema.sql > +++ b/schema/aur-schema.sql > @@ -33,7 +33,6 @@ CREATE TABLE Users ( > LangPreference VARCHAR(5) NOT NULL DEFAULT 'en', > IRCNick VARCHAR(32) NOT NULL DEFAULT '', > PGPKey VARCHAR(40) NULL DEFAULT NULL, > - SSHPubKey VARCHAR(4096) NULL DEFAULT NULL, > LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, > LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, > InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0, > @@ -53,6 +52,17 @@ INSERT INTO Users (ID, AccountTypeID, Username, Email, > Passwd) VALUES ( > 3, 1, 'user', 'user@localhost', MD5('user')); > > > +-- SSH public keys used for the aurweb SSH/Git interface. > +-- > +CREATE TABLE SSHPubKeys ( > + UserID INTEGER UNSIGNED NOT NULL, > + Fingerprint VARCHAR(44) NOT NULL, > + PubKey VARCHAR(4096) NOT NULL, > + PRIMARY KEY (Fingerprint), > + FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE > +) ENGINE = InnoDB; > + > + > -- Track Users logging in/out of AUR web site. > -- > CREATE TABLE Sessions ( > diff --git a/upgrading/4.0.0.txt b/upgrading/4.0.0.txt > index 637c4b9..74e167b 100644 > --- a/upgrading/4.0.0.txt > +++ b/upgrading/4.0.0.txt > @@ -3,10 +3,16 @@ want to keep the package contents, please create a backup > before starting the > upgrade process and import the source tarballs into the Git repositories > afterwards. > > -1. Add a field for the SSH public key to the Users table: > +1. Add a table to store SSH public keys: > > ---- > -ALTER TABLE Users ADD COLUMN SSHPubKey VARCHAR(4096) NULL DEFAULT NULL; > +CREATE TABLE SSHPubKeys ( > + UserID INTEGER UNSIGNED NOT NULL, > + Fingerprint VARCHAR(44) NOT NULL, > + PubKey VARCHAR(4096) NOT NULL, > + PRIMARY KEY (Fingerprint), > + FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE > +) ENGINE = InnoDB; > ---- > > 2. Create a new user and configure Git/SSH as described in INSTALL. > diff --git a/web/html/account.php b/web/html/account.php > index 0bb145c..c447de3 100644 > --- a/web/html/account.php > +++ b/web/html/account.php > @@ -16,6 +16,7 @@ $need_userinfo = array( > > if (in_array($action, $need_userinfo)) { > $row = account_details(in_request("ID"), in_request("U")); > + $PK = implode("\n", account_get_ssh_keys($row["ID"])); > } > > if ($action == "AccountInfo") { > @@ -59,7 +60,7 @@ if (isset($_COOKIE["AURSID"])) { > display_account_form("UpdateAccount", > $row["Username"], > $row["AccountTypeID"], > $row["Suspended"], $row["Email"], > "", "", $row["RealName"], > $row["LangPreference"], > - $row["IRCNick"], $row["PGPKey"], > $row["SSHPubKey"], > + $row["IRCNick"], $row["PGPKey"], $PK, > $row["InactivityTS"] ? 1 : 0, > $row["ID"]); > } else { > print __("You do not have permission to edit > this account."); > diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php > index 6b7d227..417ee6d 100644 > --- a/web/lib/acctfuncs.inc.php > +++ b/web/lib/acctfuncs.inc.php > @@ -53,7 +53,7 @@ function html_format_pgp_fingerprint($fingerprint) { > * @param string $L The language preference of the displayed user > * @param string $I The IRC nickname of the displayed user > * @param string $K The PGP key fingerprint of the displayed user > - * @param string $PK The SSH public key of the displayed user > + * @param string $PK The list of SSH public keys > * @param string $J The inactivity status of the displayed user > * @param string $UID The user ID of the displayed user > * > @@ -83,7 +83,7 @@ function > display_account_form($A,$U="",$T="",$S="",$E="",$P="",$C="",$R="", > * @param string $L The language preference of the user > * @param string $I The IRC nickname of the user > * @param string $K The PGP fingerprint of the user > - * @param string $PK The SSH public key of the user > + * @param string $PK The list of public SSH keys > * @param string $J The inactivity status of the user > * @param string $UID The user ID of the modified account > * > @@ -149,12 +149,32 @@ function > process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$P="",$C="", > } > > if (!$error && !empty($PK)) { > - if (valid_ssh_pubkey($PK)) { > - $tokens = explode(" ", $PK); > - $PK = $tokens[0] . " " . $tokens[1]; > - } else { > - $error = __("The SSH public key is invalid."); > + $ssh_keys = array_filter(array_map('trim', explode("\n", $PK))); > + $ssh_fingerprints = array(); > + > + foreach ($ssh_keys as &$ssh_key) { > + if (!valid_ssh_pubkey($ssh_key)) { > + $error = __("The SSH public key is invalid."); > + break; > + } > + > + $ssh_fingerprint = ssh_key_fingerprint($ssh_key); > + if (!$ssh_fingerprint) { > + $error = __("The SSH public key is invalid."); > + break; > + } > + > + $tokens = explode(" ", $ssh_key); > + $ssh_key = $tokens[0] . " " . $tokens[1]; > + > + $ssh_fingerprints[] = $ssh_fingerprint; > } > + > + /* > + * Destroy last reference to prevent accidentally overwriting > + * an array element. > + */ > + unset($ssh_key); > } > > if (isset($_COOKIE['AURSID'])) { > @@ -203,22 +223,24 @@ function > process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$P="",$C="", > "<strong>", > htmlspecialchars($E,ENT_QUOTES), "</strong>"); > } > } > - if (!$error && !empty($PK)) { > + if (!$error && count($ssh_keys) > 0) { > /* > - * Check whether the SSH public key is available. > + * Check whether any of the SSH public keys is already in use. > * TODO: Fix race condition. > */ > - $q = "SELECT COUNT(*) FROM Users "; > - $q.= "WHERE SSHPubKey = " . $dbh->quote($PK); > + $q = "SELECT Fingerprint FROM SSHPubKeys "; > + $q.= "WHERE Fingerprint IN ("; > + $q.= implode(',', array_map(array($dbh, 'quote'), > $ssh_fingerprints)); > + $q.= ")"; > if ($TYPE == "edit") { > - $q.= " AND ID != " . intval($UID); > + $q.= " AND UserID != " . intval($UID); > } > $result = $dbh->query($q); > $row = $result->fetch(PDO::FETCH_NUM); > > - if ($row[0]) { > + if ($row) { > $error = __("The SSH public key, %s%s%s, is already in > use.", > - "<strong>", htmlspecialchars($PK, > ENT_QUOTES), "</strong>"); > + "<strong>", htmlspecialchars($row[0], > ENT_QUOTES), "</strong>"); > } > } > > @@ -247,13 +269,11 @@ function > process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$P="",$C="", > $L = $dbh->quote($L); > $I = $dbh->quote($I); > $K = $dbh->quote(str_replace(" ", "", $K)); > - $PK = empty($PK) ? "NULL" : $dbh->quote($PK); > $q = "INSERT INTO Users (AccountTypeID, Suspended, "; > $q.= "InactivityTS, Username, Email, Passwd, Salt, "; > - $q.= "RealName, LangPreference, IRCNick, PGPKey, "; > - $q.= "SSHPubKey) "; > + $q.= "RealName, LangPreference, IRCNick, PGPKey) "; > $q.= "VALUES (1, 0, 0, $U, $E, $P, $salt, $R, $L, "; > - $q.= "$I, $K, $PK)"; > + $q.= "$I, $K)"; > $result = $dbh->exec($q); > if (!$result) { > print __("Error trying to create account, %s%s%s.", > @@ -261,6 +281,9 @@ function > process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$P="",$C="", > return; > } > > + $uid = $dbh->lastInsertId(); > + account_set_ssh_keys($uid, $ssh_keys, $ssh_fingerprints); > + > print __("The account, %s%s%s, has been successfully created.", > "<strong>", htmlspecialchars($U,ENT_QUOTES), > "</strong>"); > print "<p>\n"; > @@ -321,10 +344,12 @@ function > process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$P="",$C="", > $q.= ", LangPreference = " . $dbh->quote($L); > $q.= ", IRCNick = " . $dbh->quote($I); > $q.= ", PGPKey = " . $dbh->quote(str_replace(" ", "", $K)); > - $q.= ", SSHPubKey = " . $dbh->quote($PK); > $q.= ", InactivityTS = " . $inactivity_ts; > $q.= " WHERE ID = ".intval($UID); > $result = $dbh->exec($q); > + > + account_set_ssh_keys($UID, $ssh_keys, $ssh_fingerprints); > + > if (!$result) { > print __("No changes were made to the account, %s%s%s.", > "<strong>", > htmlspecialchars($U,ENT_QUOTES), "</strong>"); > @@ -1194,3 +1219,93 @@ function can_edit_account($acctinfo) { > $uid = $acctinfo['ID']; > return has_credential(CRED_ACCOUNT_EDIT, array($uid)); > } > + > +/* > + * Compute the fingerprint of an SSH key. > + * > + * @param string $ssh_key The SSH public key to retrieve the fingerprint for > + * > + * @return string The SSH key fingerprint > + */ > +function ssh_key_fingerprint($ssh_key) { > + $tmpfile = tempnam(sys_get_temp_dir(), "aurweb"); > + file_put_contents($tmpfile, $ssh_key); > + > + /* > + * The -l option of ssh-keygen can be used to show the fingerprint of > + * the specified public key file. Expected output format: > + * > + * 2048 SHA256:uBBTXmCNjI2CnLfkuz9sG8F+e9/T4C+qQQwLZWIODBY > user@host (RSA) > + * > + * ... where 2048 is the key length, the second token is the actual > + * fingerprint, followed by the key comment and the key type. > + */ > + > + $cmd = "/usr/bin/ssh-keygen -l -f " . escapeshellarg($tmpfile); > + exec($cmd, $out, $ret); > + if ($ret !== 0 || count($out) !== 1) { > + return false; > + } > + > + unlink($tmpfile); > + > + $tokens = explode(' ', $out[0]); > + if (count($tokens) != 4) { > + return false; > + } > + > + $tokens = explode(':', $tokens[1]); > + if (count($tokens) != 2 || $tokens[0] != 'SHA256') { > + return false; > + } > + > + return $tokens[1]; > +} > + > +/* > + * Get the SSH public keys associated with an account. > + * > + * @param int $uid The user ID of the account to retrieve the keys for. > + * > + * @return array An array representing the keys > + */ > +function account_get_ssh_keys($uid) { > + $dbh = DB::connect(); > + $q = "SELECT PubKey FROM SSHPubKeys WHERE UserID = " . intval($uid); > + $result = $dbh->query($q); > + > + if ($result) { > + return $result->fetchAll(PDO::FETCH_COLUMN, 0); > + } else { > + return array(); > + } > +} > + > +/* > + * Set the SSH public keys associated with an account. > + * > + * @param int $uid The user ID of the account to assign the keys to. > + * @param array $ssh_keys The SSH public keys. > + * @param array $ssh_fingerprints The corresponding SSH key fingerprints. > + * > + * @return bool Boolean flag indicating success or failure. > + */ > +function account_set_ssh_keys($uid, $ssh_keys, $ssh_fingerprints) { > + $dbh = DB::connect(); > + > + $q = sprintf("DELETE FROM SSHPubKeys WHERE UserID = %d", $uid); > + $dbh->exec($q); > + > + $ssh_fingerprint = reset($ssh_fingerprints); > + foreach ($ssh_keys as $ssh_key) { > + $q = sprintf( > + "INSERT INTO SSHPubKeys (UserID, Fingerprint, PubKey) " > . > + "VALUES (%d, %s, %s)", $uid, > + $dbh->quote($ssh_fingerprint), $dbh->quote($ssh_key) > + ); > + $dbh->exec($q); > + $ssh_fingerprint = next($ssh_fingerprints); > + } > + > + return true; > +} > -- > 2.4.4
Acked-by --
