#306 Create OpenID nonce table
Opened 2 years ago by dbnicholson. Modified 5 months ago
dbnicholson/ipsilon openid-nonce-table  into  master

@@ -1,6 +1,7 @@ 

  # Copyright (C) 2014 Ipsilon project Contributors, for license see COPYING

  

- from ipsilon.util.data import Store, UNIQUE_DATA_TABLE, OPTIONS_TABLE

+ from ipsilon.util.data import (Store, UNIQUE_DATA_TABLE, OPTIONS_TABLE,

+                                CURRENT_SCHEMA_VERSION)

  

  from openid import oidutil

  from openid.association import Association
@@ -10,6 +11,9 @@ 

  from time import time

  

  

+ CURRENT_OPENID_SCHEMA_VERSION = CURRENT_SCHEMA_VERSION + 1

+ 

+ 

  class OpenIDStore(Store, OpenIDStoreInterface):

      _auto_cleanup_tables = ['association', 'nonce']

  
@@ -75,10 +79,14 @@ 

          # This is automatically cleaned up

          return

  

+     def _code_schema_version(self):

+         return CURRENT_OPENID_SCHEMA_VERSION

+ 

      def _initialize_schema(self):

-         q = self._query(self._db, 'association', UNIQUE_DATA_TABLE,

-                         trans=False)

-         q.create()

+         for tablename in ['association', 'nonce']:

+             q = self._query(self._db, tablename, UNIQUE_DATA_TABLE,

+                             trans=False)

+             q.create()

  

      def _upgrade_schema(self, old_version):

          if old_version == 1:
@@ -97,5 +105,11 @@ 

              return 2

          elif old_version == 2:

              return 3

+         elif old_version == 3:

+             # In OpenID schema version 4 the missing nonce table was added

+             q = self._query(self._db, 'nonce', UNIQUE_DATA_TABLE,

+                             trans=False)

+             q.create()

+             return 4

          else:

              raise NotImplementedError()

@@ -0,0 +1,21 @@ 

+ PRAGMA foreign_keys=OFF;

+ BEGIN TRANSACTION;

+ CREATE TABLE dbinfo (

+ 	name TEXT NOT NULL, 

+ 	option TEXT NOT NULL, 

+ 	value TEXT

+ );

+ INSERT INTO "dbinfo" VALUES('OpenIDStore_schema','version','3');

+ CREATE TABLE association (

+ 	uuid TEXT NOT NULL, 

+ 	name TEXT NOT NULL, 

+ 	value TEXT

+ );

+ CREATE TABLE openid_extensions (

+     name TEXT NOT NULL,

+     option TEXT NOT NULL,

+     value TEXT

+ );

+ CREATE INDEX idx_association_uuid ON association (uuid);

+ CREATE INDEX idx_openid_extensions_name ON openid_extensions (name);

+ COMMIT;

file modified
+28 -2
@@ -10,6 +10,7 @@ 

  import sys

  import signal

  import subprocess

+ from ipsilon.providers.openid.store import CURRENT_OPENID_SCHEMA_VERSION

  import ipsilon.util.data

  

  idp_g = {'TEMPLATES': '${TESTDIR}/templates/install',
@@ -92,11 +93,16 @@ 

              self.use_readonly_adminconfig(name)

  

          if old_version > 0:

-             for database in ['adminconfig',

+             if old_version <= ipsilon.util.data.CURRENT_SCHEMA_VERSION:

+                 databases = ['adminconfig',

                               'openid',

                               'saml2.sessions.db',

                               'transactions',

-                              'userprefs']:

+                              'userprefs']

+             else:

+                 databases = ['openid']

+ 

+             for database in databases:

                  db_in = os.path.join(db_indir, '%s.sqlite.dump' % database)

                  db_out = os.path.join(db_outdir, '%s.sqlite' % database)

                  os.unlink(db_out)
@@ -143,6 +149,15 @@ 

                  raise Exception('Database upgrade did not introduce ' +

                                  'authz_config table')

  

+         elif old_version > ipsilon.util.data.CURRENT_SCHEMA_VERSION:

+             # Database specific schema changes

+             if old_version == 3:

+                 # OpenID version 4 added the nonce table

+                 output = self.dump_db(db_outdir, with_readonly)

+                 if b'TABLE nonce' not in output:

+                     raise Exception('OpenID database upgrade did not ' +

+                                     'introduce nonce table')

+ 

          # Start the httpd server

          http_server = self.start_http_server(conf, env)

  
@@ -180,6 +195,17 @@ 

                  overall_exit_code = 1

              overall_results.extend(results)

  

+         for version in range(ipsilon.util.data.CURRENT_SCHEMA_VERSION,

+                              CURRENT_OPENID_SCHEMA_VERSION)

+             for with_readonly in [True, False]:

+                 exit_code, results = self.test_upgrade_from(env,

+                                                             version,

+                                                             with_readonly)

+ 

+             if exit_code != 0:

+                 overall_exit_code = 1

+             overall_results.extend(results)

+ 

          return overall_exit_code, overall_results

  

  

OpenIDStore registers the nonce table for automatic cleanup, but it's
never created. Even though the table isn't required, a SQL error is
logged every time automatic cleanup is triggered if it doesn't exist.

Fixes: #240

Signed-off-by: Dan Nicholson nicholson@endlessm.com

Sorry, didn't checked the issue.

Otherwise it looks good to me.

Hi,

Thanks for your patch, and I'm sorry for the delay in responding to this!
Unfortunately, since the "nonce" table wasn't created previously, we shouldn't assume it exists on database upgrades (the "dbupgrades" test shows that this fails).
Instead, this should bump the schema version for OpenID and create the nonce table on upgrade.
The creation on initialization does look fine to me.

Given the delay in my reply, I'm willing to fix the patch up if you'd prefer, just let me know.

Unfortunately, since the "nonce" table wasn't created previously, we shouldn't assume it exists on database upgrades (the "dbupgrades" test shows that this fails).
Instead, this should bump the schema version for OpenID and create the nonce table on upgrade.

Makes sense. I only see one schema version and not one specifically for OpenID. Or are you saying that I should modify the 2 to 3 upgrade so that it creates the "nonce" table?

Or are you saying that I should modify the 2 to 3 upgrade so that it creates the "nonce" table?

Oh, that wouldn't work either since schema version 2 didn't have it. So, should I bump CURRENT_SCHEMA_VERSION to 4 and add no-op 3 to 4 upgrades everywhere except in openid where it creates the nonce table?

@dbnicholson @puiterwijk @simo Do we still need this patch?

@hellcp and I are seeing this with git master code:

[Sat Apr 25 17:22:18.959285 2020] [wsgi:error] [pid 27131:tid 139714212304640] [remote 192.168.47.101:49288] 192.168.47.101 - - [25/Apr/2020:17:22:18] "OPTIONS / HTTP/1.1" 200 1846 "" ""
[Sat Apr 25 17:22:18.960620 2020] [wsgi:error] [pid 27131:tid 139714229090048] [remote 192.168.47.102:50498] 192.168.47.102 - - [25/Apr/2020:17:22:18] "OPTIONS / HTTP/1.1" 200 1846 "" ""
[Sat Apr 25 17:22:20.798523 2020] [wsgi:error] [pid 27131:tid 139714111174400] Exception in thread Thread-1:
[Sat Apr 25 17:22:20.798596 2020] [wsgi:error] [pid 27131:tid 139714111174400] Traceback (most recent call last):
[Sat Apr 25 17:22:20.798609 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1247, in _execute_context
[Sat Apr 25 17:22:20.800257 2020] [wsgi:error] [pid 27131:tid 139714111174400]     self.dialect.do_execute(
[Sat Apr 25 17:22:20.800309 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
[Sat Apr 25 17:22:20.803297 2020] [wsgi:error] [pid 27131:tid 139714111174400]     cursor.execute(statement, parameters)
[Sat Apr 25 17:22:20.803517 2020] [wsgi:error] [pid 27131:tid 139714111174400] sqlite3.OperationalError: no such table: nonce
[Sat Apr 25 17:22:20.803554 2020] [wsgi:error] [pid 27131:tid 139714111174400] 
[Sat Apr 25 17:22:20.803573 2020] [wsgi:error] [pid 27131:tid 139714111174400] The above exception was the direct cause of the following exception:
[Sat Apr 25 17:22:20.803902 2020] [wsgi:error] [pid 27131:tid 139714111174400] 
[Sat Apr 25 17:22:20.803960 2020] [wsgi:error] [pid 27131:tid 139714111174400] Traceback (most recent call last):
[Sat Apr 25 17:22:20.803993 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/threading.py", line 932, in _bootstrap_inner
[Sat Apr 25 17:22:20.806102 2020] [wsgi:error] [pid 27131:tid 139714111174400]     self.run()
[Sat Apr 25 17:22:20.806121 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib/python3.8/site-packages/cherrypy/process/plugins.py", line 517, in run
[Sat Apr 25 17:22:20.807774 2020] [wsgi:error] [pid 27131:tid 139714111174400]     self.function(*self.args, **self.kwargs)
[Sat Apr 25 17:22:20.807794 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib/python3.8/site-packages/ipsilon/util/data.py", line 760, in _maybe_run_cleanup
[Sat Apr 25 17:22:20.809424 2020] [wsgi:error] [pid 27131:tid 139714111174400]     auto_removed_entries = self._auto_cleanup()
[Sat Apr 25 17:22:20.809446 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib/python3.8/site-packages/ipsilon/util/data.py", line 783, in _auto_cleanup
[Sat Apr 25 17:22:20.809717 2020] [wsgi:error] [pid 27131:tid 139714111174400]     cleaned_table = q.perform_auto_cleanup()
[Sat Apr 25 17:22:20.809728 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib/python3.8/site-packages/ipsilon/util/data.py", line 276, in perform_auto_cleanup
[Sat Apr 25 17:22:20.809862 2020] [wsgi:error] [pid 27131:tid 139714111174400]     return d.execute().rowcount
[Sat Apr 25 17:22:20.809874 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/sql/base.py", line 414, in execute
[Sat Apr 25 17:22:20.810791 2020] [wsgi:error] [pid 27131:tid 139714111174400]     return e._execute_clauseelement(self, multiparams, params)
[Sat Apr 25 17:22:20.810808 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 2198, in _execute_clauseelement
[Sat Apr 25 17:22:20.811476 2020] [wsgi:error] [pid 27131:tid 139714111174400]     return connection._execute_clauseelement(elem, multiparams, params)
[Sat Apr 25 17:22:20.811490 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1097, in _execute_clauseelement
[Sat Apr 25 17:22:20.811830 2020] [wsgi:error] [pid 27131:tid 139714111174400]     ret = self._execute_context(
[Sat Apr 25 17:22:20.811841 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1287, in _execute_context
[Sat Apr 25 17:22:20.812237 2020] [wsgi:error] [pid 27131:tid 139714111174400]     self._handle_dbapi_exception(
[Sat Apr 25 17:22:20.812260 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1481, in _handle_dbapi_exception
[Sat Apr 25 17:22:20.812751 2020] [wsgi:error] [pid 27131:tid 139714111174400]     util.raise_(
[Sat Apr 25 17:22:20.812765 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
[Sat Apr 25 17:22:20.813358 2020] [wsgi:error] [pid 27131:tid 139714111174400]     raise exception
[Sat Apr 25 17:22:20.813375 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1247, in _execute_context
[Sat Apr 25 17:22:20.813791 2020] [wsgi:error] [pid 27131:tid 139714111174400]     self.dialect.do_execute(
[Sat Apr 25 17:22:20.813804 2020] [wsgi:error] [pid 27131:tid 139714111174400]   File "/usr/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
[Sat Apr 25 17:22:20.814050 2020] [wsgi:error] [pid 27131:tid 139714111174400]     cursor.execute(statement, parameters)
[Sat Apr 25 17:22:20.814159 2020] [wsgi:error] [pid 27131:tid 139714111174400] sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: nonce
[Sat Apr 25 17:22:20.814164 2020] [wsgi:error] [pid 27131:tid 139714111174400] [SQL: DELETE FROM nonce WHERE nonce.uuid IN (SELECT nonce.uuid 
[Sat Apr 25 17:22:20.814167 2020] [wsgi:error] [pid 27131:tid 139714111174400] FROM nonce 
[Sat Apr 25 17:22:20.814170 2020] [wsgi:error] [pid 27131:tid 139714111174400] WHERE nonce.name = ? AND nonce.value <= ?)]
[Sat Apr 25 17:22:20.814173 2020] [wsgi:error] [pid 27131:tid 139714111174400] [parameters: ('expiration_time', '2020-04-25 17:22:20.797368')]
[Sat Apr 25 17:22:20.814177 2020] [wsgi:error] [pid 27131:tid 139714111174400] (Background on this error at: http://sqlalche.me/e/e3q8)
[Sat Apr 25 17:22:20.814183 2020] [wsgi:error] [pid 27131:tid 139714111174400] 

But PR author does not allow me to rebase.
Please rebase your self and then we can merge.

Sorry I haven't had a lot of free time lately. I'll take a look at this later today.

But I still didn't get an answer to my earlier questions about the version. Maybe the code has changed in the meantime, but back then it appeared that there was only a single global version. Should that be bumped so a migration can be added?

@dbnicholson Yes, the db version needs to be bumped globally.

@dbnicholson Apparently, according to @puiterwijk:

the problem is that people are currently on schema version 2 (iirc), and if the adding of the table stays in the v0->v1 upgrade, people won't get the table added.

So you'd need to get the openid database schema bumped to a new version, and in that new version add the nonce table

Different databases can have different schema versions

Right, I gathered that. After reading through the code a bit more I see how you can have a different schema version per database, but it hasn't actually been used anywhere as far as I can tell. Well, I'll give it a shot and hopefully it's the right way to do it. It seems like you'd easily conflict with the global version, but I guess I can just increment from the global version in case there's another bump there.

rebased onto f525ec4

6 months ago

pretty please pagure-ci rebuild

6 months ago

1 new commit added

  • really add a test for openid version 4 upgrade
6 months ago

I added what I think was intended and some tests. I'll squash those all together if the tests pass. Could someone kick the CI for me?

Honestly I'm a bit unsure about the db specific schema and tests. The next time someone bumps the global schema version tests/dbupgrade.py will need to get pretty hairy.

@dbnicholson the CI is broken at the moment, I'm working on getting it fixed. But could you please rebase this PR? Once I get the CI online that's going to cause it to fail...

It needs to be rebased again? Which part is going to fail?

CI does a merge into a test branch before executing.

Then why would I need to rebase? Is there a conflict? How can you tell? Last time I rebased there were no conflicts.

I can tell because pagure is telling me there are merge conflicts in the PR at the top right.

Oh wait, nevermind, I misread the error. The error is the DCO signoff, nevermind. 🤦‍♂️