Package openid :: Package store :: Module sqlstore
[frames] | no frames]

Source Code for Module openid.store.sqlstore

  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   
11 -def _inTxn(func):
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
26 -class SQLStore(OpenIDStore):
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 # Six hours, in seconds
111
112 - def blobDecode(self, blob):
113 """Convert a blob as returned by the SQL engine into a str object. 114 115 str -> str""" 116 return blob
117
118 - def blobEncode(self, s):
119 """Convert a str object into the necessary object for storing 120 in the database as a blob.""" 121 return s
122
123 - def _getSQL(self, sql_name):
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
132 - def _execSQL(self, sql_name, *args):
133 sql = self._getSQL(sql_name) 134 self.cur.execute(sql, args)
135
136 - def __getattr__(self, attr):
137 # if the attribute starts with db_, use a default 138 # implementation that looks up the appropriate SQL statement 139 # as an attribute of this object and executes it. 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
149 - def _callInTransaction(self, func, *args, **kwargs):
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 # No nesting of transactions 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
171 - def txn_createTables(self):
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
183 - def txn_getAuthKey(self):
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
208 - def txn_storeAssociation(self, server_url, association):
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
224 - def txn_getAssociation(self, server_url, handle=None):
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
256 - def txn_removeAssociation(self, server_url, handle):
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 # -1 is undefined
264 265 removeAssociation = _inTxn(txn_removeAssociation) 266
267 - def txn_storeNonce(self, nonce):
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
278 - def txn_useNonce(self, nonce):
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
301 -class SQLiteStore(SQLStore):
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
358 - def blobDecode(self, buf):
359 return str(buf)
360
361 - def blobEncode(self, s):
362 return buffer(s)
363
364 -class MySQLStore(SQLStore):
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
425 - def blobDecode(self, blob):
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 # Update the table since this associations already exists. 482 return self.db_update_assoc(secret, issued, lifetime, assoc_type, 483 server_url, handle) 484 else: 485 # Insert a new record because this association wasn't 486 # found. 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 # Update the table since this nonce already exists. 513 return self.db_update_nonce(expires, nonce) 514 else: 515 # Insert a new record because this nonce wasn't found. 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