96
72
Takes no parameters - gets all the DB info from the configuration."""
97
73
self.db = pg.connect(dbname=conf.db_dbname, host=conf.db_host,
98
74
port=conf.db_port, user=conf.db_user, passwd=conf.db_password)
105
# GENERIC DB FUNCTIONS #
108
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
109
"""Checks that a dict does not contain keys that are not fields
110
of the specified table.
111
dict: A mapping from string keys to values; the keys are checked to
112
see that they correspond to login table fields.
113
tablefields: Collection of strings for field names in the table.
114
Only these fields will be allowed.
115
disallowed: Optional collection of strings for field names that are
117
must: If True, the dict MUST contain all fields in tablefields.
118
If False, it may contain any subset of the fields.
119
Returns True if the dict is valid, False otherwise.
121
allowed = frozenset(tablefields) - frozenset(disallowed)
122
dictkeys = frozenset(dict.keys())
124
return allowed == dictkeys
126
return allowed.issuperset(dictkeys)
128
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
76
# USER MANAGEMENT FUNCTIONS #
78
def create_user(self, login, password, nick, fullname, rolenm, studentid,
130
"""Inserts a new row in a table, using data from a supplied
131
dictionary (which will be checked by check_dict).
132
dict: Dictionary mapping column names to values. The values may be
133
any of the following types:
134
str, int, long, float, NoneType.
135
tablename: String, name of the table to insert into. Will NOT be
136
escaped - must be a valid identifier.
137
tablefields, disallowed: see check_dict.
138
dry: Returns the SQL query as a string, and does not execute it.
139
Raises a DBException if the dictionary contains invalid fields.
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.
141
if not DB.check_dict(dict, tablefields, disallowed):
142
raise DBException("Supplied dictionary contains invalid fields.")
143
# Build two lists concurrently: field names and values, as SQL strings
146
for k,v in dict.items():
148
values.append(_escape(v))
149
if len(fieldnames) == 0: return
150
fieldnames = ', '.join(fieldnames)
151
values = ', '.join(values)
152
query = ("INSERT INTO %s (%s) VALUES (%s);"
153
% (tablename, fieldnames, values))
86
passhash = _passhash(password)
87
query = ("INSERT INTO login (login, passhash, nick, fullname, "
88
"rolenm, studentid) VALUES (%s, %s, %s, %s, %s, %s);" %
89
(_escape(login), _escape(passhash), _escape(nick),
90
_escape(fullname), _escape(rolenm), _escape(studentid)))
154
91
if dry: return query
155
92
self.db.query(query)
157
def update(self, primarydict, updatedict, tablename, tablefields,
158
primary_keys, disallowed_update=frozenset([]), dry=False):
159
"""Updates a row in a table, matching against primarydict to find the
160
row, and using the data in updatedict (which will be checked by
162
primarydict: Dict mapping column names to values. The keys should be
163
the table's primary key. Only rows which match this dict's values
165
updatedict: Dict mapping column names to values. The columns will be
166
updated with the given values for the matched rows.
167
tablename, tablefields, disallowed_update: See insert.
168
primary_keys: Collection of strings which together form the primary
169
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
172
if (not (DB.check_dict(primarydict, primary_keys, must=True)
173
and DB.check_dict(updatedict, tablefields, disallowed_update))):
174
raise DBException("Supplied dictionary contains invalid or "
176
107
# Make a list of SQL fragments of the form "field = 'new value'"
177
108
# These fragments are ALREADY-ESCAPED
179
for k,v in updatedict.items():
180
setlist.append("%s = %s" % (k, _escape(v)))
182
for k,v in primarydict.items():
183
wherelist.append("%s = %s" % (k, _escape(v)))
184
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:
186
120
# Join the fragments into a comma-separated string
187
121
setstring = ', '.join(setlist)
188
wherestring = ' AND '.join(wherelist)
189
122
# Build the whole query as an UPDATE statement
190
query = ("UPDATE %s SET %s WHERE %s;"
191
% (tablename, setstring, wherestring))
195
def delete(self, primarydict, tablename, primary_keys, dry=False):
196
"""Deletes a row in the table, matching against primarydict to find
198
primarydict, tablename, primary_keys: See update.
200
if not DB.check_dict(primarydict, primary_keys, must=True):
201
raise DBException("Supplied dictionary contains invalid or "
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 "
230
for k,v in primarydict.items():
231
wherelist.append("%s = %s" % (k, _escape(v)))
232
if len(getfields) == 0 or len(wherelist) == 0:
234
# Join the fragments into a comma-separated string
235
getstring = ', '.join(getfields)
236
wherestring = ' AND '.join(wherelist)
237
# Build the whole query as an SELECT statement
238
query = ("SELECT %s FROM %s WHERE %s;"
239
% (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, nick, fullname, rolenm, studentid FROM login "
141
"WHERE login = %s;" % _escape(login))
240
142
if dry: return query
241
143
result = self.db.query(query)
242
144
# Expecting exactly one
243
145
if result.ntuples() != 1:
244
146
# It should not be possible for ntuples to be greater than 1
245
147
assert (result.ntuples() < 1)
246
raise DBException(error_notfound)
148
raise DBException("get_user: No user with that login name")
247
149
# Return as a dictionary
248
150
return result.dictresult()[0]
250
def get_all(self, tablename, getfields, dry=False):
251
"""Retrieves all rows from a table, returning it as a list of
252
dictionaries mapping field names to values.
253
tablename, getfields: See get_single.
255
if len(getfields) == 0:
257
getstring = ', '.join(getfields)
258
query = ("SELECT %s FROM %s;" % (getstring, tablename))
260
return self.db.query(query).dictresult()
262
# USER MANAGEMENT FUNCTIONS #
264
login_primary = frozenset(["login"])
265
login_fields_list = [
266
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
267
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login"
269
login_fields = frozenset(login_fields_list)
270
# Do not return passhash when reading from the DB
271
login_getfields = login_fields - frozenset(["passhash"])
273
def create_user(self, dry=False, **kwargs):
274
"""Creates a user login entry in the database.
275
All user fields are to be passed as args. The argument names
276
are the field names of the "login" table of the DB schema.
277
However, instead of supplying a "passhash", you must supply a
278
"password" argument, which will be hashed internally.
279
Also "state" must not given explicitly; it is implicitly set to
281
Raises an exception if the user already exists, or the dict contains
282
invalid keys or is missing required keys.
284
if 'passhash' in kwargs:
285
raise DBException("Supplied arguments include passhash (invalid).")
286
# Make a copy of the dict. Change password to passhash (hashing it),
287
# and set 'state' to "no_agreement".
288
kwargs = copy.copy(kwargs)
289
if 'password' in kwargs:
290
kwargs['passhash'] = _passhash(kwargs['password'])
291
del kwargs['password']
292
kwargs['state'] = "no_agreement"
294
return self.insert(kwargs, "login", self.login_fields, dry=dry)
296
def update_user(self, login, dry=False, **kwargs):
297
"""Updates fields of a particular user. login is the name of the user
298
to update. The dict contains the fields which will be modified, and
299
their new values. If any value is omitted from the dict, it does not
300
get modified. login and studentid may not be modified.
301
Passhash may be modified by supplying a "password" field, in
302
cleartext, not a hashed password.
304
Note that no checking is done. It is expected this function is called
305
by a trusted source. In particular, it allows the password to be
306
changed without knowing the old password. The caller should check
307
that the user knows the existing password before calling this function
310
if 'passhash' in kwargs:
311
raise DBException("Supplied arguments include passhash (invalid).")
312
if "password" in kwargs:
313
kwargs = copy.copy(kwargs)
314
kwargs['passhash'] = _passhash(kwargs['password'])
315
del kwargs['password']
316
return self.update({"login": login}, kwargs, "login",
317
self.login_fields, self.login_primary, ["login", "studentid"],
320
def get_user(self, login, dry=False):
321
"""Given a login, returns a dictionary of the user's DB fields,
322
excluding the passhash field.
324
Raises a DBException if the login is not found in the DB.
326
return self.get_single({"login": login}, "login",
327
self.login_getfields, self.login_primary,
328
error_notfound="get_user: No user with that login name", dry=dry)
330
def get_users(self, dry=False):
331
"""Returns a list of all users. The list elements are a dictionary of
332
the user's DB fields, excluding the passhash field.
334
return self.get_all("login", self.login_getfields, dry=dry)
336
152
def user_authenticate(self, login, password, dry=False):
337
153
"""Performs a password authentication on a user. Returns True if
338
"passhash" is the correct passhash for the given login, False
154
"password" is the correct password for the given login, False
155
otherwise. "password" is cleartext.
340
156
Also returns False if the login does not exist (so if you want to
341
157
differentiate these cases, use get_user and catch an exception).
added
removed
lib/common/db.py
