This is an automated email from the ASF dual-hosted git repository. gstein pushed a commit to branch trunk in repository https://gitbox.apache.org/repos/asf/steve.git
commit 927a8555d71ee9f5d00090f67b4bdb1003818e9e Author: Greg Stein <[email protected]> AuthorDate: Mon Sep 22 18:49:23 2025 -0500 Track the changes/scheme-redesign for a multi-election database. Many queries and APIs need to change to track the schema changes. A new entrypoints for managing the mayvoter table. crypto.py: - remove the generalized gen_token() function; we only have/need gen_vote_token() now - create_vote() takes a vote_token rather than person/issue tokens. It also takes a salt in order to create an encryption key from the (high-entropy) vote_token. Only returns the ciphertext now. - added _b64_vote_key() to build an encryption key with the appropriate key-stretching and b64 encode/decode. Note: comments in here refer to a future change. - decrypt_votestring() is adjusted for the new vote_token and associated salt and uses _b64_vote_key() election.py: - in __init__() adjust the cursors needed for interaction with the database: - salting only occurs in "mayvote". Not "persons" nor "issues" - c_add_issue, c_add_person takes one less param (the old salt) - adjust c_add_vote to match new columns in "voters" table - two new cursors for adding rows to the "mayvote" table - q_metadata, q_issues now require an eid to find the rows for a given election - q_get_mayvote to get the salt for a given pid/iid pair - q_tally replaces q_by_issue to fetch all Persons that may be voting in a given Election. Then vote_tokens are computed and searches through "votes" will occur. - new m_find_isseues to find all issues that a given Person may vote on within a given Election. - new m_has_voted to check the "votes" table for any possible votes by Person on an Issue. - new m_recent_vote to select the most-recent votestring for a Person on an Issue. - new m_all_issues to select all the Person/Issue pairings for all issues within a given Election. - gather_election_data(): pass EID to the queries for data - add_salts(): update the SALT column in "mayvote", dropping salts for the "persons" and "issues" tables. - get_metadata(), is_tampered(), get_state(): pass EID to the q_metadata query. - add_issue(), add_person(): no more SALT column. remove insertion param - list_issues(): query now needs which EID is being queried for issues - new add_voter() to add rows into the "mayvote" table for the Issues that a given Person may vote upon (one specific, or all issues in this Election) - add_vote(): revise algorithm away from issue_token and person_token to compute a single vote_token (which is a primary key to find votes), then to use that to generate an encryption key. Record the resulting ciphertext. - tally_issue(): revise code to find all people eligible to vote on the given issue, then compute a vote_token, then look for any votes performed for that pair. Remove dedup code since we only select the most-recent vote via SQL. - has_voted_upon(): EID for q_metadata. Change algorithm to find all Issues (in this Election) that this person is eligible to vote upon. Construct a vote_token and see if at least 1 vote has been cast for the Issue. --- v3/steve/crypto.py | 48 ++++++++----- v3/steve/election.py | 189 ++++++++++++++++++++++++++++++--------------------- 2 files changed, 141 insertions(+), 96 deletions(-) diff --git a/v3/steve/crypto.py b/v3/steve/crypto.py index 4ea0baa..cb63c84 100644 --- a/v3/steve/crypto.py +++ b/v3/steve/crypto.py @@ -25,6 +25,9 @@ import passlib.hash # note that .argon2 is proxy in this pkg import passlib.utils # for the RNG, to create Salt values import cryptography.fernet +from cryptography.hazmat.primitives import hashes +from cryptography.hazmat.primitives.kdf import hkdf + # All salt values will be 16 bytes in length. After base64 encoding, they # will be represented with 22 characters. @@ -41,32 +44,41 @@ def gen_opened_key(edata: bytes, salt: bytes) -> bytes: return _hash(edata, salt) -def gen_token(opened_key: bytes, value: str, salt: bytes) -> bytes: +def gen_vote_token(opened_key: bytes, pid: str, iid: str, salt: bytes) -> bytes: "Generate a person or issue token." - return _hash(opened_key + value.encode(), salt) + return _hash(opened_key + pid.encode() + iid.encode(), salt) + + +def _b64_vote_key(vote_token: bytes, salt: bytes) -> str: + "Key-stretch the vote_token. (ref: PBKDF)" + ### still using Fernet now, but will switch soon. Leaving comments. + keymaker = hkdf.HKDF( + algorithm=hashes.SHA256(), + length=32, # 32-byte key for XChaCha20-Poly1305 + salt=salt, + info=b"xchacha20_key" + ) + vote_key = keymaker.derive(vote_token) + return base64.urlsafe_b64encode(vote_key) -### fix return type, to be a tuple -def create_vote(person_token: bytes, - issue_token: bytes, - votestring: str) -> bytes: - "Create a vote tuple, to record the VOTESTRING." - salt = gen_salt() - key = _hash(person_token + issue_token, salt) - b64key = base64.urlsafe_b64encode(key) + +def create_vote(vote_token: bytes, salt: bytes, votestring: str) -> bytes: + "Encrypt VOTESTRING using the VOTE_TOKEN and SALT." + + b64key = _b64_vote_key(vote_token, salt) f = cryptography.fernet.Fernet(b64key) - return salt, f.encrypt(votestring.encode()) + return f.encrypt(votestring.encode()) -def decrypt_votestring(person_token: bytes, - issue_token: bytes, +def decrypt_votestring(vote_token: bytes, salt: bytes, - token: bytes) -> str: - "Decrypt TOKEN into a VOTESTRING." - key = _hash(person_token + issue_token, salt) - b64key = base64.urlsafe_b64encode(key) + ciphertext: bytes) -> str: + "Decrypt CIPHERTEXT into a VOTESTRING." + + b64key = _b64_vote_key(vote_token, salt) f = cryptography.fernet.Fernet(b64key) - return f.decrypt(token).decode() + return f.decrypt(ciphertext).decode() def _hash(data: bytes, salt: bytes) -> bytes: diff --git a/v3/steve/election.py b/v3/steve/election.py index d76d883..b607edb 100644 --- a/v3/steve/election.py +++ b/v3/steve/election.py @@ -41,10 +41,8 @@ class Election: self.eid = eid # Construct cursors for all operations. - self.c_salt_issue = self.db.add_statement( - 'UPDATE ISSUES SET salt = ? WHERE _ROWID_ = ?') - self.c_salt_person = self.db.add_statement( - 'UPDATE PERSON SET salt = ? WHERE _ROWID_ = ?') + self.c_salt_mayvote = self.db.add_statement( + 'UPDATE mayvote SET salt = ? WHERE _ROWID_ = ?') self.c_open = self.db.add_statement( 'UPDATE ELECTIONS SET salt = ?, opened_key = ?' ' WHERE eid = ?') @@ -52,7 +50,7 @@ class Election: 'UPDATE ELECTIONS SET closed = 1' ' WHERE eid = ?') self.c_add_issue = self.db.add_statement( - '''INSERT INTO ISSUES VALUES (?, ?, ?, ?, ?, ?, ?) + '''INSERT INTO ISSUES VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT DO UPDATE SET title=excluded.title, description=excluded.description, @@ -60,7 +58,7 @@ class Election: kv=excluded.kv ''') self.c_add_person = self.db.add_statement( - '''INSERT INTO PERSON VALUES (?, ?, ?, ?) + '''INSERT INTO PERSON VALUES (?, ?, ?) ON CONFLICT DO UPDATE SET name=excluded.name, email=excluded.email @@ -70,27 +68,55 @@ class Election: self.c_delete_person = self.db.add_statement( 'DELETE FROM PERSON WHERE pid = ?') self.c_add_vote = self.db.add_statement( - 'INSERT INTO VOTES VALUES (NULL, ?, ?, ?, ?)') - self.c_has_voted = self.db.add_statement( - '''SELECT 1 FROM VOTES - WHERE person_token = ? AND issue_token = ? - LIMIT 1 - ''') + 'INSERT INTO VOTES VALUES (NULL, ?, ?)') + self.c_add_mayvote = self.db.add_statement( + 'INSERT INTO mayvote (pid, iid, salt) VALUES (?, ?, NULL)') + self.c_add_mayvote_all = self.db.add_statement( + 'INSERT INTO mayvote (pid, iid, salt)' + ' SELECT ?, iid, NULL FROM issues WHERE eid = ?') # Cursors for running queries. - ### need to add eid to the ISSUES queries; likely ELECTIONS self.q_metadata = self.db.add_query('elections', - 'SELECT * FROM ELECTIONS') + 'SELECT * FROM ELECTIONS WHERE eid = ?') self.q_issues = self.db.add_query('issues', - 'SELECT * FROM ISSUES ORDER BY iid') + 'SELECT * FROM ISSUES WHERE eid = ? ORDER BY iid') self.q_person = self.db.add_query('person', 'SELECT * FROM PERSON ORDER BY pid') self.q_get_issue = self.db.add_query('issues', 'SELECT * FROM ISSUES WHERE iid = ?') self.q_get_person = self.db.add_query('person', 'SELECT * FROM PERSON WHERE pid = ?') - self.q_by_issue = self.db.add_query('votes', - 'SELECT * FROM VOTES WHERE issue_token = ? ORDER BY _ROWID_') + self.q_get_mayvote = self.db.add_query('mayvote', + 'SELECT * FROM MAYVOTE WHERE pid = ? AND iid = ?') + self.q_tally = self.db.add_query('mayvote', + 'SELECT * FROM MAYVOTE WHERE iid = ?') + + # Manual queries: these queries do not follow the 'SELECT * ' + # pattern, so we cannot use .add_query() and will not have + # attribute access to the row results while running the query. + self.m_find_issues = self.db.add_statement( + '''SELECT m.* + FROM mayvote m + JOIN issues i ON m.iid = i.iid + WHERE m.pid = ? AND i.eid = ? + ''') + self.m_has_voted = self.db.add_statement( + '''SELECT 1 FROM VOTES + WHERE vote_token = ? + LIMIT 1 + ''') + self.m_recent_vote = self.db.add_statement( + '''SELECT ciphertext FROM VOTES + WHERE vote_token = ? + ORDER BY _ROWID_ DESC + LIMIT 1 + ''') + self.m_all_issues = self.db.add_statement( + '''SELECT m._ROWID_ + FROM mayvote m + JOIN issues i ON m.iid = i.iid + WHERE i.eid = ?; + ''') def open(self): @@ -119,10 +145,10 @@ class Election: # NOTE: all assembly of rows must use a repeatable ordering. - md = self.q_metadata.first_row() + md = self.q_metadata.first_row((self.eid,)) mdata = md.eid + md.title - self.q_issues.perform() + self.q_issues.perform((self.eid,)) # Use an f-string to render "None" if a column is NULL. idata = ''.join(f'{i.iid}{i.title}{i.description}{i.type}{i.kv}' for i in self.q_issues.fetchall()) @@ -143,34 +169,29 @@ class Election: self.c_close.perform((self.eid,)) def add_salts(self): - "Set the SALT column in the ISSUES and PERSON tables." + "Set the SALT column in the MAYVOTE table." # The Election should be editable. assert self.is_editable() - cur = self.db.conn.cursor() - - def for_table(table, mod_cursor): - "Use MOD_CURSOR to salt each row of TABLE." + # Use M_ALL_ISSUES to iterate over all Person/Issue mappings + # in this Election (specified by EID). + self.m_all_issues.execute('BEGIN TRANSACTION') + self.m_all_issues.perform((self.eid,)) + for mayvote in self.m_all_issues.fetchall(): + # MAYVOTE is a 1-tuple: _ROWID_ - # Fetch all ROWID values now, to avoid two cursors - # attempting to work on TABLE at the same time. - cur.execute(f'SELECT _ROWID_ FROM {table}') - ids = cur.fetchall() + # Use a distinct cursor to insert the SALT value. + salt = crypto.gen_salt() + #print('ROW will use:', table, r[0], salt) + self.c_salt_mayvote.perform((salt, mayvote[0])) - # Now, add a salt to every row. - for r in ids: - salt = crypto.gen_salt() - print('ROW will use:', table, r[0], salt) - mod_cursor.perform((salt, r[0])) - - for_table('issues', self.c_salt_issue) - for_table('person', self.c_salt_person) + self.m_all_issues.execute('COMMIT') def get_metadata(self): "Return basic metadata about this Election." - md = self.q_metadata.first_row() + md = self.q_metadata.first_row((self.eid,)) # NOTE: do not return the SALT # note: likely: never return opened_key @@ -193,7 +214,7 @@ class Election: # If we ADD, then SALT will be NULL. If we UPDATE, then it will not # be touched (it should be NULL). self.c_add_issue.perform((iid, eid, title, description, vtype, - self.kv2json(kv), None)) + self.kv2json(kv))) def delete_issue(self, iid): "Delete the Issue designated by IID." @@ -208,7 +229,7 @@ class Election: # NOTE: the SALT column is omitted. It should never be exposed. return row[:4] + (self.json2kv(row.kv),) - self.q_issues.perform() + self.q_issues.perform((self.eid,)) return [ extract_issue(row) for row in self.q_issues.fetchall() ] def get_person(self, pid): @@ -224,7 +245,7 @@ class Election: # If we ADD, then SALT will be NULL. If we UPDATE, then it will not # be touched (it should be NULL). - self.c_add_person.perform((pid, name, email, None)) + self.c_add_person.perform((pid, name, email,)) def delete_person(self, pid): "Delete the Person designated by PID." @@ -239,27 +260,31 @@ class Election: self.q_person.perform() return [ row[:3] for row in self.q_person.fetchall() ] - def add_vote(self, pid, iid, votestring): + def add_voter(self, pid: str, iid: str | None = None) -> None: + "Add PID (Person) to Issue IID, or to all Issues (None)." + + if iid: + self.c_add_mayvote.perform((pid, iid,)) + else: + self.c_add_mayvote_all.perform((pid, self.eid,)) + + def add_vote(self, pid: str, iid: str, votestring: str): "Add VOTESTRING as the (latest) vote by PID for IID." # The Election should be open. assert self.is_open() - md = self.q_metadata.first_row() - person = self.q_get_person.first_row((pid,)) - issue = self.q_get_issue.first_row((iid,)) + md = self.q_metadata.first_row((self.eid,)) ### validate VOTESTRING for ISSUE.TYPE voting - person_token = crypto.gen_token(md.opened_key, pid, person.salt) - #print('PERSON:', pid, person.salt, person_token) - issue_token = crypto.gen_token(md.opened_key, iid, issue.salt) - #print('ISSUE:', iid, issue.salt, issue_token) + mayvote = self.q_get_mayvote.first_row((pid, iid,)) + vote_token = crypto.gen_vote_token(md.opened_key, pid, iid, mayvote.salt) - salt, token = crypto.create_vote(person_token, issue_token, votestring) - #print('SALT:', salt) - #print('TOKEN:', token) - self.c_add_vote.perform((person_token, issue_token, salt, token)) + # Pass MAYVOTE.SALT for PBKDF. + ciphertext = crypto.create_vote(vote_token, mayvote.salt, votestring) + + self.c_add_vote.perform((vote_token, ciphertext)) def tally_issue(self, iid): """Return the results for a given ISSUE-ID. @@ -275,25 +300,37 @@ class Election: # The Election should be closed. assert self.is_closed() - md = self.q_metadata.first_row() + md = self.q_metadata.first_row((self.eid,)) + + # Need the issue TYPE issue = self.q_get_issue.first_row((iid,)) - issue_token = crypto.gen_token(md.opened_key, iid, issue.salt) - # Use this dict to retain "most recent" votes. - dedup = { } # (PERSON_TOKEN, ISSUE_TOKEN) : VOTESTRING + # Accumulate all MOST-RECENT votes for Issue IID. + votes = [ ] + + # Use mayvote to determine all potential voters for Issue IID. + self.q_tally.perform((iid,)) + for mayvote in self.q_tally.fetchall(): + # Each row is: PID, IID, SALT - self.q_by_issue.perform((issue_token,)) - for row in self.q_by_issue.fetchall(): + # For the given Person PID found, compute a VOTE_TOKEN. + vote_token = crypto.gen_vote_token( + md.opened_key, mayvote.pid, iid, mayvote.salt, + ) + + # We don't need/want all columns, so only pick CIPHERTEXT. + row = self.m_recent_vote.first_row((vote_token,)) votestring = crypto.decrypt_votestring( - row.person_token, issue_token, row.salt, row.token) - dedup[row.person_token, row.issue_token] = votestring + vote_token, mayvote.salt, row[0], + ) + votes.append(votestring) - # Make sure the votes are not in database-order. + # Make sure the votes are NOT in database-order. # Note: we are not returning the votes, so this may be # superfluous. But it certainly should not hurt. - votes = list(dedup.values()) crypto.shuffle(votes) # in-place + print('VOTES:', votes) # Perform the tally, and return the results. m = vtypes.vtype_module(issue.type) return m.tally(votes, self.json2kv(issue.kv)) @@ -304,25 +341,21 @@ class Election: # The Election should be open. assert self.is_open() - md = self.q_metadata.first_row() - person = self.q_get_person.first_row((pid,)) - person_token = crypto.gen_token(md.opened_key, pid, person.salt) + md = self.q_metadata.first_row((self.eid,)) voted_upon = { } - self.q_issues.perform() - for issue in self.q_issues.fetchall(): - issue_token = crypto.gen_token(md.opened_key, - issue.iid, - issue.salt) + self.m_find_issues.perform((pid, self.eid,)) + for row in self.m_find_issues.fetchall(): + # Query is mayvote.* ... so ROW is: PID, IID, SALT + vote_token = crypto.gen_vote_token( + md.opened_key, pid, row[1], row[2] + ) - # Is any vote present? - self.c_has_voted.perform((person_token, issue_token)) - row = self.c_has_voted.fetchone() - _ = self.c_has_voted.fetchall() # should be empty (LIMIT 1) + # Is any vote present? (wicked fast) + voted = self.m_has_voted.first_row((vote_token,)) - #print('HAS-VOTED:', row, '||', person_token, issue_token) - voted_upon[issue.iid] = row is not None + voted_upon[row[1]] = (voted is not None) return voted_upon @@ -331,7 +364,7 @@ class Election: # The Election should be open. assert self.is_open() - md = self.q_metadata.first_row() + md = self.q_metadata.first_row((self.eid,)) # Compute an opened_key based on the current data. edata = self.gather_election_data() @@ -359,7 +392,7 @@ class Election: def get_state(self): "Derive our election state from the METADATA table." - md = self.q_metadata.first_row() + md = self.q_metadata.first_row((self.eid,)) if md.closed == 1: assert md.salt is not None and md.opened_key is not None return self.S_CLOSED
