1 """
2 This module contains C{L{OpenIDStore}} implementations that use
3 various SQL databases to back them.
4 """
5 import time
6
7 from openid import cryptutil
8 from openid.association import Association
9 from openid.store.interface import OpenIDStore
10
12 def wrapped(self, *args, **kwargs):
13 return self._callInTransaction(func, self, *args, **kwargs)
14
15 if hasattr(func, '__name__'):
16 try:
17 wrapped.__name__ = func.__name__[4:]
18 except TypeError:
19 pass
20
21 if hasattr(func, '__doc__'):
22 wrapped.__doc__ = func.__doc__
23
24 return wrapped
25
27 """
28 This is the parent class for the SQL stores, which contains the
29 logic common to all of the SQL stores.
30
31 The table names used are determined by the class variables
32 C{L{settings_table}}, C{L{associations_table}}, and
33 C{L{nonces_table}}. To change the name of the tables used, pass
34 new table names into the constructor.
35
36 To create the tables with the proper schema, see the
37 C{L{createTables}} method.
38
39 This class shouldn't be used directly. Use one of its subclasses
40 instead, as those contain the code necessary to use a specific
41 database.
42
43 All methods other than C{L{__init__}} and C{L{createTables}}
44 should be considered implementation details.
45
46
47 @cvar settings_table: This is the default name of the table to
48 keep this store's settings in.
49
50 @cvar associations_table: This is the default name of the table to
51 keep associations in
52
53 @cvar nonces_table: This is the default name of the table to keep
54 nonces in.
55
56
57 @sort: __init__, createTables
58 """
59
60 settings_table = 'oid_settings'
61 associations_table = 'oid_associations'
62 nonces_table = 'oid_nonces'
63
64 - def __init__(self, conn, settings_table=None, associations_table=None,
65 nonces_table=None):
66 """
67 This creates a new SQLStore instance. It requires an
68 established database connection be given to it, and it allows
69 overriding the default table names.
70
71
72 @param conn: This must be an established connection to a
73 database of the correct type for the SQLStore subclass
74 you're using.
75
76 @type conn: A python database API compatible connection
77 object.
78
79
80 @param settings_table: This is an optional parameter to
81 specify the name of the table used for this store's
82 settings. The default value is specified in
83 C{L{SQLStore.settings_table}}.
84
85 @type settings_table: C{str}
86
87
88 @param associations_table: This is an optional parameter to
89 specify the name of the table used for storing
90 associations. The default value is specified in
91 C{L{SQLStore.associations_table}}.
92
93 @type associations_table: C{str}
94
95
96 @param nonces_table: This is an optional parameter to specify
97 the name of the table used for storing nonces. The
98 default value is specified in C{L{SQLStore.nonces_table}}.
99
100 @type nonces_table: C{str}
101 """
102 self.conn = conn
103 self.cur = None
104 self._statement_cache = {}
105 self._table_names = {
106 'settings': settings_table or self.settings_table,
107 'associations': associations_table or self.associations_table,
108 'nonces': nonces_table or self.nonces_table,
109 }
110 self.max_nonce_age = 6 * 60 * 60
111
113 """Convert a blob as returned by the SQL engine into a str object.
114
115 str -> str"""
116 return blob
117
119 """Convert a str object into the necessary object for storing
120 in the database as a blob."""
121 return s
122
124 try:
125 return self._statement_cache[sql_name]
126 except KeyError:
127 sql = getattr(self, sql_name)
128 sql %= self._table_names
129 self._statement_cache[sql_name] = sql
130 return sql
131
133 sql = self._getSQL(sql_name)
134 self.cur.execute(sql, args)
135
137
138
139
140 if attr[:3] == 'db_':
141 sql_name = attr[3:] + '_sql'
142 def func(*args):
143 return self._execSQL(sql_name, *args)
144 setattr(self, attr, func)
145 return func
146 else:
147 raise AttributeError('Attribute %r not found' % (attr,))
148
150 """Execute the given function inside of a transaction, with an
151 open cursor. If no exception is raised, the transaction is
152 comitted, otherwise it is rolled back."""
153
154 self.conn.rollback()
155
156 try:
157 self.cur = self.conn.cursor()
158 try:
159 ret = func(*args, **kwargs)
160 finally:
161 self.cur.close()
162 self.cur = None
163 except:
164 self.conn.rollback()
165 raise
166 else:
167 self.conn.commit()
168
169 return ret
170
172 """
173 This method creates the database tables necessary for this
174 store to work. It should not be called if the tables already
175 exist.
176 """
177 self.db_create_nonce()
178 self.db_create_assoc()
179 self.db_create_settings()
180
181 createTables = _inTxn(txn_createTables)
182
184 """Get the key for this consumer to use to sign its own
185 communications. This function will create a new key if one
186 does not yet exist.
187
188 () -> str
189 """
190 self.db_get_auth()
191 val = self.cur.fetchone()
192 if val is None:
193 auth_key = cryptutil.randomString(self.AUTH_KEY_LEN)
194 auth_key_s = self.blobEncode(auth_key)
195 self.db_create_auth(auth_key_s)
196 else:
197 (auth_key_s,) = val
198 auth_key = self.blobDecode(auth_key_s)
199
200 if len(auth_key) != self.AUTH_KEY_LEN:
201 fmt = 'Expected %d-byte string for auth key. Got %r'
202 raise ValueError(fmt % (self.AUTH_KEY_LEN, auth_key))
203
204 return auth_key
205
206 getAuthKey = _inTxn(txn_getAuthKey)
207
209 """Set the association for the server URL.
210
211 Association -> NoneType
212 """
213 a = association
214 self.db_set_assoc(
215 server_url,
216 a.handle,
217 self.blobEncode(a.secret),
218 a.issued,
219 a.lifetime,
220 a.assoc_type)
221
222 storeAssociation = _inTxn(txn_storeAssociation)
223
225 """Get the most recent association that has been set for this
226 server URL and handle.
227
228 str -> NoneType or Association
229 """
230 if handle is not None:
231 self.db_get_assoc(server_url, handle)
232 else:
233 self.db_get_assocs(server_url)
234
235 rows = self.cur.fetchall()
236 if len(rows) == 0:
237 return None
238 else:
239 associations = []
240 for values in rows:
241 assoc = Association(*values)
242 assoc.secret = self.blobDecode(assoc.secret)
243 if assoc.getExpiresIn() == 0:
244 self.txn_removeAssociation(server_url, assoc.handle)
245 else:
246 associations.append((assoc.issued, assoc))
247
248 if associations:
249 associations.sort()
250 return associations[-1][1]
251 else:
252 return None
253
254 getAssociation = _inTxn(txn_getAssociation)
255
257 """Remove the association for the given server URL and handle,
258 returning whether the association existed at all.
259
260 (str, str) -> bool
261 """
262 self.db_remove_assoc(server_url, handle)
263 return self.cur.rowcount > 0
264
265 removeAssociation = _inTxn(txn_removeAssociation)
266
268 """Add this nonce to the set of extant nonces, ignoring if it
269 is already present.
270
271 str -> NoneType
272 """
273 now = int(time.time())
274 self.db_add_nonce(nonce, now)
275
276 storeNonce = _inTxn(txn_storeNonce)
277
279 """Return whether this nonce is present, and if it is, then
280 remove it from the set.
281
282 str -> bool"""
283 self.db_get_nonce(nonce)
284 row = self.cur.fetchone()
285 if row is not None:
286 (nonce, timestamp) = row
287 nonce_age = int(time.time()) - timestamp
288 if nonce_age > self.max_nonce_age:
289 present = 0
290 else:
291 present = 1
292
293 self.db_remove_nonce(nonce)
294 else:
295 present = 0
296
297 return present
298
299 useNonce = _inTxn(txn_useNonce)
300
302 """
303 This is an SQLite-based specialization of C{L{SQLStore}}.
304
305 To create an instance, see C{L{SQLStore.__init__}}. To create the
306 tables it will use, see C{L{SQLStore.createTables}}.
307
308 All other methods are implementation details.
309 """
310
311 create_nonce_sql = """
312 CREATE TABLE %(nonces)s
313 (
314 nonce CHAR(8) UNIQUE PRIMARY KEY,
315 expires INTEGER
316 );
317 """
318
319 create_assoc_sql = """
320 CREATE TABLE %(associations)s
321 (
322 server_url VARCHAR(2047),
323 handle VARCHAR(255),
324 secret BLOB(128),
325 issued INTEGER,
326 lifetime INTEGER,
327 assoc_type VARCHAR(64),
328 PRIMARY KEY (server_url, handle)
329 );
330 """
331
332 create_settings_sql = """
333 CREATE TABLE %(settings)s
334 (
335 setting VARCHAR(128) UNIQUE PRIMARY KEY,
336 value BLOB(20)
337 );
338 """
339
340 create_auth_sql = 'INSERT INTO %(settings)s VALUES ("auth_key", ?);'
341 get_auth_sql = 'SELECT value FROM %(settings)s WHERE setting = "auth_key";'
342
343 set_assoc_sql = ('INSERT OR REPLACE INTO %(associations)s '
344 'VALUES (?, ?, ?, ?, ?, ?);')
345 get_assocs_sql = ('SELECT handle, secret, issued, lifetime, assoc_type '
346 'FROM %(associations)s WHERE server_url = ?;')
347 get_assoc_sql = (
348 'SELECT handle, secret, issued, lifetime, assoc_type '
349 'FROM %(associations)s WHERE server_url = ? AND handle = ?;')
350
351 remove_assoc_sql = ('DELETE FROM %(associations)s '
352 'WHERE server_url = ? AND handle = ?;')
353
354 add_nonce_sql = 'INSERT OR REPLACE INTO %(nonces)s VALUES (?, ?);'
355 get_nonce_sql = 'SELECT * FROM %(nonces)s WHERE nonce = ?;'
356 remove_nonce_sql = 'DELETE FROM %(nonces)s WHERE nonce = ?;'
357
359 return str(buf)
360
362 return buffer(s)
363
365 """
366 This is a MySQL-based specialization of C{L{SQLStore}}.
367
368 Uses InnoDB tables for transaction support.
369
370 To create an instance, see C{L{SQLStore.__init__}}. To create the
371 tables it will use, see C{L{SQLStore.createTables}}.
372
373 All other methods are implementation details.
374 """
375
376 create_nonce_sql = """
377 CREATE TABLE %(nonces)s
378 (
379 nonce CHAR(8) UNIQUE PRIMARY KEY,
380 expires INTEGER
381 )
382 TYPE=InnoDB;
383 """
384
385 create_assoc_sql = """
386 CREATE TABLE %(associations)s
387 (
388 server_url BLOB,
389 handle VARCHAR(255),
390 secret BLOB,
391 issued INTEGER,
392 lifetime INTEGER,
393 assoc_type VARCHAR(64),
394 PRIMARY KEY (server_url(255), handle)
395 )
396 TYPE=InnoDB;
397 """
398
399 create_settings_sql = """
400 CREATE TABLE %(settings)s
401 (
402 setting VARCHAR(128) UNIQUE PRIMARY KEY,
403 value BLOB
404 )
405 TYPE=InnoDB;
406 """
407
408 create_auth_sql = 'INSERT INTO %(settings)s VALUES ("auth_key", %%s);'
409 get_auth_sql = 'SELECT value FROM %(settings)s WHERE setting = "auth_key";'
410
411 set_assoc_sql = ('REPLACE INTO %(associations)s '
412 'VALUES (%%s, %%s, %%s, %%s, %%s, %%s);')
413 get_assocs_sql = ('SELECT handle, secret, issued, lifetime, assoc_type'
414 ' FROM %(associations)s WHERE server_url = %%s;')
415 get_assoc_sql = (
416 'SELECT handle, secret, issued, lifetime, assoc_type'
417 ' FROM %(associations)s WHERE server_url = %%s AND handle = %%s;')
418 remove_assoc_sql = ('DELETE FROM %(associations)s '
419 'WHERE server_url = %%s AND handle = %%s;')
420
421 add_nonce_sql = 'REPLACE INTO %(nonces)s VALUES (%%s, %%s);'
422 get_nonce_sql = 'SELECT * FROM %(nonces)s WHERE nonce = %%s;'
423 remove_nonce_sql = 'DELETE FROM %(nonces)s WHERE nonce = %%s;'
424
426 return blob.tostring()
427
428 -class PostgreSQLStore(SQLStore):
429 """
430 This is a PostgreSQL-based specialization of C{L{SQLStore}}.
431
432 To create an instance, see C{L{SQLStore.__init__}}. To create the
433 tables it will use, see C{L{SQLStore.createTables}}.
434
435 All other methods are implementation details.
436 """
437
438 create_nonce_sql = """
439 CREATE TABLE %(nonces)s
440 (
441 nonce CHAR(8) UNIQUE PRIMARY KEY,
442 expires INTEGER
443 );
444 """
445
446 create_assoc_sql = """
447 CREATE TABLE %(associations)s
448 (
449 server_url VARCHAR(2047),
450 handle VARCHAR(255),
451 secret BYTEA,
452 issued INTEGER,
453 lifetime INTEGER,
454 assoc_type VARCHAR(64),
455 PRIMARY KEY (server_url, handle),
456 CONSTRAINT secret_length_constraint CHECK (LENGTH(secret) <= 128)
457 );
458 """
459
460 create_settings_sql = """
461 CREATE TABLE %(settings)s
462 (
463 setting VARCHAR(128) UNIQUE PRIMARY KEY,
464 value BYTEA,
465 CONSTRAINT value_length_constraint CHECK (LENGTH(value) <= 20)
466 );
467 """
468
469 create_auth_sql = "INSERT INTO %(settings)s VALUES ('auth_key', %%s);"
470 get_auth_sql = "SELECT value FROM %(settings)s WHERE setting = 'auth_key';"
471
472 - def db_set_assoc(self, server_url, handle, secret, issued, lifetime, assoc_type):
473 """
474 Set an association. This is implemented as a method because
475 REPLACE INTO is not supported by PostgreSQL (and is not
476 standard SQL).
477 """
478 result = self.db_get_assoc(server_url, handle)
479 rows = self.cur.fetchall()
480 if len(rows):
481
482 return self.db_update_assoc(secret, issued, lifetime, assoc_type,
483 server_url, handle)
484 else:
485
486
487 return self.db_new_assoc(server_url, handle, secret, issued,
488 lifetime, assoc_type)
489
490 new_assoc_sql = ('INSERT INTO %(associations)s '
491 'VALUES (%%s, %%s, %%s, %%s, %%s, %%s);')
492 update_assoc_sql = ('UPDATE %(associations)s SET '
493 'secret = %%s, issued = %%s, '
494 'lifetime = %%s, assoc_type = %%s '
495 'WHERE server_url = %%s AND handle = %%s;')
496 get_assocs_sql = ('SELECT handle, secret, issued, lifetime, assoc_type'
497 ' FROM %(associations)s WHERE server_url = %%s;')
498 get_assoc_sql = (
499 'SELECT handle, secret, issued, lifetime, assoc_type'
500 ' FROM %(associations)s WHERE server_url = %%s AND handle = %%s;')
501 remove_assoc_sql = ('DELETE FROM %(associations)s '
502 'WHERE server_url = %%s AND handle = %%s;')
503
504 - def db_add_nonce(self, nonce, expires):
505 """
506 Set a nonce. This is implemented as a method because REPLACE
507 INTO is not supported by PostgreSQL (and is not standard SQL).
508 """
509 self.db_get_nonce(nonce)
510 rows = self.cur.fetchall()
511 if len(rows):
512
513 return self.db_update_nonce(expires, nonce)
514 else:
515
516 return self.db_new_nonce(nonce, expires)
517
518 update_nonce_sql = 'UPDATE %(nonces)s SET expires = %%s WHERE nonce = %%s;'
519 new_nonce_sql = 'INSERT INTO %(nonces)s VALUES (%%s, %%s);'
520 get_nonce_sql = 'SELECT * FROM %(nonces)s WHERE nonce = %%s;'
521 remove_nonce_sql = 'DELETE FROM %(nonces)s WHERE nonce = %%s;'
522
523 - def blobEncode(self, blob):
524 import psycopg
525 return psycopg.Binary(blob)
526