"""User database setup program entry point.
Python process to set up databases for users and save passwords in .pgpass
files.
"""
import argparse
import errno
import itertools
import os
import pwd
import random
import string
import traceback
import psycopg2
import psycopg2.extensions
from uw.sql.dbapi import *
from uw.sql.wrap import open_psycopg2_db_service_cursor
from uw.local.dbtools import Cursor
[docs]class AdminCursor (Cursor):
get_fullname = make_execute_required_value ("select format ('%%s, %%s', surname, givennames) from watiam_entry where userid=%(userid)s")
[docs]class UserDBCursor (Cursor):
[docs] def create_db (self, userid):
sql = self.callproc_required_value ("userdb_write_create_db", userid)
self.execute_none (sql)
create_user = make_callproc_none ("userdb_create_user")
[docs]def generate_password ():
"""Generate a random password for a new user database account.
The password is a randomly-chosen string of 10 lowercase letters.
"""
return ''.join (random.choice (string.ascii_lowercase) for i in range (10))
[docs]def setup_user (cursor, userid, fullname, password):
"""Setup the given userid using the given password.
:param cursor: DB connection cursor.
:param str userid: the userid to set up.
:param str fullname: the fullname to use as a comment on the role,
database, and schema.
:param str password: the password to use for the newly-created role.
Creates the corresponding database and other setup steps. The database
creation step requires that set_isolation_level be used to set isolation
level 0 on the cursor's connection, in order that the command can run
outside of a transaction block.
Returns the request ID assigned by the database.
"""
if not isinstance (userid, str):
raise ValueError('userid must be a string')
try:
# Create database
cursor.create_db (userid)
except psycopg2.ProgrammingError as x:
# Don't worry if database already exists
if x.pgcode != '42P04': # SQL state "duplicate_database"
raise
# Finish role and schema setup
cursor.create_user (userid, fullname, password)
[docs]def create_user (admin_cursor, userdb_cursor, userid):
"""Set up the given userid with a role, database, and schema.
:param admin_cursor: DB connection cursor for obtaining the full name.
:param userdb_cursor: DB connection cursor for setting up role, database,
and schema.
:param str userid: the userid to set up.
Obtain the user's fullname from the WatIAM database, generate a random
password, create the appropriate role, database, and schema, and return the
password for storing in a .pgpass file. If the role already exists,
return None instead.
"""
# Get user's full name for database and schema comments
fullname = admin_cursor.get_fullname (userid=userid)
# Generate random password
random.seed (os.urandom (16))
password = generate_password ()
setup_user (userdb_cursor, userid, fullname, password)
notices = userdb_cursor.connection.notices
role_already_exists = False
for notice in notices:
if notice == 'NOTICE: Role %s already exists\n' % userid:
role_already_exists = True
elif notice == 'NOTICE: Schema %s already exists\n' % userid:
pass
else:
print('Unexpected notice: %r' % notice)
del notices[:]
if role_already_exists:
return None
else:
return password
[docs]def pgpass_file_names ():
yield '.pgpass'
for n in itertools.count (1):
yield 'new.pgpass.%d' % n
[docs]def write_pgpass (hostname, port, userid, password):
"""Write out password information to a .pgpass file.
:param str hostname: the server hostname.
:param int port: the server port.
:param str userid: the database userid; also used to determine where to
save the .pgpass file.
:param str password: the password.
Creates a new .pgpass file in ~userid containing a single line. Uses
:func:`pgpass_file_names` to choose alternate filenames if necessary.
"""
pw = pwd.getpwnam (userid)
homedir = pwd.getpwnam (userid).pw_dir
if not os.path.isdir (homedir):
raise OSError ('Home %s is not directory' % homedir)
for pgpassfile in pgpass_file_names ():
pgpasspath = os.path.join (homedir, pgpassfile)
try:
fd = os.open (pgpasspath, os.O_WRONLY | os.O_CREAT | os.O_EXCL, 0o600)
except OSError as x:
if x.errno != errno.EEXIST:
raise
continue
os.fchown (fd, pw.pw_uid, pw.pw_gid)
f = os.fdopen (fd, 'w')
f.write (':'.join ([hostname, str (port), '*', userid, password]))
f.close ()
break
server_hostnames = {
'_userdb_creator_teaching': 'postgres.student.cs',
'_userdb_creator_general': 'postgres.cs',
}
[docs]def main ():
"""Main program for setting up calling user's database, role, and schema.
"""
parser = argparse.ArgumentParser ()
group = parser.add_mutually_exclusive_group (required=True)
group.add_argument ('--user', nargs='+', default=argparse.SUPPRESS, metavar='userid')
group.add_argument ('--offering-admin-id', default=argparse.SUPPRESS, type=int, metavar='admin_id')
args = parser.parse_args ()
# Connect to main postgres.odyssey database
admin_cursor = open_psycopg2_db_service_cursor (service="_userdb_creator_admin", cursor_class=AdminCursor)
# Connect to specific postgres.student.cs or postgres.cs (or ...)
userdb_cursor = open_psycopg2_db_service_cursor (cursor_class=UserDBCursor)
userdb_cursor.connection.set_isolation_level (psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
if 'user' in args:
userids = args.user
elif 'offering_admin_id' in args:
userids = admin_cursor.execute_values ("with p as (select person_id from teaching_admin_registration where (term_id, admin_id) = (uw_term_current (), %(admin_id)s) and dropped is null union select person_id from auth_offering_personnel_complete where (term_id, admin_id) = (uw_term_current (), %(admin_id)s) and role_current and role_code = 'INST' union select person_id from ta_position_assignment where (term_id, admin_id) = (uw_term_current (), %(admin_id)s)) select userid from p natural join person_identity_complete order by userid", admin_id=args.offering_admin_id)
else:
userids = []
for userid in userids:
try:
password = create_user (admin_cursor, userdb_cursor, userid)
if password is None:
print('Role already exists for %s' % userid)
else:
# Ugly hack because psycopg2 does not provide access to PQhost()
# and PQport()
hostname = server_hostnames[os.getenv ('PGSERVICE')]
port = 5432
write_pgpass (hostname, port, userid, password)
except Exception as x:
print('---- Error handling userid %s' % userid)
traceback.print_exc ()
print('----')