diff options
| -rw-r--r-- | myrpki/README | 27 | ||||
| -rw-r--r-- | myrpki/setup-sql.py | 94 |
2 files changed, 117 insertions, 4 deletions
diff --git a/myrpki/README b/myrpki/README index a1bba25b..801f2446 100644 --- a/myrpki/README +++ b/myrpki/README @@ -250,9 +250,27 @@ The [current] steps are: 5) Next, you need to set up the MySQL databases that rpkid et al will use. The MySQL database, username, and password values all need to - match the ones you specified in myrpki.conf. This is one of the - tasks a setup script should handle, but until that is written, - you'll have to do this by hand in the mysql command line tool: + match the ones you specified in myrpki.conf. There are two + different ways you can do this: + + a) You can use the setup-sql.py script, which promts you for your + MySQL root password then attempts to do everything else + automatically using valuses from myrpki.conf; or + + b) You can do it manually. + + The first approach is simple: + + $ python setup-sql.py + Please enter your MySQL root password: + + The script should tell you what databases it creates. You can use + the -v option if you want to see more details about what it's doing. + + If you'd prefer to do the SQL setup manually, perhaps because you + have valuable data in other MySQL databases and you don't want to + trust some random setup script with your MySQL root password, + you'll need to use the MySQL command line tool, as follows: $ mysql -u root -p @@ -271,7 +289,8 @@ The [current] steps are: "rpki_database", "rpki_user", and "rpki_password" are the appropriate values from your configuration file. - If you are running pubd, you will also have to do: + If you are running pubd and doing manual SQL setup, you'll also + have to do: $ mysql -u root -p mysql> CREATE DATABASE pubd_database; diff --git a/myrpki/setup-sql.py b/myrpki/setup-sql.py new file mode 100644 index 00000000..855fd1b6 --- /dev/null +++ b/myrpki/setup-sql.py @@ -0,0 +1,94 @@ +""" +Automated setup of all the pesky SQL stuff we need. Prompts for MySQL +root password, pulls other information from myrpki.conf. + +$Id$ + +Copyright (C) 2009 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 notice and this permission notice appear in all copies. + +THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH +REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY +AND FITNESS. IN NO EVENT SHALL 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, getopt, sys, time, rpki.config, getpass +import MySQLdb, warnings, _mysql_exceptions + +warnings.simplefilter("error", _mysql_exceptions.Warning) + +schema_dir = os.path.normpath(os.path.join(sys.path[0], "../rpkid")) + +def read_schema(filename): + """ + Convert an SQL file into a list of SQL statements. + """ + lines = [] + f = open(filename) + for line in f: + words = line.strip(" \t\n").expandtabs().split(" ") + line = " ".join(word for word in words if word) + if line and not line.startswith("--"): + lines.append(line) + f.close() + return [statement.strip() for statement in " ".join(lines).rstrip(";").split(";")] + +def sql_setup(name): + """ + Create a new SQL database and construct all its tables. + """ + database = cfg.get("sql-database", section = name) + username = cfg.get("sql-username", section = name) + password = cfg.get("sql-password", section = name) + schema = read_schema(os.path.join(schema_dir, "%s.sql" % name)) + + print "Creating database", database + cur = rootdb.cursor() + cur.execute("DROP DATABASE IF EXISTS %s" % database) + cur.execute("CREATE DATABASE %s" % database) + cur.execute("GRANT ALL ON %s.* TO %s@localhost IDENTIFIED BY %%s" % (database, username), (password,)) + rootdb.commit() + + db = MySQLdb.connect(db = database, user = username, passwd = password) + cur = db.cursor() + for statement in schema: + if statement.upper().startswith("DROP TABLE"): + continue + if verbose: + print "+", statement + cur.execute(statement) + db.commit() + db.close() + +cfg_file = "myrpki.conf" + +verbose = False + +opts, argv = getopt.getopt(sys.argv[1:], "c:hv?", ["config=", "help", "verbose"]) +for o, a in opts: + if o in ("-h", "--help", "-?"): + print __doc__ + sys.exit(0) + if o in ("-v", "--verbose"): + verbose = True + if o in ("-c", "--config"): + cfg_file = a + +cfg = rpki.config.parser(cfg_file, "myirbe") + +rootdb = MySQLdb.connect(db = "mysql", user = "root", passwd = getpass.getpass("Please enter your MySQL root password: ")) + +sql_setup("irdbd") +sql_setup("rpkid") + +if cfg.getboolean("want_pubd", False): + sql_setup("pubd") + +rootdb.close() |