From b2bb35e508d90efcf5469ace1a2b37c257cd0c2a Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Thu, 27 Jul 2017 14:07:16 +0200 Subject: [PATCH] Start adding support for poweradmin on importing PDNS data --- db/create-poweradmin-db-postgresql.sql | 45 ++++--- etc/import-pdnsdata.ini.default | 2 + pp_lib/import_pdnsdata.py | 170 ++++++++++++++++++++++++- 3 files changed, 195 insertions(+), 22 deletions(-) diff --git a/db/create-poweradmin-db-postgresql.sql b/db/create-poweradmin-db-postgresql.sql index a43c0a2..37bef53 100644 --- a/db/create-poweradmin-db-postgresql.sql +++ b/db/create-poweradmin-db-postgresql.sql @@ -109,23 +109,6 @@ INSERT INTO users ('admin', '21232f297a57a5a743894a0e4a801fc3', 'Administrator', 'frank.brehm@pixelpark.com', 'Administrator with full rights.', 1, 1, 0); --- ------------------------------------------------------------- --- Create table zones - -CREATE TABLE zones ( - id SERIAL PRIMARY KEY, - domain_id integer default 0, - owner integer default 0, - comment text, - zone_templ_id integer NOT NULL -); - -CREATE INDEX zone_domain_owner ON zones(domain_id, owner); - -COMMENT ON TABLE zones IS 'Zones of a domain.'; - -ALTER TABLE IF EXISTS zones OWNER TO pdnsadm; - -- ------------------------------------------------------------- -- Create table zone_templ @@ -135,6 +118,7 @@ CREATE TABLE zone_templ ( descr text NOT NULL, owner integer default 0 ); +CREATE UNIQUE INDEX zone_templ_name_index ON zone_templ(name); COMMENT ON TABLE zone_templ IS 'Zone templates.'; @@ -150,7 +134,8 @@ CREATE TABLE zone_templ_records ( type varchar(6) NOT NULL, content varchar(255) NOT NULL, ttl integer default NULL, - prio integer default NULL + prio integer default NULL, + FOREIGN KEY(zone_templ_id) REFERENCES zone_templ(id) ); COMMENT ON TABLE zone_templ_records IS 'Records of a zone template.'; @@ -163,11 +148,33 @@ ALTER TABLE IF EXISTS zone_templ_records OWNER TO pdnsadm; CREATE TABLE records_zone_templ ( domain_id integer NOT NULL, record_id integer NOT NULL, - zone_templ_id integer NOT NULL + zone_templ_id integer NOT NULL, + FOREIGN KEY(domain_id) REFERENCES domains(id), + FOREIGN KEY(zone_templ_id) REFERENCES zone_templ(id) ); + ALTER TABLE IF EXISTS records_zone_templ OWNER TO pdnsadm; +-- ------------------------------------------------------------- +-- Create table zones + +CREATE TABLE zones ( + id SERIAL PRIMARY KEY, + domain_id integer default 0, + owner integer default 0, + comment text, + zone_templ_id integer NOT NULL, + FOREIGN KEY(domain_id) REFERENCES domains(id), + FOREIGN KEY(zone_templ_id) REFERENCES zone_templ(id) +); + +CREATE INDEX zone_domain_owner ON zones(domain_id, owner); + +COMMENT ON TABLE zones IS 'Zones of a domain.'; + +ALTER TABLE IF EXISTS zones OWNER TO pdnsadm; + -- ------------------------------------------------------------- -- Create table migrations diff --git a/etc/import-pdnsdata.ini.default b/etc/import-pdnsdata.ini.default index 3605fca..a331809 100644 --- a/etc/import-pdnsdata.ini.default +++ b/etc/import-pdnsdata.ini.default @@ -37,6 +37,8 @@ # Password of the PostgreSQL user (no defult) #password = 'changeme' +# Is the target database intended for 'poweradmin'? +#is_poweradmin = False # vim: filetype=dosini diff --git a/pp_lib/import_pdnsdata.py b/pp_lib/import_pdnsdata.py index 6bc10d1..b0e0840 100644 --- a/pp_lib/import_pdnsdata.py +++ b/pp_lib/import_pdnsdata.py @@ -26,11 +26,11 @@ import psycopg2 import pymysql # Own modules -from .common import pp +from .common import pp, to_bool from .cfg_app import PpCfgAppError, PpConfigApplication -__version__ = '0.6.4' +__version__ = '0.7.1' LOG = logging.getLogger(__name__) # ============================================================================= @@ -82,6 +82,8 @@ class ImportPdnsdataApp(PpConfigApplication): self.tgt_db_user = self.default_tgt_db_user self.tgt_db_pass = None + self.tgt_db_is_poweradmin = False + self.src_connection = None self.tgt_connection = None @@ -279,6 +281,9 @@ class ImportPdnsdataApp(PpConfigApplication): if 'password' in section: self.tgt_db_pass = section['password'] + if 'is_poweradmin' in section: + self.tgt_db_is_poweradmin = to_bool(section['is_poweradmin']) + # ------------------------------------------------------------------------- def pre_run(self): @@ -402,6 +407,9 @@ class ImportPdnsdataApp(PpConfigApplication): self.import_domainmetadata() self.import_records() self.import_tsigkeys() + if self.tgt_db_is_poweradmin: + self.import_perm_templ() + self.import_users() finally: self._close_all() @@ -482,11 +490,21 @@ class ImportPdnsdataApp(PpConfigApplication): 'comments', 'cryptokeys', 'domainmetadata', 'records', 'supermasters', 'tsigkeys', 'domains', ] + if self.tgt_db_is_poweradmin: + tables.insert(0, 'perm_templ_items') + tables.insert(0, 'zone_templ') + tables.insert(0, 'zone_templ_records') + tables.insert(0, 'zones') sequences = [ 'comments_id_seq', 'cryptokeys_id_seq', 'domainmetadata_id_seq', 'domains_id_seq', 'records_id_seq', 'tsigkeys_id_seq', ] + if self.tgt_db_is_poweradmin: + sequences.append('perm_templ_items_id_seq') + sequences.append('zone_templ_id_seq') + sequences.append('zone_templ_records_id_seq') + sequences.append('zones_id_seq') LOG.info("Truncating all tables in target database ...") @@ -494,13 +512,28 @@ class ImportPdnsdataApp(PpConfigApplication): for table in tables: - LOG.debug("Truncating {!r} ...".format(table)) + LOG.debug("Truncating table {!r} ...".format(table)) sql = 'DELETE FROM {}'.format(table) if self.verbose > 1: LOG.debug("SQL: {}".format(sql)) if not self.simulate: tgt_cursor.execute(sql) + if self.tgt_db_is_poweradmin: + LOG.debug("Truncating table 'users' ...") + sql = 'DELETE FROM users WHERE id > 1' + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + if not self.simulate: + tgt_cursor.execute(sql) + + LOG.debug("Truncating table 'perm_templ' ...") + sql = "DELETE FROM perm_templ WHERE id != 1" + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + if not self.simulate: + tgt_cursor.execute(sql) + if self.tgt_db_type != 'mysql': for sequence in sequences: @@ -891,6 +924,137 @@ class ImportPdnsdataApp(PpConfigApplication): LOG.debug("Commiting changes ...") self.tgt_connection.commit() + # ------------------------------------------------------------------------- + def import_perm_templ(self): + + LOG.info("Importing all permission templates except 'Administrator' ...") + + src_sql = textwrap.dedent('''\ + SELECT id, name, descr + FROM perm_templ + WHERE id > 1 + ''').strip() + if self.verbose > 1: + LOG.debug("Source SQL:\n{}".format(src_sql)) + + tgt_sql = textwrap.dedent('''\ + INSERT INTO perm_templ ( + id, name, descr) + VALUES ( + %(id)s, %(name)s, %(descr)s) + ''').strip() + if self.verbose > 1: + LOG.debug("Target SQL:\n{}".format(tgt_sql)) + + with self.tgt_connection.cursor() as tgt_cursor: + with self.src_connection.cursor() as src_cursor: + + i = 0 + src_cursor.execute(src_sql) + results = src_cursor.fetchall() + + if self.verbose > 3: + LOG.debug("Got users:\n{}".format(pp(results))) + + if not results: + LOG.info("No permission templates in source database.") + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + return + + for result in results: + i += 1 + if not self.simulate: + tgt_cursor.execute(tgt_sql, result) + LOG.info("Imported {} permission templates.".format(i)) + + if self.tgt_db_type != 'mysql': + LOG.debug("Get max. Permission Template Id ...") + sql = "SELECT MAX(id) AS max_id FROM perm_templ" + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + tgt_cursor.execute(sql) + result = tgt_cursor.fetchone() + if self.verbose > 2: + LOG.debug("Got max permission template Id:\n{}".format(pp(result))) + max_id = int(result[0]) + sql = "SELECT SETVAL('perm_templ_id_seq', %s)" + LOG.debug("Setting curval of perm_templ_id_seq to {} ...".format(max_id)) + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + if not self.simulate: + tgt_cursor.execute(sql, (max_id, )) + + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + + # ------------------------------------------------------------------------- + def import_users(self): + + LOG.info("Importing all users except 'admin' ...") + + src_sql = textwrap.dedent('''\ + SELECT id, username, password, fullname, email, description, perm_templ, active + FROM users + WHERE id > 1 + ''').strip() + if self.verbose > 1: + LOG.debug("Source SQL:\n{}".format(src_sql)) + + tgt_sql = textwrap.dedent('''\ + INSERT INTO users ( + id, username, password, fullname, email, + description, perm_templ, active, use_ldap) + VALUES ( + %(id)s, %(username)s, %(password)s, %(fullname)s, %(email)s, + %(description)s, %(perm_templ)s, %(active)s, 0) + ''').strip() + if self.verbose > 1: + LOG.debug("Target SQL:\n{}".format(tgt_sql)) + + + with self.tgt_connection.cursor() as tgt_cursor: + with self.src_connection.cursor() as src_cursor: + + i = 0 + src_cursor.execute(src_sql) + results = src_cursor.fetchall() + + if self.verbose > 3: + LOG.debug("Got users:\n{}".format(pp(results))) + + if not results: + LOG.info("No users in source database.") + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + return + + for result in results: + i += 1 + if not self.simulate: + tgt_cursor.execute(tgt_sql, result) + LOG.info("Imported {} users.".format(i)) + + if self.tgt_db_type != 'mysql': + LOG.debug("Get max. User Id ...") + sql = "SELECT MAX(id) AS max_id FROM users" + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + tgt_cursor.execute(sql) + result = tgt_cursor.fetchone() + if self.verbose > 2: + LOG.debug("Got max user Id:\n{}".format(pp(result))) + max_id = int(result[0]) + sql = "SELECT SETVAL('users_id_seq', %s)" + LOG.debug("Setting curval of users_id_seq to {} ...".format(max_id)) + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + if not self.simulate: + tgt_cursor.execute(sql, (max_id, )) + + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + # ------------------------------------------------------------------------- def _close_all(self): -- 2.39.5