94
70
def __init__(self):
95
71
"""Connects to the database and creates a DB object.
96
72
Takes no parameters - gets all the DB info from the configuration."""
98
73
self.db = pg.connect(dbname=conf.db_dbname, host=conf.db_host,
99
74
port=conf.db_port, user=conf.db_user, passwd=conf.db_password)
106
# GENERIC DB FUNCTIONS #
109
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
110
"""Checks that a dict does not contain keys that are not fields
111
of the specified table.
112
dict: A mapping from string keys to values; the keys are checked to
113
see that they correspond to login table fields.
114
tablefields: Collection of strings for field names in the table.
115
Only these fields will be allowed.
116
disallowed: Optional collection of strings for field names that are
118
must: If True, the dict MUST contain all fields in tablefields.
119
If False, it may contain any subset of the fields.
120
Returns True if the dict is valid, False otherwise.
122
allowed = frozenset(tablefields) - frozenset(disallowed)
123
dictkeys = frozenset(dict.keys())
125
return allowed == dictkeys
127
return allowed.issuperset(dictkeys)
129
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
131
"""Inserts a new row in a table, using data from a supplied
132
dictionary (which will be checked by check_dict).
133
dict: Dictionary mapping column names to values. The values may be
134
any of the following types:
135
str, int, long, float, NoneType.
136
tablename: String, name of the table to insert into. Will NOT be
137
escaped - must be a valid identifier.
138
tablefields, disallowed: see check_dict.
139
dry: Returns the SQL query as a string, and does not execute it.
140
Raises a DBException if the dictionary contains invalid fields.
142
if not DB.check_dict(dict, tablefields, disallowed):
143
extras = set(dict.keys()) - tablefields
144
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
145
# Build two lists concurrently: field names and values, as SQL strings
148
for k,v in dict.items():
150
values.append(_escape(v))
151
if len(fieldnames) == 0: return
152
fieldnames = ', '.join(fieldnames)
153
values = ', '.join(values)
154
query = ("INSERT INTO %s (%s) VALUES (%s);"
155
% (tablename, fieldnames, values))
76
# USER MANAGEMENT FUNCTIONS #
78
def create_user(self, login, unixid, password, nick, fullname, rolenm,
79
studentid, dry=False):
80
"""Creates a user login entry in the database.
81
Arguments are the same as those in the "login" table of the schema.
82
The exception is "password", which is a cleartext password. makeuser
83
will hash the password.
84
Raises an exception if the user already exists.
86
passhash = _passhash(password)
87
query = ("INSERT INTO login (login, unixid, passhash, nick, fullname,"
88
" rolenm, studentid) VALUES (%s, %d, %s, %s, %s, %s, %s);" %
89
(_escape(login), unixid, _escape(passhash), _escape(nick),
90
_escape(fullname), _escape(rolenm), _escape(studentid)))
156
91
if dry: return query
157
92
self.db.query(query)
159
def update(self, primarydict, updatedict, tablename, tablefields,
160
primary_keys, disallowed_update=frozenset([]), dry=False):
161
"""Updates a row in a table, matching against primarydict to find the
162
row, and using the data in updatedict (which will be checked by
164
primarydict: Dict mapping column names to values. The keys should be
165
the table's primary key. Only rows which match this dict's values
167
updatedict: Dict mapping column names to values. The columns will be
168
updated with the given values for the matched rows.
169
tablename, tablefields, disallowed_update: See insert.
170
primary_keys: Collection of strings which together form the primary
171
key for this table. primarydict must contain all of these as keys,
94
def update_user(self, login, password=None, nick=None,
95
fullname=None, rolenm=None, dry=False):
96
"""Updates fields of a particular user. login is the name of the user
97
to update. The other arguments are optional fields which may be
98
modified. If None or omitted, they do not get modified. login and
99
studentid may not be modified.
101
Note that no checking is done. It is expected this function is called
102
by a trusted source. In particular, it allows the password to be
103
changed without knowing the old password. The caller should check
104
that the user knows the existing password before calling this function
174
if (not (DB.check_dict(primarydict, primary_keys, must=True)
175
and DB.check_dict(updatedict, tablefields, disallowed_update))):
176
raise DBException("Supplied dictionary contains invalid or missing fields (1).")
177
107
# Make a list of SQL fragments of the form "field = 'new value'"
178
108
# These fragments are ALREADY-ESCAPED
180
for k,v in updatedict.items():
181
setlist.append("%s = %s" % (k, _escape(v)))
183
for k,v in primarydict.items():
184
wherelist.append("%s = %s" % (k, _escape(v)))
185
if len(setlist) == 0 or len(wherelist) == 0:
110
if password is not None:
111
setlist.append("passhash = " + _escape(_passhash(password)))
113
setlist.append("nick = " + _escape(nick))
114
if fullname is not None:
115
setlist.append("fullname = " + _escape(fullname))
116
if rolenm is not None:
117
setlist.append("rolenm = " + _escape(rolenm))
118
if len(setlist) == 0:
187
120
# Join the fragments into a comma-separated string
188
121
setstring = ', '.join(setlist)
189
wherestring = ' AND '.join(wherelist)
190
122
# Build the whole query as an UPDATE statement
191
query = ("UPDATE %s SET %s WHERE %s;"
192
% (tablename, setstring, wherestring))
196
def delete(self, primarydict, tablename, primary_keys, dry=False):
197
"""Deletes a row in the table, matching against primarydict to find
199
primarydict, tablename, primary_keys: See update.
201
if not DB.check_dict(primarydict, primary_keys, must=True):
202
raise DBException("Supplied dictionary contains invalid or missing fields (2).")
204
for k,v in primarydict.items():
205
wherelist.append("%s = %s" % (k, _escape(v)))
206
if len(wherelist) == 0:
208
wherestring = ' AND '.join(wherelist)
209
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
213
def get_single(self, primarydict, tablename, getfields, primary_keys,
214
error_notfound="No rows found", dry=False):
215
"""Retrieves a single row from a table, returning it as a dictionary
216
mapping field names to values. Matches against primarydict to find the
218
primarydict, tablename, primary_keys: See update/delete.
219
getfields: Collection of strings; the field names which will be
220
returned as keys in the dictionary.
221
error_notfound: Error message if 0 rows match.
222
Raises a DBException if 0 rows match, with error_notfound as the msg.
223
Raises an AssertError if >1 rows match (this should not happen if
224
primary_keys is indeed the primary key).
226
if not DB.check_dict(primarydict, primary_keys, must=True):
227
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
229
for k,v in primarydict.items():
230
wherelist.append("%s = %s" % (k, _escape(v)))
231
if len(getfields) == 0 or len(wherelist) == 0:
233
# Join the fragments into a comma-separated string
234
getstring = ', '.join(getfields)
235
wherestring = ' AND '.join(wherelist)
236
# Build the whole query as an SELECT statement
237
query = ("SELECT %s FROM %s WHERE %s;"
238
% (getstring, tablename, wherestring))
123
query = ("UPDATE login SET %s WHERE login = %s;"
124
% (setstring, _escape(login)))
128
def delete_user(self, login, dry=False):
129
"""Deletes a user login entry from the database."""
130
query = "DELETE FROM login WHERE login = %s;" % _escape(login)
134
def get_user(self, login, dry=False):
135
"""Given a login, returns a dictionary of the user's DB fields,
136
excluding the passhash field.
138
Raises a DBException if the login is not found in the DB.
140
query = ("SELECT login, unixid, nick, fullname, rolenm, studentid "
141
"FROM login WHERE login = %s;" % _escape(login))
239
142
if dry: return query
240
143
result = self.db.query(query)
241
144
# Expecting exactly one
242
145
if result.ntuples() != 1:
243
146
# It should not be possible for ntuples to be greater than 1
244
147
assert (result.ntuples() < 1)
245
raise DBException(error_notfound)
148
raise DBException("get_user: No user with that login name")
246
149
# Return as a dictionary
247
150
return result.dictresult()[0]
249
def get_all(self, tablename, getfields, dry=False):
250
"""Retrieves all rows from a table, returning it as a list of
251
dictionaries mapping field names to values.
252
tablename, getfields: See get_single.
254
if len(getfields) == 0:
256
getstring = ', '.join(getfields)
257
query = ("SELECT %s FROM %s;" % (getstring, tablename))
259
return self.db.query(query).dictresult()
261
def start_transaction(self, dry=False):
262
"""Starts a DB transaction.
263
Will not commit any changes until self.commit() is called.
265
query = "START TRANSACTION;"
269
def commit(self, dry=False):
270
"""Commits (ends) a DB transaction.
271
Commits all changes since the call to start_transaction.
277
def rollback(self, dry=False):
278
"""Rolls back (ends) a DB transaction, undoing all changes since the
279
call to start_transaction.
285
# USER MANAGEMENT FUNCTIONS #
287
login_primary = frozenset(["login"])
288
login_fields_list = [
289
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
290
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login", "svn_pass"
292
login_fields = frozenset(login_fields_list)
294
def create_user(self, user_obj=None, dry=False, **kwargs):
295
"""Creates a user login entry in the database.
296
Two ways to call this - passing a user object, or passing
297
all fields as separate arguments.
299
Either pass a "user_obj" as the first argument (in which case other
300
fields will be ignored), or pass all fields as arguments.
302
All user fields are to be passed as args. The argument names
303
are the field names of the "login" table of the DB schema.
304
However, instead of supplying a "passhash", you must supply a
305
"password" argument, which will be hashed internally.
306
Also "state" must not given explicitly; it is implicitly set to
308
Raises an exception if the user already exists, or the dict contains
309
invalid keys or is missing required keys.
311
if 'passhash' in kwargs:
312
raise DBException("Supplied arguments include passhash (invalid) (1).")
313
# Make a copy of the dict. Change password to passhash (hashing it),
314
# and set 'state' to "no_agreement".
317
fields = copy.copy(kwargs)
319
# Use the user object
320
fields = dict(user_obj)
321
if 'password' in fields:
322
fields['passhash'] = _passhash(fields['password'])
323
del fields['password']
325
# Convert role to rolenm
326
fields['rolenm'] = str(user_obj.role)
329
fields['state'] = "no_agreement"
330
# else, we'll trust the user, but it SHOULD be "no_agreement"
331
# (We can't change it because then the user object would not
333
if 'local_password' in fields:
334
del fields['local_password']
336
return self.insert(fields, "login", self.login_fields, dry=dry)
338
def update_user(self, login, dry=False, **kwargs):
339
"""Updates fields of a particular user. login is the name of the user
340
to update. The dict contains the fields which will be modified, and
341
their new values. If any value is omitted from the dict, it does not
342
get modified. login and studentid may not be modified.
343
Passhash may be modified by supplying a "password" field, in
344
cleartext, not a hashed password.
346
Note that no checking is done. It is expected this function is called
347
by a trusted source. In particular, it allows the password to be
348
changed without knowing the old password. The caller should check
349
that the user knows the existing password before calling this function
352
if 'passhash' in kwargs:
353
raise DBException("Supplied arguments include passhash (invalid) (2).")
354
if "password" in kwargs:
355
kwargs = copy.copy(kwargs)
356
kwargs['passhash'] = _passhash(kwargs['password'])
357
del kwargs['password']
358
return self.update({"login": login}, kwargs, "login",
359
self.login_fields, self.login_primary, ["login", "studentid"],
362
def get_user(self, login, dry=False):
363
"""Given a login, returns a User object containing details looked up
366
Raises a DBException if the login is not found in the DB.
368
userdict = self.get_single({"login": login}, "login",
369
self.login_fields, self.login_primary,
370
error_notfound="get_user: No user with that login name", dry=dry)
372
return userdict # Query string
373
# Package into a User object
374
return user.User(**userdict)
376
def get_users(self, dry=False):
377
"""Returns a list of all users in the DB, as User objects.
379
userdicts = self.get_all("login", self.login_fields, dry=dry)
381
return userdicts # Query string
382
# Package into User objects
383
return [user.User(**userdict) for userdict in userdicts]
385
152
def user_authenticate(self, login, password, dry=False):
386
153
"""Performs a password authentication on a user. Returns True if
387
"passhash" is the correct passhash for the given login, False
388
if the passhash does not match the password in the DB,
389
and None if the passhash in the DB is NULL.
154
"password" is the correct password for the given login, False
155
otherwise. "password" is cleartext.
390
156
Also returns False if the login does not exist (so if you want to
391
157
differentiate these cases, use get_user and catch an exception).
393
query = "SELECT passhash FROM login WHERE login = '%s';" % login
159
query = ("SELECT login FROM login "
160
"WHERE login = '%s' AND passhash = %s;"
161
% (login, _escape(_passhash(password))))
394
162
if dry: return query
395
163
result = self.db.query(query)
396
if result.ntuples() == 1:
397
# Valid username. Check password.
398
passhash = result.getresult()[0][0]
401
return _passhash(password) == passhash
164
# If one row was returned, succeed.
165
# Otherwise, fail to authenticate.
166
return result.ntuples() == 1
406
169
"""Close the DB connection. Do not call any other functions after
407
170
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
