You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@steve.apache.org by gs...@apache.org on 2025/09/23 00:31:17 UTC
(steve) 03/05: Track the changes/scheme-redesign for a multi-election database.
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 <gs...@gmail.com>
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