path: root/ca/rpki-sql-setup

#!/usr/bin/env python

# $Id$
#
# Copyright (C) 2014  Dragon Research Labs ("DRL")
# Portions copyright (C) 2009-2013  Internet Systems Consortium ("ISC")
#
# Permission to use, copy, modify, and distribute this software for any
# purpose with or without fee is hereby granted, provided that the above
# copyright notices and this permission notice appear in all copies.
#
# THE SOFTWARE IS PROVIDED "AS IS" AND DRL AND ISC DISCLAIM ALL
# WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED
# WARRANTIES OF MERCHANTABILITY AND FITNESS.  IN NO EVENT SHALL DRL OR
# ISC BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL
# DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA
# OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER
# TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
# PERFORMANCE OF THIS SOFTWARE.

import os
import sys
import glob
import getpass
import argparse
import datetime
import rpki.config
import rpki.version
import rpki.autoconf

# This program implements its own schema versioning system as a poor
# substitute for schema migrations.  Now that we're moving to Django
# ORM, this is pretty much useless, and should be removed at some point.


from rpki.mysql_import import MySQLdb, _mysql_exceptions

ER_NO_SUCH_TABLE = 1146                 # See mysqld_ername.h


class RootDB(object):
    """
    Class to wrap MySQL actions that require root-equivalent access so
    we can defer such actions until we're sure they're really needed.
    Overall goal here is to prompt the user for the root password once
    at most, and not at all when not necessary.
    """

    def __init__(self, mysql_defaults = None):
        self.initialized = False
        self.mysql_defaults = mysql_defaults

    def __getattr__(self, name):
        if self.initialized:
            raise AttributeError
        if self.mysql_defaults is None:
            self.db = MySQLdb.connect(db     = "mysql",
                                      user   = "root",
                                      passwd = getpass.getpass("Please enter your MySQL root password: "))
        else:
            mysql_cfg = rpki.config.parser(set_filename = self.mysql_defaults, section = "client")
            self.db = MySQLdb.connect(db     = "mysql",
                                      user   = mysql_cfg.get("user"),
                                      passwd = mysql_cfg.get("password"))
        self.cur = self.db.cursor()
        self.cur.execute("SHOW DATABASES")
        self.databases = set(d[0] for d in self.cur.fetchall())
        self.initialized = True
        return getattr(self, name)

    def close(self):
        if self.initialized:
            self.db.close()


class UserDB(object):
    """
    Class to wrap MySQL access parameters for a particular database.

    NB: The SQL definitions for the upgrade_version table is embedded in
    this class rather than being declared in any of the .sql files.
    This is deliberate: nothing but the upgrade system should ever touch
    this table, and it's simpler to keep everything in one place.

    We have to be careful about SQL commits here, because CREATE TABLE
    implies an automatic commit.  So presence of the magic table per se
    isn't significant, only its content (or lack thereof).
    """

    upgrade_version_table_schema = """
        CREATE TABLE upgrade_version (
            version   TEXT NOT NULL,
            updated   DATETIME NOT NULL
        ) ENGINE=InnoDB
        """

    def __init__(self, name):
        self.name = name
        self.database = cfg.get("sql-database", section = name)
        self.username = cfg.get("sql-username", section = name)
        self.password = cfg.get("sql-password", section = name)
        self.db = None
        self.cur = None

    def open(self):
        self.db = MySQLdb.connect(db = self.database, user = self.username, passwd = self.password)
        self.db.autocommit(False)
        self.cur = self.db.cursor()

    def close(self):
        if self.cur is not None:
            self.cur.close()
            self.cur = None
        if self.db is not None:
            self.db.commit()
            self.db.close()
            self.db = None

    @property
    def exists_and_accessible(self):
        try:
            MySQLdb.connect(db = self.database, user = self.username, passwd = self.password).close()
        except:                             # pylint: disable=W0702
            return False
        else:
            return True

    @property
    def version(self):
        try:
            self.cur.execute("SELECT version FROM upgrade_version")
            v = self.cur.fetchone()
            return Version(None if v is None else v[0])
        except _mysql_exceptions.ProgrammingError, e:
            if e.args[0] != ER_NO_SUCH_TABLE:
                raise
            log("Creating upgrade_version table in %s" % self.name)
            self.cur.execute(self.upgrade_version_table_schema)
            return Version(None)

    @version.setter
    def version(self, v):
        if v > self.version:
            self.cur.execute("DELETE FROM upgrade_version")
            self.cur.execute("INSERT upgrade_version (version, updated) VALUES (%s, %s)", (v, datetime.datetime.now()))
            self.db.commit()
            log("Updated %s to %s" % (self.name, v))



class Version(object):
    """
    A version number.  This is a class in its own right to force the
    comparision and string I/O behavior we want.
    """

    def __init__(self, v):
        if v is None:
            v = "0.0"
        self.v = tuple(v.lower().split("."))

    def __str__(self):
        return ".".join(self.v)

    def __cmp__(self, other):
        return cmp(self.v, other.v)


class Upgrade(object):
    """
    One upgrade script.  Really, just its filename and the Version
    object we parse from its filename, we don't need to read the script
    itself except when applying it, but we do need to sort all the
    available upgrade scripts into version order.
    """

    @classmethod
    def load_all(cls, name, dn):
        g = os.path.join(dn, "upgrade-%s-to-*.py" % name)
        for fn in glob.iglob(g):
            yield cls(g, fn)

    def __init__(self, g, fn):
        head, sep, tail = g.partition("*")  # pylint: disable=W0612
        self.fn = fn
        self.version = Version(fn[len(head):-len(tail)])

    def __cmp__(self, other):
        return cmp(self.version, other.version)

    def apply(self, db):
        # db is an argument here primarily so the script we exec can get at it
        log("Applying %s to %s" % (self.fn, db.name))
        with open(self.fn, "r") as f:
            exec f                            # pylint: disable=W0122


def do_drop(name):
    db = UserDB(name)
    if db.database in root.databases:
        log("DROP DATABASE %s" %  db.database)
        root.cur.execute("DROP DATABASE %s" %  db.database)
        root.db.commit()

def do_create(name):
    db = UserDB(name)
    log("CREATE DATABASE %s" % db.database)
    root.cur.execute("CREATE DATABASE %s" % db.database)
    log("GRANT ALL ON %s.* TO %s@localhost IDENTIFIED BY ###" % (db.database, db.username))
    root.cur.execute("GRANT ALL ON %s.* TO %s@localhost IDENTIFIED BY %%s" % (db.database, db.username),
                     (db.password,))
    root.db.commit()
    db.open()
    db.version = current_version
    db.close()

def do_script_drop(name):
    db = UserDB(name)
    print "DROP DATABASE IF EXISTS %s;" % db.database

def do_drop_and_create(name):
    do_drop(name)
    do_create(name)

def do_fix_grants(name):
    db = UserDB(name)
    if not db.exists_and_accessible:
        log("GRANT ALL ON %s.* TO %s@localhost IDENTIFIED BY ###" % (db.database, db.username))
        root.cur.execute("GRANT ALL ON %s.* TO %s@localhost IDENTIFIED BY %%s" % (db.database, db.username),
                         (db.password,))
        root.db.commit()

def do_create_if_missing(name):
    db = UserDB(name)
    if not db.exists_and_accessible:
        do_create(name)

def do_apply_upgrades(name):
    upgrades = sorted(Upgrade.load_all(name, args.upgrade_scripts))
    if upgrades:
        db = UserDB(name)
        db.open()
        log("Current version of %s is %s" % (db.name, db.version))
        for upgrade in upgrades:
            if upgrade.version > db.version:
                upgrade.apply(db)
                db.version = upgrade.version
        db.version = current_version
        db.close()

def log(text):
    if args.verbose:
        print "#", text

parser = argparse.ArgumentParser(description = """\
Automated setup of all SQL stuff used by the RPKI CA tools.  Pulls
configuration from rpki.conf, prompts for MySQL password when needed.
""")
group  = parser.add_mutually_exclusive_group()
parser.add_argument("-c", "--config",
                    help = "specify alternate location for rpki.conf")
parser.add_argument("-v", "--verbose",  action = "store_true",
                    help = "whistle while you work")
parser.add_argument("--mysql-defaults",
                    help = "specify MySQL root access credentials via a configuration file")
parser.add_argument("--upgrade-scripts",
                    default = os.path.join(rpki.autoconf.datarootdir, "rpki", "upgrade-scripts"),
                    help = "override default location of upgrade scripts")
group.add_argument("--create",
                   action = "store_const", dest = "dispatch", const = do_create,
                   help = "create databases and load schemas")
group.add_argument("--drop",
                   action = "store_const", dest = "dispatch", const = do_drop,
                   help = "drop databases")
group.add_argument("--script-drop",
                   action = "store_const", dest = "dispatch", const = do_script_drop,
                   help = "send SQL commands to drop databases to standard output")
group.add_argument("--drop-and-create",
                   action = "store_const", dest = "dispatch", const = do_drop_and_create,
                   help = "drop databases then recreate them and load schemas")
group.add_argument("--fix-grants",
                   action = "store_const", dest = "dispatch", const = do_fix_grants,
                   help = "whack database access to match current configuration file")
group.add_argument("--create-if-missing",
                   action = "store_const", dest = "dispatch", const = do_create_if_missing,
                   help = "create databases and load schemas if they don't exist already")
group.add_argument("--apply-upgrades",
                   action = "store_const", dest = "dispatch", const = do_apply_upgrades,
                   help = "apply upgrade scripts to existing databases")
parser.set_defaults(dispatch = do_create_if_missing)
args = parser.parse_args()

try:
    cfg  = rpki.config.parser(set_filename = args.config, section = "myrpki")
    root = RootDB(args.mysql_defaults)
    current_version = Version(rpki.version.VERSION)
    for program_name in ("irdbd", "rpkid", "pubd"):
        if cfg.getboolean("start_" + program_name, False):
            args.dispatch(program_name)
    root.close()
except Exception, e:
    #raise
    sys.exit(str(e))