From 20c41c0ea3ebc0e7a4278e5edef3a1c73680ee97 Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Thu, 27 Jul 2017 11:14:17 +0200 Subject: [PATCH] Better handling in Postgresql on diiferent DB users --- db/create-pdns-db-postgresql.sql | 171 +++++++++++++++++++++++ db/create-poweradmin-db-postgresql.sql | 181 +++++++++++++++++++++++++ pp_lib/import_pdnsdata.py | 24 +++- 3 files changed, 371 insertions(+), 5 deletions(-) create mode 100644 db/create-pdns-db-postgresql.sql create mode 100644 db/create-poweradmin-db-postgresql.sql diff --git a/db/create-pdns-db-postgresql.sql b/db/create-pdns-db-postgresql.sql new file mode 100644 index 0000000..9aa626a --- /dev/null +++ b/db/create-pdns-db-postgresql.sql @@ -0,0 +1,171 @@ + +-- ------------------------------------------------------------- +--- Create Database +CREATE DATABASE poweradmin WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8'; + +-- ------------------------------------------------------------- +-- Create users +BEGIN WORK; + +CREATE ROLE dns + NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; +COMMENT ON ROLE dns IS 'All DNS related users'; + +CREATE USER pdns WITH PASSWORD 'oo?fah7gai7X'; +GRANT dns TO pdns; +GRANT ALL ON DATABASE "poweradmin" TO pdns; +ALTER DATABASE poweradmin OWNER TO pdns; + +CREATE USER pdnsadm WITH PASSWORD 'Eig}o3ojoh@w'; +GRANT dns TO pdnsadm; +GRANT ALL ON DATABASE "poweradmin" TO pdnsadm; + +COMMIT; + +-- ------------------------------------------------------------- +-- Default Privileges + +\c poweradmin + +BEGIN WORK; + +ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON TABLES TO dns; +ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON SEQUENCES TO dns; +ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON FUNCTIONS TO dns; +ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON TYPES TO dns; + +COMMIT; + +-- ------------------------------------------------------------- +-- Creating all necessary tables, sequences and relations +-- ------------------------------------------------------------- + +BEGIN WORK; + +-- ------------------------------------------------------------- +-- Create table domains +CREATE TABLE domains ( + id SERIAL PRIMARY KEY, + name VARCHAR(255) NOT NULL, + master VARCHAR(128) DEFAULT NULL, + last_check INT DEFAULT NULL, + type VARCHAR(6) NOT NULL, + notified_serial INT DEFAULT NULL, + account VARCHAR(40) DEFAULT NULL, + CONSTRAINT c_lowercase_name CHECK (((name)::TEXT = LOWER((name)::TEXT))) +); + +CREATE UNIQUE INDEX name_index ON domains(name); + +ALTER TABLE IF EXISTS domains OWNER TO pdns; +ALTER SEQUENCE IF EXISTS domains_id_seq OWNER TO pdns; + +-- ------------------------------------------------------------- +-- Create table records +CREATE TABLE records ( + id SERIAL PRIMARY KEY, + domain_id INT DEFAULT NULL, + name VARCHAR(255) DEFAULT NULL, + type VARCHAR(10) DEFAULT NULL, + content VARCHAR(65535) DEFAULT NULL, + ttl INT DEFAULT NULL, + prio INT DEFAULT NULL, + change_date INT DEFAULT NULL, + disabled BOOL DEFAULT 'f', + ordername VARCHAR(255), + auth BOOL DEFAULT 't', + CONSTRAINT domain_exists + FOREIGN KEY(domain_id) REFERENCES domains(id) + ON DELETE CASCADE, + CONSTRAINT c_lowercase_name CHECK (((name)::TEXT = LOWER((name)::TEXT))) +); + +CREATE INDEX rec_name_index ON records(name); +CREATE INDEX nametype_index ON records(name,type); +CREATE INDEX domain_id ON records(domain_id); +CREATE INDEX recordorder ON records (domain_id, ordername text_pattern_ops); + +ALTER TABLE IF EXISTS records OWNER TO pdns; +ALTER SEQUENCE IF EXISTS records_id_seq OWNER TO pdns; + +-- ------------------------------------------------------------- +-- Create table supermasters +CREATE TABLE supermasters ( + ip INET NOT NULL, + nameserver VARCHAR(255) NOT NULL, + account VARCHAR(40) NOT NULL, + PRIMARY KEY(ip, nameserver) +); + +ALTER TABLE IF EXISTS supermasters OWNER TO pdns; + +-- ------------------------------------------------------------- +-- Create table comments +CREATE TABLE comments ( + id SERIAL PRIMARY KEY, + domain_id INT NOT NULL, + name VARCHAR(255) NOT NULL, + type VARCHAR(10) NOT NULL, + modified_at INT NOT NULL, + account VARCHAR(40) DEFAULT NULL, + comment VARCHAR(65535) NOT NULL, + CONSTRAINT domain_exists + FOREIGN KEY(domain_id) REFERENCES domains(id) + ON DELETE CASCADE, + CONSTRAINT c_lowercase_name CHECK (((name)::TEXT = LOWER((name)::TEXT))) +); + +CREATE INDEX comments_domain_id_idx ON comments (domain_id); +CREATE INDEX comments_name_type_idx ON comments (name, type); +CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); + +ALTER TABLE IF EXISTS comments OWNER TO pdns; +ALTER SEQUENCE IF EXISTS comments_id_seq OWNER TO pdns; + +-- ------------------------------------------------------------- +-- Create table domainmetadata +CREATE TABLE domainmetadata ( + id SERIAL PRIMARY KEY, + domain_id INT REFERENCES domains(id) ON DELETE CASCADE, + kind VARCHAR(32), + content TEXT +); + +CREATE INDEX domainidmetaindex ON domainmetadata(domain_id); + +ALTER TABLE IF EXISTS domainmetadata OWNER TO pdns; +ALTER SEQUENCE IF EXISTS domainmetadata_id_seq OWNER TO pdns; + +-- ------------------------------------------------------------- +-- Create table cryptokeys +CREATE TABLE cryptokeys ( + id SERIAL PRIMARY KEY, + domain_id INT REFERENCES domains(id) ON DELETE CASCADE, + flags INT NOT NULL, + active BOOL, + content TEXT +); + +CREATE INDEX domainidindex ON cryptokeys(domain_id); + +ALTER TABLE IF EXISTS cryptokeys OWNER TO pdns; +ALTER SEQUENCE IF EXISTS cryptokeys_id_seq OWNER TO pdns; + +-- ------------------------------------------------------------- +-- Create table tsigkeys +CREATE TABLE tsigkeys ( + id SERIAL PRIMARY KEY, + name VARCHAR(255), + algorithm VARCHAR(50), + secret VARCHAR(255), + CONSTRAINT c_lowercase_name CHECK (((name)::TEXT = LOWER((name)::TEXT))) +); + +CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm); + +ALTER TABLE IF EXISTS tsigkeys OWNER TO pdns; +ALTER SEQUENCE IF EXISTS tsigkeys_id_seq OWNER TO pdns; + +COMMIT; + + diff --git a/db/create-poweradmin-db-postgresql.sql b/db/create-poweradmin-db-postgresql.sql new file mode 100644 index 0000000..a43c0a2 --- /dev/null +++ b/db/create-poweradmin-db-postgresql.sql @@ -0,0 +1,181 @@ + +-- ------------------------------------------------------------- +--- Connect to Database +\c poweradmin + +BEGIN WORK; + +-- ------------------------------------------------------------- +-- Create table perm_items + +CREATE TABLE perm_items ( + id SERIAL PRIMARY KEY, + name varchar(64) NOT NULL, + descr text NOT NULL +); + +CREATE UNIQUE INDEX perm_item_name_index ON perm_items(name); + +ALTER TABLE IF EXISTS perm_items OWNER TO pdnsadm; + +COMMENT ON TABLE perm_items is 'Permission items.'; + +INSERT INTO perm_items (name, descr) VALUES + ('user_is_ueberuser', 'User has full access. God-like. Redeemer.'), + ('zone_master_add', 'User is allowed to add new master zones.'), + ('zone_slave_add', 'User is allowed to add new slave zones.'), + ('zone_content_view_own', 'User is allowed to see the content and meta data of zones he owns.'), + ('zone_content_edit_own', 'User is allowed to edit the content of zones he owns.'), + ('zone_meta_edit_own', 'User is allowed to edit the meta data of zones he owns.'), + ('zone_content_view_others', 'User is allowed to see the content and meta data of zones he does not own.'), + ('zone_content_edit_others', 'User is allowed to edit the content of zones he does not own.'), + ('zone_meta_edit_others', 'User is allowed to edit the meta data of zones he does not own.'), + ('search', 'User is allowed to perform searches.'), + ('supermaster_view', 'User is allowed to view supermasters.'), + ('supermaster_add', 'User is allowed to add new supermasters.'), + ('supermaster_edit', 'User is allowed to edit supermasters.'), + ('user_view_others', 'User is allowed to see other users and their details.'), + ('user_add_new', 'User is allowed to add new users.'), + ('user_edit_own', 'User is allowed to edit their own details.'), + ('user_edit_others', 'User is allowed to edit other users.'), + ('user_passwd_edit_others', 'User is allowed to edit the password of other users.'), + ('user_edit_templ_perm', 'User is allowed to change the permission template that is assigned to a user.'), + ('templ_perm_add', 'User is allowed to add new permission templates.'), + ('templ_perm_edit', 'User is allowed to edit existing permission templates.'); + +-- ------------------------------------------------------------- +-- Create table perm_templ + +CREATE TABLE perm_templ ( + id SERIAL PRIMARY KEY, + name varchar(128) NOT NULL, + descr text NOT NULL +); + +CREATE UNIQUE INDEX perm_templ_name_index ON perm_templ(name); + +COMMENT ON TABLE perm_templ IS 'Permission templates.'; + +ALTER TABLE IF EXISTS perm_templ OWNER TO pdnsadm; + +INSERT INTO perm_templ (name, descr) VALUES + ('Administrator', 'Administrator template with full rights.'); + +-- ------------------------------------------------------------- +-- Create table perm_templ_items + +CREATE TABLE perm_templ_items ( + id SERIAL PRIMARY KEY, + templ_id integer NOT NULL, + perm_id integer NOT NULL, + FOREIGN KEY(templ_id) REFERENCES perm_templ(id), + FOREIGN KEY(perm_id) REFERENCES perm_items(id) +); + +CREATE UNIQUE INDEX perm_templ_item_tp_id ON perm_templ_items(templ_id, perm_id); +CREATE INDEX perm_templ_item_perm_id ON perm_templ_items(perm_id); + +COMMENT ON TABLE perm_templ_items IS 'Permission template items.'; + +ALTER TABLE IF EXISTS perm_templ_items OWNER TO pdnsadm; + +INSERT INTO perm_templ_items (templ_id, perm_id) VALUES (1, 1); + +-- ------------------------------------------------------------- +-- Create table users + +CREATE TABLE users ( + id SERIAL PRIMARY KEY, + username varchar(64) NOT NULL, + password varchar(128) NOT NULL, + fullname varchar(255) NOT NULL, + email varchar(255) NOT NULL, + description text NOT NULL, + perm_templ integer default 0, + active smallint default 0, + use_ldap smallint default 0, + FOREIGN KEY(perm_templ) REFERENCES perm_templ(id) +); + +CREATE UNIQUE INDEX users_name_index ON users(username); + +COMMENT ON TABLE users IS 'Users of the poweradmin application.'; + +ALTER TABLE IF EXISTS users OWNER TO pdnsadm; + +-- admin/admin +INSERT INTO users + (username, password, fullname, email, description, perm_templ, active, use_ldap) VALUES + ('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 + +CREATE TABLE zone_templ ( + id SERIAL PRIMARY KEY, + name varchar(128) NOT NULL, + descr text NOT NULL, + owner integer default 0 +); + +COMMENT ON TABLE zone_templ IS 'Zone templates.'; + +ALTER TABLE IF EXISTS zone_templ OWNER TO pdnsadm; + +-- ------------------------------------------------------------- +-- Create table zone_templ_records + +CREATE TABLE zone_templ_records ( + id SERIAL PRIMARY KEY, + zone_templ_id integer NOT NULL, + name varchar(255) NOT NULL, + type varchar(6) NOT NULL, + content varchar(255) NOT NULL, + ttl integer default NULL, + prio integer default NULL +); + +COMMENT ON TABLE zone_templ_records IS 'Records of a zone template.'; + +ALTER TABLE IF EXISTS zone_templ_records OWNER TO pdnsadm; + +-- ------------------------------------------------------------- +-- Create table records_zone_templ + +CREATE TABLE records_zone_templ ( + domain_id integer NOT NULL, + record_id integer NOT NULL, + zone_templ_id integer NOT NULL +); + +ALTER TABLE IF EXISTS records_zone_templ OWNER TO pdnsadm; + +-- ------------------------------------------------------------- +-- Create table migrations + +CREATE TABLE migrations ( + version varchar(255) NOT NULL, + apply_time integer NOT NULL +); + +ALTER TABLE IF EXISTS migrations OWNER TO pdnsadm; + +COMMIT; diff --git a/pp_lib/import_pdnsdata.py b/pp_lib/import_pdnsdata.py index 9aab1b1..6bc10d1 100644 --- a/pp_lib/import_pdnsdata.py +++ b/pp_lib/import_pdnsdata.py @@ -30,7 +30,7 @@ from .common import pp from .cfg_app import PpCfgAppError, PpConfigApplication -__version__ = '0.6.3' +__version__ = '0.6.4' LOG = logging.getLogger(__name__) # ============================================================================= @@ -483,6 +483,11 @@ class ImportPdnsdataApp(PpConfigApplication): 'supermasters', 'tsigkeys', 'domains', ] + sequences = [ + 'comments_id_seq', 'cryptokeys_id_seq', 'domainmetadata_id_seq', + 'domains_id_seq', 'records_id_seq', 'tsigkeys_id_seq', + ] + LOG.info("Truncating all tables in target database ...") with self.tgt_connection.cursor() as tgt_cursor: @@ -490,15 +495,24 @@ class ImportPdnsdataApp(PpConfigApplication): for table in tables: LOG.debug("Truncating {!r} ...".format(table)) - if self.tgt_db_type == 'mysql': - sql = 'DELETE FROM {}'.format(table) - else: - sql = 'TRUNCATE TABLE {} RESTART IDENTITY CASCADE'.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_type != 'mysql': + + for sequence in sequences: + + LOG.debug("Resetting sequence {!r} ...".format(sequence)) + sql = "SELECT SETVAL('{}', 1)".format(sequence) + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + if not self.simulate: + tgt_cursor.execute(sql) + + LOG.debug("Commiting changes ...") self.tgt_connection.commit() -- 2.39.5