From 482b63e3d83d649a143d3a6678ea876f41e4182c Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Fri, 3 Nov 2017 15:00:57 +0100 Subject: [PATCH] Refactored for a schema solution --- dns/schema.pgsql.sql | 174 ++++++++++++++++++++++++++++--------------- 1 file changed, 115 insertions(+), 59 deletions(-) diff --git a/dns/schema.pgsql.sql b/dns/schema.pgsql.sql index 8c49d9d..4fed700 100644 --- a/dns/schema.pgsql.sql +++ b/dns/schema.pgsql.sql @@ -37,6 +37,13 @@ ALTER DATABASE pdns_public OWNER TO pdns_public; COMMIT; +BEGIN WORK; + +CREATE SCHEMA pdns_local AUTHORIZATION pdns_local; +CREATE SCHEMA pdns_public AUTHORIZATION pdns_public; + +COMMIT; + -- ---------------------------------------------------------------------- BEGIN WORK; @@ -215,42 +222,41 @@ COMMIT; -- ---------------------------------------------------------------------- -\connect pdns_local +-- \connect pdns_local -BEGIN WORK; +-- BEGIN WORK; -CREATE EXTENSION IF NOT EXISTS postgres_fdw; +-- CREATE EXTENSION IF NOT EXISTS postgres_fdw; -CREATE SERVER pdns_master - FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (host 'systemshare.pixelpark.com', dbname 'pdns'); -ALTER SERVER pdns_master OWNER TO pdns_local; -CREATE USER MAPPING FOR pdns_local - SERVER pdns_master - OPTIONS (user 'pdns_local', password 'Neingeichee(ghahn7ah'); +-- CREATE SERVER pdns_master +-- FOREIGN DATA WRAPPER postgres_fdw +-- OPTIONS (host 'systemshare.pixelpark.com', dbname 'pdns'); +-- ALTER SERVER pdns_master OWNER TO pdns_local; +-- CREATE USER MAPPING FOR pdns_local +-- SERVER pdns_master +-- OPTIONS (user 'pdns_local', password 'Neingeichee(ghahn7ah'); -CREATE SCHEMA IF NOT EXISTS pdns; -ALTER SCHEMA pdns OWNER TO pdns_local; +-- CREATE SCHEMA IF NOT EXISTS pdns; +-- ALTER SCHEMA pdns OWNER TO pdns_local; -COMMIT; +-- COMMIT; -BEGIN WORK; +-- BEGIN WORK; -- Execute as User pdns_local !!! -IMPORT FOREIGN SCHEMA public FROM SERVER pdns_master INTO pdns; +-- IMPORT FOREIGN SCHEMA public FROM SERVER pdns_master INTO pdns; -COMMIT; +-- COMMIT; -- ---------------- BEGIN WORK; -CREATE or REPLACE VIEW public.domains AS +CREATE or REPLACE VIEW pdns_local.domains AS SELECT id, name, master, last_check, type, notified_serial, account - FROM pdns.domains - ORDER BY name; + FROM public.domains; -ALTER TABLE IF EXISTS public.domains OWNER TO pdns_local; +ALTER TABLE IF EXISTS pdns_local.domains OWNER TO pdns_local; COMMIT; @@ -258,12 +264,12 @@ COMMIT; BEGIN WORK; -CREATE or REPLACE VIEW public.records AS +CREATE or REPLACE VIEW pdns_local.records AS SELECT id, domain_id, name, type, content, ttl, prio, change_date, disabled, ordername, auth - FROM pdns.records - WHERE name NOT in ( SELECT name FROM pdns.comments WHERE comment ~* '.*public.*'); + FROM public.records + WHERE type = 'SOA' OR name NOT in ( SELECT name FROM public.comments WHERE comment ~* '.*public.*'); -ALTER TABLE IF EXISTS public.records OWNER TO pdns_local; +ALTER TABLE IF EXISTS pdns_local.records OWNER TO pdns_local; COMMIT; @@ -271,12 +277,11 @@ COMMIT; BEGIN WORK; -CREATE or REPLACE VIEW public.supermasters AS +CREATE or REPLACE VIEW pdns_local.supermasters AS SELECT ip, nameserver, account - FROM pdns.supermasters - ORDER BY nameserver; + FROM public.supermasters; -ALTER TABLE IF EXISTS public.supermasters OWNER TO pdns_local; +ALTER TABLE IF EXISTS pdns_local.supermasters OWNER TO pdns_local; COMMIT; @@ -284,12 +289,12 @@ COMMIT; BEGIN WORK; -CREATE or REPLACE VIEW public.comments AS +CREATE or REPLACE VIEW pdns_local.comments AS SELECT id, domain_id, name, type, modified_at, account, comment - FROM pdns.comments + FROM public.comments WHERE comment IS NULL OR comment !~* '.*public.*'; -ALTER TABLE IF EXISTS public.comments OWNER TO pdns_local; +ALTER TABLE IF EXISTS pdns_local.comments OWNER TO pdns_local; COMMIT; @@ -297,11 +302,11 @@ COMMIT; BEGIN WORK; -CREATE or REPLACE VIEW public.domainmetadata AS +CREATE or REPLACE VIEW pdns_local.domainmetadata AS SELECT id, domain_id, kind, content - FROM pdns.domainmetadata; + FROM public.domainmetadata; -ALTER TABLE IF EXISTS public.domainmetadata OWNER TO pdns_local; +ALTER TABLE IF EXISTS pdns_local.domainmetadata OWNER TO pdns_local; COMMIT; @@ -309,11 +314,11 @@ COMMIT; BEGIN WORK; -CREATE or REPLACE VIEW public.cryptokeys AS +CREATE or REPLACE VIEW pdns_local.cryptokeys AS SELECT id, domain_id, flags, active, content - FROM pdns.cryptokeys; + FROM public.cryptokeys; -ALTER TABLE IF EXISTS public.cryptokeys OWNER TO pdns_local; +ALTER TABLE IF EXISTS pdns_local.cryptokeys OWNER TO pdns_local; COMMIT; @@ -321,39 +326,90 @@ COMMIT; BEGIN WORK; -CREATE or REPLACE VIEW public.tsigkeys AS +CREATE or REPLACE VIEW pdns_local.tsigkeys AS SELECT id, name, algorithm, secret - FROM pdns.tsigkeys; + FROM public.tsigkeys; -ALTER TABLE IF EXISTS public.tsigkeys OWNER TO pdns_local; +ALTER TABLE IF EXISTS pdns_local.tsigkeys OWNER TO pdns_local; COMMIT; -- ---------------------------------------------------------------------- -\connect pdns_public +BEGIN WORK; + +CREATE or REPLACE VIEW pdns_public.domains AS +SELECT id, name, master, last_check, type, notified_serial, account + FROM public.domains + WHERE account IS NULL OR account !~* '.*(lo[ck]al|privat|intern).*'; + +ALTER TABLE IF EXISTS pdns_public.domains OWNER TO pdns_public; + +COMMIT; + +-- ---------------- + +BEGIN WORK; + +CREATE or REPLACE VIEW pdns_public.records AS +SELECT id, domain_id, name, type, content, ttl, prio, change_date, disabled, ordername, auth + FROM public.records + WHERE domain_id IN (SELECT id FROM pdns_public.domains ) + AND ( type = 'SOA' OR name NOT in ( SELECT name FROM public.comments WHERE comment ~* '.*(lo[ck]al|privat|intern).*')); + +ALTER TABLE IF EXISTS pdns_public.records OWNER TO pdns_public; + +COMMIT; + +-- ---------------- + +BEGIN WORK; + +CREATE or REPLACE VIEW pdns_public.supermasters AS +SELECT ip, nameserver, account + FROM public.supermasters; + +ALTER TABLE IF EXISTS pdns_public.supermasters OWNER TO pdns_public; + +COMMIT; + +-- ---------------- BEGIN WORK; -CREATE EXTENSION IF NOT EXISTS postgres_fdw; +CREATE or REPLACE VIEW pdns_public.comments AS +SELECT id, domain_id, name, type, modified_at, account, comment + FROM public.comments + WHERE (domain_id IS NULL OR domain_id IN (SELECT id FROM pdns_public.domains)) + AND (comment IS NULL OR comment !~* '.*(lo[ck]al|privat|intern).*'); + +ALTER TABLE IF EXISTS pdns_public.comments OWNER TO pdns_public; + +COMMIT; + +-- ---------------- -CREATE SERVER pdns_master - FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (host 'systemshare.pixelpark.com', dbname 'pdns'); -ALTER SERVER pdns_master OWNER TO pdns_public; -CREATE USER MAPPING FOR pdns_public - SERVER pdns_master - OPTIONS (user 'pdns_public', password 'ahr7die3gee]s4vahCoh'); +BEGIN WORK; -CREATE SCHEMA IF NOT EXISTS pdns; -ALTER SCHEMA pdns OWNER TO pdns_public; +CREATE or REPLACE VIEW pdns_public.domainmetadata AS +SELECT id, domain_id, kind, content + FROM public.domainmetadata + WHERE domain_id IS NULL OR domain_id IN (SELECT id FROM pdns_public.domains); + +ALTER TABLE IF EXISTS pdns_public.domainmetadata OWNER TO pdns_public; COMMIT; +-- ---------------- + BEGIN WORK; --- Execute as User pdns_public !!! -IMPORT FOREIGN SCHEMA public FROM SERVER pdns_master INTO pdns; +CREATE or REPLACE VIEW pdns_public.cryptokeys AS +SELECT id, domain_id, flags, active, content + FROM public.cryptokeys + WHERE domain_id IS NULL OR domain_id IN (SELECT id FROM pdns_public.domains); + +ALTER TABLE IF EXISTS pdns_public.cryptokeys OWNER TO pdns_public; COMMIT; @@ -361,15 +417,15 @@ COMMIT; BEGIN WORK; -CREATE or REPLACE VIEW public.domains AS -SELECT id, name, master, last_check, type, notified_serial, account - FROM pdns.domains - WHERE account IS NULL OR account !~* '.*(lo[ck]al|privat|intern).*' - ORDER BY name; +CREATE or REPLACE VIEW pdns_public.tsigkeys AS +SELECT id, name, algorithm, secret + FROM public.tsigkeys; -ALTER TABLE IF EXISTS public.domains OWNER TO pdns_local; +ALTER TABLE IF EXISTS pdns_public.tsigkeys OWNER TO pdns_public; COMMIT; +-- ---------------- + -- vim: list -- 2.39.5