From e46e2bd42bbb5457a1b70f9c82515d02e0c8eed0 Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Thu, 2 Dec 2021 13:32:59 +0100 Subject: [PATCH] Adding Database stuff for PowerDNS with PostgreSQL --- db/pdns/pdns-pgsql-create-4.1.sql | 931 +++++++++++++++++ db/pdns/pdns-pgsql-create-4.5.sql | 933 ++++++++++++++++++ db/pdns/pdns-pgsql-upgrade.4.1.0_to_4.3.0.sql | 227 +++++ 3 files changed, 2091 insertions(+) create mode 100644 db/pdns/pdns-pgsql-create-4.1.sql create mode 100644 db/pdns/pdns-pgsql-create-4.5.sql create mode 100644 db/pdns/pdns-pgsql-upgrade.4.1.0_to_4.3.0.sql diff --git a/db/pdns/pdns-pgsql-create-4.1.sql b/db/pdns/pdns-pgsql-create-4.1.sql new file mode 100644 index 0000000..02bc952 --- /dev/null +++ b/db/pdns/pdns-pgsql-create-4.1.sql @@ -0,0 +1,931 @@ + +\set AUTOCOMMIT on + +-- +-- PostgreSQL database cluster dump +-- + +SET default_transaction_read_only = off; + +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; + + + +-- +-- Drop roles +-- + +DROP ROLE IF EXISTS dns; +DROP ROLE IF EXISTS pdns; +DROP ROLE IF EXISTS pdns_local; +DROP ROLE IF EXISTS pdns_public; +DROP ROLE IF EXISTS pdns_ro; +DROP ROLE IF EXISTS postgres; + + +-- +-- Roles +-- + +CREATE ROLE dns; +ALTER ROLE dns WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity'; +COMMENT ON ROLE dns IS 'All DNS related users'; +CREATE ROLE pdns; +ALTER ROLE pdns WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md53f122a311bd86c1da3ef18fa8fde7a38'; +COMMENT ON ROLE pdns IS 'User for PowerDNS'; +CREATE ROLE pdns_local; +ALTER ROLE pdns_local WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5f61076c088c54686393ff13bb1e56e9b'; +COMMENT ON ROLE pdns_local IS 'User for local PowerDNS'; +CREATE ROLE pdns_public; +ALTER ROLE pdns_public WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md51150a78048c7549c99d474b6f015ae18'; +COMMENT ON ROLE pdns_public IS 'User for public PowerDNS'; +CREATE ROLE pdns_ro; +ALTER ROLE pdns_ro WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md587d52200eb2545657dc639f303b0e82f' VALID UNTIL 'infinity'; +COMMENT ON ROLE pdns_ro IS 'Readonly access to pdns database'; +CREATE ROLE postgres; +ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:nGupATc/o6rmH+sMZyYGVA==$2cIfsqw8vm6FaCAFAKXwSvQ+idSHoZP07OXKJg15WWI=:7pTY0VIJlE5QVBT9RJdKX1LZ0Qh7Sx8BGRcJ/GmK6OY='; + + +-- +-- Role memberships +-- + +GRANT dns TO pdns GRANTED BY postgres; +GRANT dns TO pdns_local GRANTED BY postgres; +GRANT dns TO pdns_public GRANTED BY postgres; +GRANT dns TO pdns_ro GRANTED BY postgres; + + + + +-- +-- PostgreSQL database cluster dump complete +-- + +-- +-- PostgreSQL database dump +-- + +-- Dumped from database version 14.1 +-- Dumped by pg_dump version 14.1 + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET idle_in_transaction_session_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SELECT pg_catalog.set_config('search_path', '', false); +SET check_function_bodies = false; +SET xmloption = content; +SET client_min_messages = warning; +SET row_security = off; + +DROP DATABASE IF EXISTS pdns; +-- +-- Name: pdns; Type: DATABASE; Schema: -; Owner: postgres +-- + +CREATE DATABASE pdns WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_US.UTF-8'; + + +ALTER DATABASE pdns OWNER TO postgres; + +\connect pdns + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET idle_in_transaction_session_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SELECT pg_catalog.set_config('search_path', '', false); +SET check_function_bodies = false; +SET xmloption = content; +SET client_min_messages = warning; +SET row_security = off; + +-- +-- Name: DATABASE pdns; Type: COMMENT; Schema: -; Owner: postgres +-- + +COMMENT ON DATABASE pdns IS 'PowerDNS authoritative server itself'; + + +-- +-- Name: pdns_local; Type: SCHEMA; Schema: -; Owner: pdns_local +-- + +CREATE SCHEMA pdns_local; + + +ALTER SCHEMA pdns_local OWNER TO pdns_local; + +-- +-- Name: pdns_public; Type: SCHEMA; Schema: -; Owner: pdns_public +-- + +CREATE SCHEMA pdns_public; + + +ALTER SCHEMA pdns_public OWNER TO pdns_public; + +SET default_tablespace = ''; + +SET default_table_access_method = heap; + +-- +-- Name: comments; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.comments ( + id integer NOT NULL, + domain_id integer NOT NULL, + name character varying(255) NOT NULL, + type character varying(10) NOT NULL, + modified_at integer NOT NULL, + account character varying(40) DEFAULT NULL::character varying, + comment character varying(65535) NOT NULL, + CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text))) +); + + +ALTER TABLE public.comments OWNER TO pdns; + +-- +-- Name: comments; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.comments AS + SELECT comments.id, + comments.domain_id, + comments.name, + comments.type, + comments.modified_at, + comments.account, + comments.comment + FROM public.comments + WHERE ((comments.comment IS NULL) OR ((comments.comment)::text !~* '.*public.*'::text)); + + +ALTER TABLE pdns_local.comments OWNER TO pdns_local; + +-- +-- Name: cryptokeys; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.cryptokeys ( + id integer NOT NULL, + domain_id integer, + flags integer NOT NULL, + active boolean, + content text +); + + +ALTER TABLE public.cryptokeys OWNER TO pdns; + +-- +-- Name: cryptokeys; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.cryptokeys AS + SELECT cryptokeys.id, + cryptokeys.domain_id, + cryptokeys.flags, + cryptokeys.active, + cryptokeys.content + FROM public.cryptokeys; + + +ALTER TABLE pdns_local.cryptokeys OWNER TO pdns_local; + +-- +-- Name: domainmetadata; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.domainmetadata ( + id integer NOT NULL, + domain_id integer, + kind character varying(32), + content text +); + + +ALTER TABLE public.domainmetadata OWNER TO pdns; + +-- +-- Name: domainmetadata; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.domainmetadata AS + SELECT domainmetadata.id, + domainmetadata.domain_id, + domainmetadata.kind, + domainmetadata.content + FROM public.domainmetadata; + + +ALTER TABLE pdns_local.domainmetadata OWNER TO pdns_local; + +-- +-- Name: domains; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.domains ( + id integer NOT NULL, + name character varying(255) NOT NULL, + master character varying(128) DEFAULT NULL::character varying, + last_check integer, + type character varying(6) NOT NULL, + notified_serial integer, + account character varying(40) DEFAULT NULL::character varying, + CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text))) +); + + +ALTER TABLE public.domains OWNER TO pdns; + +-- +-- Name: domains; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.domains AS + SELECT domains.id, + domains.name, + domains.master, + domains.last_check, + domains.type, + domains.notified_serial, + domains.account + FROM public.domains; + + +ALTER TABLE pdns_local.domains OWNER TO pdns_local; + +-- +-- Name: records; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.records ( + id integer NOT NULL, + domain_id integer, + name character varying(255) DEFAULT NULL::character varying, + type character varying(10) DEFAULT NULL::character varying, + content character varying(65535) DEFAULT NULL::character varying, + ttl integer, + prio integer, + change_date integer, + disabled boolean DEFAULT false, + ordername character varying(255), + auth boolean DEFAULT true, + CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text))) +); + + +ALTER TABLE public.records OWNER TO pdns; + +-- +-- Name: records; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.records AS + SELECT records.id, + records.domain_id, + records.name, + records.type, + records.content, + records.ttl, + records.prio, + records.change_date, + records.disabled, + records.ordername, + records.auth + FROM public.records + WHERE (((records.type)::text = 'SOA'::text) OR (NOT ((records.name)::text IN ( SELECT comments.name + FROM public.comments + WHERE ((comments.comment)::text ~* '.*public.*'::text))))); + + +ALTER TABLE pdns_local.records OWNER TO pdns_local; + +-- +-- Name: supermasters; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.supermasters ( + ip inet NOT NULL, + nameserver character varying(255) NOT NULL, + account character varying(40) NOT NULL +); + + +ALTER TABLE public.supermasters OWNER TO pdns; + +-- +-- Name: supermasters; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.supermasters AS + SELECT supermasters.ip, + supermasters.nameserver, + supermasters.account + FROM public.supermasters; + + +ALTER TABLE pdns_local.supermasters OWNER TO pdns_local; + +-- +-- Name: tsigkeys; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.tsigkeys ( + id integer NOT NULL, + name character varying(255), + algorithm character varying(50), + secret character varying(255), + CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text))) +); + + +ALTER TABLE public.tsigkeys OWNER TO pdns; + +-- +-- Name: tsigkeys; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.tsigkeys AS + SELECT tsigkeys.id, + tsigkeys.name, + tsigkeys.algorithm, + tsigkeys.secret + FROM public.tsigkeys; + + +ALTER TABLE pdns_local.tsigkeys OWNER TO pdns_local; + +-- +-- Name: domains; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.domains AS + SELECT domains.id, + domains.name, + domains.master, + domains.last_check, + domains.type, + domains.notified_serial, + domains.account + FROM public.domains + WHERE ((domains.account IS NULL) OR ((domains.account)::text !~* '.*(lo[ck]al|privat|intern).*'::text)); + + +ALTER TABLE pdns_public.domains OWNER TO pdns_public; + +-- +-- Name: comments; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.comments AS + SELECT comments.id, + comments.domain_id, + comments.name, + comments.type, + comments.modified_at, + comments.account, + comments.comment + FROM public.comments + WHERE (((comments.domain_id IS NULL) OR (comments.domain_id IN ( SELECT domains.id + FROM pdns_public.domains))) AND ((comments.comment IS NULL) OR ((comments.comment)::text !~* '.*(lo[ck]al|privat|intern).*'::text))); + + +ALTER TABLE pdns_public.comments OWNER TO pdns_public; + +-- +-- Name: cryptokeys; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.cryptokeys AS + SELECT cryptokeys.id, + cryptokeys.domain_id, + cryptokeys.flags, + cryptokeys.active, + cryptokeys.content + FROM public.cryptokeys + WHERE ((cryptokeys.domain_id IS NULL) OR (cryptokeys.domain_id IN ( SELECT domains.id + FROM pdns_public.domains))); + + +ALTER TABLE pdns_public.cryptokeys OWNER TO pdns_public; + +-- +-- Name: domainmetadata; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.domainmetadata AS + SELECT domainmetadata.id, + domainmetadata.domain_id, + domainmetadata.kind, + domainmetadata.content + FROM public.domainmetadata + WHERE ((domainmetadata.domain_id IS NULL) OR (domainmetadata.domain_id IN ( SELECT domains.id + FROM pdns_public.domains))); + + +ALTER TABLE pdns_public.domainmetadata OWNER TO pdns_public; + +-- +-- Name: records; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.records AS + SELECT records.id, + records.domain_id, + records.name, + records.type, + records.content, + records.ttl, + records.prio, + records.change_date, + records.disabled, + records.ordername, + records.auth + FROM public.records + WHERE ((records.domain_id IN ( SELECT domains.id + FROM pdns_public.domains)) AND (((records.type)::text = 'SOA'::text) OR (NOT ((records.name)::text IN ( SELECT comments.name + FROM public.comments + WHERE ((comments.comment)::text ~* '.*(lo[ck]al|privat|intern).*'::text)))))); + + +ALTER TABLE pdns_public.records OWNER TO pdns_public; + +-- +-- Name: supermasters; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.supermasters AS + SELECT supermasters.ip, + supermasters.nameserver, + supermasters.account + FROM public.supermasters; + + +ALTER TABLE pdns_public.supermasters OWNER TO pdns_public; + +-- +-- Name: tsigkeys; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.tsigkeys AS + SELECT tsigkeys.id, + tsigkeys.name, + tsigkeys.algorithm, + tsigkeys.secret + FROM public.tsigkeys; + + +ALTER TABLE pdns_public.tsigkeys OWNER TO pdns_public; + +-- +-- Name: comments_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.comments_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.comments_id_seq OWNER TO pdns; + +-- +-- Name: comments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.comments_id_seq OWNED BY public.comments.id; + + +-- +-- Name: cryptokeys_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.cryptokeys_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.cryptokeys_id_seq OWNER TO pdns; + +-- +-- Name: cryptokeys_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.cryptokeys_id_seq OWNED BY public.cryptokeys.id; + + +-- +-- Name: domainmetadata_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.domainmetadata_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.domainmetadata_id_seq OWNER TO pdns; + +-- +-- Name: domainmetadata_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.domainmetadata_id_seq OWNED BY public.domainmetadata.id; + + +-- +-- Name: domains_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.domains_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.domains_id_seq OWNER TO pdns; + +-- +-- Name: domains_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.domains_id_seq OWNED BY public.domains.id; + + +-- +-- Name: records_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.records_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.records_id_seq OWNER TO pdns; + +-- +-- Name: records_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.records_id_seq OWNED BY public.records.id; + + +-- +-- Name: tsigkeys_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.tsigkeys_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.tsigkeys_id_seq OWNER TO pdns; + +-- +-- Name: tsigkeys_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.tsigkeys_id_seq OWNED BY public.tsigkeys.id; + + +-- +-- Name: v_domainmetadata; Type: VIEW; Schema: public; Owner: pdns +-- + +CREATE VIEW public.v_domainmetadata AS + SELECT md.id AS metadata_id, + md.domain_id, + d.name AS domain_name, + md.kind AS metadata_kind, + md.content AS metadata_content, + d.master AS domain_master, + d.last_check AS domain_last_check, + d.type AS domain_type, + d.notified_serial AS domain_serial, + d.account AS domain_account + FROM (public.domainmetadata md + LEFT JOIN public.domains d ON ((md.domain_id = d.id))) + ORDER BY d.name, md.kind; + + +ALTER TABLE public.v_domainmetadata OWNER TO pdns; + +-- +-- Name: v_records; Type: VIEW; Schema: public; Owner: pdns +-- + +CREATE VIEW public.v_records AS + SELECT r.id AS record_id, + r.domain_id, + r.name AS record_name, + r.type AS record_type, + r.content AS record_content, + r.ttl AS record_ttl, + r.prio AS record_prio, + r.change_date AS record_change_date, + r.disabled AS record_disabled, + r.ordername AS record_ordername, + r.auth AS record_auth, + d.name AS domain_name, + d.master AS domain_master, + d.last_check AS domain_last_check, + d.type AS domain_type, + d.notified_serial AS domain_notified_serial, + d.account AS domain_account, + c.id AS comment_id, + c.name AS comment_name, + c.type AS comment_type, + c.modified_at AS comment_modified_at, + c.account AS comment_account, + c.comment + FROM ((public.records r + LEFT JOIN public.domains d ON ((d.id = r.domain_id))) + LEFT JOIN public.comments c ON ((((r.name)::text = (c.name)::text) AND ((r.type)::text = (c.type)::text)))) + ORDER BY d.name, r.name; + + +ALTER TABLE public.v_records OWNER TO pdns; + +-- +-- Name: comments id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.comments ALTER COLUMN id SET DEFAULT nextval('public.comments_id_seq'::regclass); + + +-- +-- Name: cryptokeys id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.cryptokeys ALTER COLUMN id SET DEFAULT nextval('public.cryptokeys_id_seq'::regclass); + + +-- +-- Name: domainmetadata id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domainmetadata ALTER COLUMN id SET DEFAULT nextval('public.domainmetadata_id_seq'::regclass); + + +-- +-- Name: domains id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domains ALTER COLUMN id SET DEFAULT nextval('public.domains_id_seq'::regclass); + + +-- +-- Name: records id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.records ALTER COLUMN id SET DEFAULT nextval('public.records_id_seq'::regclass); + + +-- +-- Name: tsigkeys id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.tsigkeys ALTER COLUMN id SET DEFAULT nextval('public.tsigkeys_id_seq'::regclass); + + +-- +-- Name: comments comments_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.comments + ADD CONSTRAINT comments_pkey PRIMARY KEY (id); + + +-- +-- Name: cryptokeys cryptokeys_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.cryptokeys + ADD CONSTRAINT cryptokeys_pkey PRIMARY KEY (id); + + +-- +-- Name: domainmetadata domainmetadata_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domainmetadata + ADD CONSTRAINT domainmetadata_pkey PRIMARY KEY (id); + + +-- +-- Name: domains domains_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domains + ADD CONSTRAINT domains_pkey PRIMARY KEY (id); + + +-- +-- Name: records records_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.records + ADD CONSTRAINT records_pkey PRIMARY KEY (id); + + +-- +-- Name: supermasters supermasters_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.supermasters + ADD CONSTRAINT supermasters_pkey PRIMARY KEY (ip, nameserver); + + +-- +-- Name: tsigkeys tsigkeys_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.tsigkeys + ADD CONSTRAINT tsigkeys_pkey PRIMARY KEY (id); + + +-- +-- Name: comments_domain_id_idx; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX comments_domain_id_idx ON public.comments USING btree (domain_id); + + +-- +-- Name: comments_name_type_idx; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX comments_name_type_idx ON public.comments USING btree (name, type); + + +-- +-- Name: comments_order_idx; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX comments_order_idx ON public.comments USING btree (domain_id, modified_at); + + +-- +-- Name: domain_id; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX domain_id ON public.records USING btree (domain_id); + + +-- +-- Name: domainidindex; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX domainidindex ON public.cryptokeys USING btree (domain_id); + + +-- +-- Name: domainidmetaindex; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX domainidmetaindex ON public.domainmetadata USING btree (domain_id); + + +-- +-- Name: name_index; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE UNIQUE INDEX name_index ON public.domains USING btree (name); + + +-- +-- Name: namealgoindex; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE UNIQUE INDEX namealgoindex ON public.tsigkeys USING btree (name, algorithm); + + +-- +-- Name: nametype_index; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX nametype_index ON public.records USING btree (name, type); + + +-- +-- Name: rec_name_index; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX rec_name_index ON public.records USING btree (name); + + +-- +-- Name: recordorder; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX recordorder ON public.records USING btree (domain_id, ordername text_pattern_ops); + + +-- +-- Name: cryptokeys cryptokeys_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.cryptokeys + ADD CONSTRAINT cryptokeys_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE; + + +-- +-- Name: records domain_exists; Type: FK CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.records + ADD CONSTRAINT domain_exists FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE; + + +-- +-- Name: comments domain_exists; Type: FK CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.comments + ADD CONSTRAINT domain_exists FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE; + + +-- +-- Name: domainmetadata domainmetadata_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domainmetadata + ADD CONSTRAINT domainmetadata_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE; + + +-- +-- Name: TABLE comments; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.comments TO dns; + + +-- +-- Name: TABLE cryptokeys; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.cryptokeys TO dns; + + +-- +-- Name: TABLE domainmetadata; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.domainmetadata TO dns; + + +-- +-- Name: TABLE domains; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.domains TO dns; + + +-- +-- Name: TABLE records; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.records TO dns; + + +-- +-- Name: TABLE supermasters; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.supermasters TO dns; + + +-- +-- Name: TABLE tsigkeys; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.tsigkeys TO dns; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/db/pdns/pdns-pgsql-create-4.5.sql b/db/pdns/pdns-pgsql-create-4.5.sql new file mode 100644 index 0000000..1750931 --- /dev/null +++ b/db/pdns/pdns-pgsql-create-4.5.sql @@ -0,0 +1,933 @@ + +\set AUTOCOMMIT on + +-- +-- PostgreSQL database cluster dump +-- + +SET default_transaction_read_only = off; + +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; + + + +-- +-- Drop roles +-- + +DROP ROLE IF EXISTS dns; +DROP ROLE IF EXISTS dnsui; +DROP ROLE IF EXISTS pdns; +DROP ROLE IF EXISTS pdns_local; +DROP ROLE IF EXISTS pdns_public; +DROP ROLE IF EXISTS pdns_ro; +DROP ROLE IF EXISTS postgres; + + +-- +-- Roles +-- + +CREATE ROLE dns; +ALTER ROLE dns WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity'; +COMMENT ON ROLE dns IS 'All DNS related users'; +CREATE ROLE dnsui; +ALTER ROLE dnsui WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md50e0de034f0030b820c36cae762e66019'; +CREATE ROLE pdns; +ALTER ROLE pdns WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md53f122a311bd86c1da3ef18fa8fde7a38'; +COMMENT ON ROLE pdns IS 'User for PowerDNS'; +CREATE ROLE pdns_local; +ALTER ROLE pdns_local WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5f61076c088c54686393ff13bb1e56e9b'; +COMMENT ON ROLE pdns_local IS 'User for local PowerDNS'; +CREATE ROLE pdns_public; +ALTER ROLE pdns_public WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md51150a78048c7549c99d474b6f015ae18'; +COMMENT ON ROLE pdns_public IS 'User for public PowerDNS'; +CREATE ROLE pdns_ro; +ALTER ROLE pdns_ro WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md587d52200eb2545657dc639f303b0e82f' VALID UNTIL 'infinity'; +COMMENT ON ROLE pdns_ro IS 'Readonly access to pdns database'; +CREATE ROLE postgres; +ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:bbtCWQHS+2GvNl68K2Y92g==$gldpMNWyh3xC5qCZtvZUXpqgnsLc5lu5w1XjxZQhk7U=:ugHogpQ93ImatOHCMTs09zLGJoloB0uAdnIDFPfMwAI='; + + +-- +-- Role memberships +-- + +GRANT dns TO pdns GRANTED BY postgres; +GRANT dns TO pdns_local GRANTED BY postgres; +GRANT dns TO pdns_public GRANTED BY postgres; +GRANT dns TO pdns_ro GRANTED BY postgres; + + + + +-- +-- PostgreSQL database cluster dump complete +-- + +-- +-- PostgreSQL database dump +-- + +-- Dumped from database version 14.1 +-- Dumped by pg_dump version 14.1 + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET idle_in_transaction_session_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SELECT pg_catalog.set_config('search_path', '', false); +SET check_function_bodies = false; +SET xmloption = content; +SET client_min_messages = warning; +SET row_security = off; + +DROP DATABASE IF EXISTS pdns; +-- +-- Name: pdns; Type: DATABASE; Schema: -; Owner: pdns +-- + +CREATE DATABASE pdns WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_US.UTF-8'; + + +ALTER DATABASE pdns OWNER TO pdns; + +\connect pdns + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET idle_in_transaction_session_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SELECT pg_catalog.set_config('search_path', '', false); +SET check_function_bodies = false; +SET xmloption = content; +SET client_min_messages = warning; +SET row_security = off; + +-- +-- Name: DATABASE pdns; Type: COMMENT; Schema: -; Owner: pdns +-- + +COMMENT ON DATABASE pdns IS 'PowerDNS authoritative server itself'; + + +-- +-- Name: pdns_local; Type: SCHEMA; Schema: -; Owner: pdns_local +-- + +CREATE SCHEMA pdns_local; + + +ALTER SCHEMA pdns_local OWNER TO pdns_local; + +-- +-- Name: pdns_public; Type: SCHEMA; Schema: -; Owner: pdns_public +-- + +CREATE SCHEMA pdns_public; + + +ALTER SCHEMA pdns_public OWNER TO pdns_public; + +SET default_tablespace = ''; + +SET default_table_access_method = heap; + +-- +-- Name: comments; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.comments ( + id integer NOT NULL, + domain_id integer NOT NULL, + name character varying(255) NOT NULL, + type character varying(10) NOT NULL, + modified_at integer NOT NULL, + account character varying(40) DEFAULT NULL::character varying, + comment character varying(65535) NOT NULL, + CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text))) +); + + +ALTER TABLE public.comments OWNER TO pdns; + +-- +-- Name: comments; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.comments AS + SELECT comments.id, + comments.domain_id, + comments.name, + comments.type, + comments.modified_at, + comments.account, + comments.comment + FROM public.comments + WHERE ((comments.comment IS NULL) OR ((comments.comment)::text !~* '.*public.*'::text)); + + +ALTER TABLE pdns_local.comments OWNER TO pdns_local; + +-- +-- Name: cryptokeys; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.cryptokeys ( + id integer NOT NULL, + domain_id integer, + flags integer NOT NULL, + active boolean, + published boolean DEFAULT true, + content text +); + + +ALTER TABLE public.cryptokeys OWNER TO pdns; + +-- +-- Name: cryptokeys; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.cryptokeys AS + SELECT cryptokeys.id, + cryptokeys.domain_id, + cryptokeys.flags, + cryptokeys.active, + cryptokeys.content, + cryptokeys.published + FROM public.cryptokeys; + + +ALTER TABLE pdns_local.cryptokeys OWNER TO pdns_local; + +-- +-- Name: domainmetadata; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.domainmetadata ( + id integer NOT NULL, + domain_id integer, + kind character varying(32), + content text +); + + +ALTER TABLE public.domainmetadata OWNER TO pdns; + +-- +-- Name: domainmetadata; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.domainmetadata AS + SELECT domainmetadata.id, + domainmetadata.domain_id, + domainmetadata.kind, + domainmetadata.content + FROM public.domainmetadata; + + +ALTER TABLE pdns_local.domainmetadata OWNER TO pdns_local; + +-- +-- Name: domains; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.domains ( + id integer NOT NULL, + name character varying(255) NOT NULL, + master character varying(128) DEFAULT NULL::character varying, + last_check integer, + type character varying(6) NOT NULL, + notified_serial bigint, + account character varying(40) DEFAULT NULL::character varying, + CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text))) +); + + +ALTER TABLE public.domains OWNER TO pdns; + +-- +-- Name: domains; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.domains AS + SELECT domains.id, + domains.name, + domains.master, + domains.last_check, + domains.type, + domains.notified_serial, + domains.account + FROM public.domains; + + +ALTER TABLE pdns_local.domains OWNER TO pdns_local; + +-- +-- Name: records; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.records ( + id integer NOT NULL, + domain_id integer, + name character varying(255) DEFAULT NULL::character varying, + type character varying(10) DEFAULT NULL::character varying, + content character varying(65535) DEFAULT NULL::character varying, + ttl integer, + prio integer, + disabled boolean DEFAULT false, + ordername character varying(255), + auth boolean DEFAULT true, + CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text))) +); + + +ALTER TABLE public.records OWNER TO pdns; + +-- +-- Name: records; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.records AS + SELECT records.id, + records.domain_id, + records.name, + records.type, + records.content, + records.ttl, + records.prio, + records.disabled, + records.ordername, + records.auth + FROM public.records + WHERE (((records.type)::text = 'SOA'::text) OR (NOT ((records.name)::text IN ( SELECT comments.name + FROM public.comments + WHERE ((comments.comment)::text ~* '.*public.*'::text))))); + + +ALTER TABLE pdns_local.records OWNER TO pdns_local; + +-- +-- Name: supermasters; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.supermasters ( + ip inet NOT NULL, + nameserver character varying(255) NOT NULL, + account character varying(40) NOT NULL +); + + +ALTER TABLE public.supermasters OWNER TO pdns; + +-- +-- Name: supermasters; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.supermasters AS + SELECT supermasters.ip, + supermasters.nameserver, + supermasters.account + FROM public.supermasters; + + +ALTER TABLE pdns_local.supermasters OWNER TO pdns_local; + +-- +-- Name: tsigkeys; Type: TABLE; Schema: public; Owner: pdns +-- + +CREATE TABLE public.tsigkeys ( + id integer NOT NULL, + name character varying(255), + algorithm character varying(50), + secret character varying(255), + CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text))) +); + + +ALTER TABLE public.tsigkeys OWNER TO pdns; + +-- +-- Name: tsigkeys; Type: VIEW; Schema: pdns_local; Owner: pdns_local +-- + +CREATE VIEW pdns_local.tsigkeys AS + SELECT tsigkeys.id, + tsigkeys.name, + tsigkeys.algorithm, + tsigkeys.secret + FROM public.tsigkeys; + + +ALTER TABLE pdns_local.tsigkeys OWNER TO pdns_local; + +-- +-- Name: domains; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.domains AS + SELECT domains.id, + domains.name, + domains.master, + domains.last_check, + domains.type, + domains.notified_serial, + domains.account + FROM public.domains + WHERE ((domains.account IS NULL) OR ((domains.account)::text !~* '.*(lo[ck]al|privat|intern).*'::text)); + + +ALTER TABLE pdns_public.domains OWNER TO pdns_public; + +-- +-- Name: comments; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.comments AS + SELECT comments.id, + comments.domain_id, + comments.name, + comments.type, + comments.modified_at, + comments.account, + comments.comment + FROM public.comments + WHERE (((comments.domain_id IS NULL) OR (comments.domain_id IN ( SELECT domains.id + FROM pdns_public.domains))) AND ((comments.comment IS NULL) OR ((comments.comment)::text !~* '.*(lo[ck]al|privat|intern).*'::text))); + + +ALTER TABLE pdns_public.comments OWNER TO pdns_public; + +-- +-- Name: cryptokeys; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.cryptokeys AS + SELECT cryptokeys.id, + cryptokeys.domain_id, + cryptokeys.flags, + cryptokeys.active, + cryptokeys.content, + cryptokeys.published + FROM public.cryptokeys + WHERE ((cryptokeys.domain_id IS NULL) OR (cryptokeys.domain_id IN ( SELECT domains.id + FROM pdns_public.domains))); + + +ALTER TABLE pdns_public.cryptokeys OWNER TO pdns_public; + +-- +-- Name: domainmetadata; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.domainmetadata AS + SELECT domainmetadata.id, + domainmetadata.domain_id, + domainmetadata.kind, + domainmetadata.content + FROM public.domainmetadata + WHERE ((domainmetadata.domain_id IS NULL) OR (domainmetadata.domain_id IN ( SELECT domains.id + FROM pdns_public.domains))); + + +ALTER TABLE pdns_public.domainmetadata OWNER TO pdns_public; + +-- +-- Name: records; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.records AS + SELECT records.id, + records.domain_id, + records.name, + records.type, + records.content, + records.ttl, + records.prio, + records.disabled, + records.ordername, + records.auth + FROM public.records + WHERE ((records.domain_id IN ( SELECT domains.id + FROM pdns_public.domains)) AND (((records.type)::text = 'SOA'::text) OR (NOT ((records.name)::text IN ( SELECT comments.name + FROM public.comments + WHERE ((comments.comment)::text ~* '.*(lo[ck]al|privat|intern).*'::text)))))); + + +ALTER TABLE pdns_public.records OWNER TO pdns_public; + +-- +-- Name: supermasters; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.supermasters AS + SELECT supermasters.ip, + supermasters.nameserver, + supermasters.account + FROM public.supermasters; + + +ALTER TABLE pdns_public.supermasters OWNER TO pdns_public; + +-- +-- Name: tsigkeys; Type: VIEW; Schema: pdns_public; Owner: pdns_public +-- + +CREATE VIEW pdns_public.tsigkeys AS + SELECT tsigkeys.id, + tsigkeys.name, + tsigkeys.algorithm, + tsigkeys.secret + FROM public.tsigkeys; + + +ALTER TABLE pdns_public.tsigkeys OWNER TO pdns_public; + +-- +-- Name: comments_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.comments_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.comments_id_seq OWNER TO pdns; + +-- +-- Name: comments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.comments_id_seq OWNED BY public.comments.id; + + +-- +-- Name: cryptokeys_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.cryptokeys_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.cryptokeys_id_seq OWNER TO pdns; + +-- +-- Name: cryptokeys_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.cryptokeys_id_seq OWNED BY public.cryptokeys.id; + + +-- +-- Name: domainmetadata_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.domainmetadata_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.domainmetadata_id_seq OWNER TO pdns; + +-- +-- Name: domainmetadata_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.domainmetadata_id_seq OWNED BY public.domainmetadata.id; + + +-- +-- Name: domains_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.domains_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.domains_id_seq OWNER TO pdns; + +-- +-- Name: domains_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.domains_id_seq OWNED BY public.domains.id; + + +-- +-- Name: records_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.records_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.records_id_seq OWNER TO pdns; + +-- +-- Name: records_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.records_id_seq OWNED BY public.records.id; + + +-- +-- Name: tsigkeys_id_seq; Type: SEQUENCE; Schema: public; Owner: pdns +-- + +CREATE SEQUENCE public.tsigkeys_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.tsigkeys_id_seq OWNER TO pdns; + +-- +-- Name: tsigkeys_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pdns +-- + +ALTER SEQUENCE public.tsigkeys_id_seq OWNED BY public.tsigkeys.id; + + +-- +-- Name: v_domainmetadata; Type: VIEW; Schema: public; Owner: pdns +-- + +CREATE VIEW public.v_domainmetadata AS + SELECT md.id AS metadata_id, + md.domain_id, + d.name AS domain_name, + md.kind AS metadata_kind, + md.content AS metadata_content, + d.master AS domain_master, + d.last_check AS domain_last_check, + d.type AS domain_type, + d.notified_serial AS domain_serial, + d.account AS domain_account + FROM (public.domainmetadata md + LEFT JOIN public.domains d ON ((md.domain_id = d.id))) + ORDER BY d.name, md.kind; + + +ALTER TABLE public.v_domainmetadata OWNER TO pdns; + +-- +-- Name: v_records; Type: VIEW; Schema: public; Owner: pdns +-- + +CREATE VIEW public.v_records AS + SELECT r.id AS record_id, + r.domain_id, + r.name AS record_name, + r.type AS record_type, + r.content AS record_content, + r.ttl AS record_ttl, + r.prio AS record_prio, + r.disabled AS record_disabled, + r.ordername AS record_ordername, + r.auth AS record_auth, + d.name AS domain_name, + d.master AS domain_master, + d.last_check AS domain_last_check, + d.type AS domain_type, + d.notified_serial AS domain_notified_serial, + d.account AS domain_account, + c.id AS comment_id, + c.name AS comment_name, + c.type AS comment_type, + c.modified_at AS comment_modified_at, + c.account AS comment_account, + c.comment + FROM ((public.records r + LEFT JOIN public.domains d ON ((d.id = r.domain_id))) + LEFT JOIN public.comments c ON ((((r.name)::text = (c.name)::text) AND ((r.type)::text = (c.type)::text)))) + ORDER BY d.name, r.name; + + +ALTER TABLE public.v_records OWNER TO pdns; + +-- +-- Name: comments id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.comments ALTER COLUMN id SET DEFAULT nextval('public.comments_id_seq'::regclass); + + +-- +-- Name: cryptokeys id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.cryptokeys ALTER COLUMN id SET DEFAULT nextval('public.cryptokeys_id_seq'::regclass); + + +-- +-- Name: domainmetadata id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domainmetadata ALTER COLUMN id SET DEFAULT nextval('public.domainmetadata_id_seq'::regclass); + + +-- +-- Name: domains id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domains ALTER COLUMN id SET DEFAULT nextval('public.domains_id_seq'::regclass); + + +-- +-- Name: records id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.records ALTER COLUMN id SET DEFAULT nextval('public.records_id_seq'::regclass); + + +-- +-- Name: tsigkeys id; Type: DEFAULT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.tsigkeys ALTER COLUMN id SET DEFAULT nextval('public.tsigkeys_id_seq'::regclass); + + +-- +-- Name: comments comments_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.comments + ADD CONSTRAINT comments_pkey PRIMARY KEY (id); + + +-- +-- Name: cryptokeys cryptokeys_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.cryptokeys + ADD CONSTRAINT cryptokeys_pkey PRIMARY KEY (id); + + +-- +-- Name: domainmetadata domainmetadata_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domainmetadata + ADD CONSTRAINT domainmetadata_pkey PRIMARY KEY (id); + + +-- +-- Name: domains domains_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domains + ADD CONSTRAINT domains_pkey PRIMARY KEY (id); + + +-- +-- Name: records records_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.records + ADD CONSTRAINT records_pkey PRIMARY KEY (id); + + +-- +-- Name: supermasters supermasters_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.supermasters + ADD CONSTRAINT supermasters_pkey PRIMARY KEY (ip, nameserver); + + +-- +-- Name: tsigkeys tsigkeys_pkey; Type: CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.tsigkeys + ADD CONSTRAINT tsigkeys_pkey PRIMARY KEY (id); + + +-- +-- Name: comments_domain_id_idx; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX comments_domain_id_idx ON public.comments USING btree (domain_id); + + +-- +-- Name: comments_name_type_idx; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX comments_name_type_idx ON public.comments USING btree (name, type); + + +-- +-- Name: comments_order_idx; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX comments_order_idx ON public.comments USING btree (domain_id, modified_at); + + +-- +-- Name: domain_id; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX domain_id ON public.records USING btree (domain_id); + + +-- +-- Name: domainidindex; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX domainidindex ON public.cryptokeys USING btree (domain_id); + + +-- +-- Name: domainidmetaindex; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX domainidmetaindex ON public.domainmetadata USING btree (domain_id); + + +-- +-- Name: name_index; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE UNIQUE INDEX name_index ON public.domains USING btree (name); + + +-- +-- Name: namealgoindex; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE UNIQUE INDEX namealgoindex ON public.tsigkeys USING btree (name, algorithm); + + +-- +-- Name: nametype_index; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX nametype_index ON public.records USING btree (name, type); + + +-- +-- Name: rec_name_index; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX rec_name_index ON public.records USING btree (name); + + +-- +-- Name: recordorder; Type: INDEX; Schema: public; Owner: pdns +-- + +CREATE INDEX recordorder ON public.records USING btree (domain_id, ordername text_pattern_ops); + + +-- +-- Name: cryptokeys cryptokeys_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.cryptokeys + ADD CONSTRAINT cryptokeys_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE; + + +-- +-- Name: records domain_exists; Type: FK CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.records + ADD CONSTRAINT domain_exists FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE; + + +-- +-- Name: comments domain_exists; Type: FK CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.comments + ADD CONSTRAINT domain_exists FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE; + + +-- +-- Name: domainmetadata domainmetadata_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pdns +-- + +ALTER TABLE ONLY public.domainmetadata + ADD CONSTRAINT domainmetadata_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE; + + +-- +-- Name: TABLE comments; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.comments TO dns; + + +-- +-- Name: TABLE cryptokeys; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.cryptokeys TO dns; + + +-- +-- Name: TABLE domainmetadata; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.domainmetadata TO dns; + + +-- +-- Name: TABLE domains; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.domains TO dns; + + +-- +-- Name: TABLE records; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.records TO dns; + + +-- +-- Name: TABLE supermasters; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.supermasters TO dns; + + +-- +-- Name: TABLE tsigkeys; Type: ACL; Schema: public; Owner: pdns +-- + +GRANT SELECT ON TABLE public.tsigkeys TO dns; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/db/pdns/pdns-pgsql-upgrade.4.1.0_to_4.3.0.sql b/db/pdns/pdns-pgsql-upgrade.4.1.0_to_4.3.0.sql new file mode 100644 index 0000000..cf2ee06 --- /dev/null +++ b/db/pdns/pdns-pgsql-upgrade.4.1.0_to_4.3.0.sql @@ -0,0 +1,227 @@ +BEGIN; + +\echo +\echo 'Connecting to pdns ...' + +\connect pdns + +\echo +\echo 'Dropping views ...' + +\echo ' * public.v_records' +DROP VIEW IF EXISTS public.v_records; +\echo ' * public.v_domainmetadata' +DROP VIEW IF EXISTS public.v_domainmetadata; + +\echo ' * pdns_public.cryptokeys' +DROP VIEW IF EXISTS pdns_public.cryptokeys; +\echo ' * pdns_public.comments' +DROP VIEW IF EXISTS pdns_public.comments; +\echo ' * pdns_public.domainmetadata' +DROP VIEW IF EXISTS pdns_public.domainmetadata; +\echo ' * pdns_public.records' +DROP VIEW IF EXISTS pdns_public.records; +\echo ' * pdns_public.domains' +DROP VIEW IF EXISTS pdns_public.domains; + + +\echo ' * pdns_local.cryptokeys' +DROP VIEW IF EXISTS pdns_local.cryptokeys; +\echo ' * pdns_local.records' +DROP VIEW IF EXISTS pdns_local.records; +\echo ' * pdns_local.domains' +DROP VIEW IF EXISTS pdns_local.domains; + +\echo +\echo 'Altering table public.records ...' +ALTER TABLE public.records DROP COLUMN change_date; + +\echo +\echo 'Altering table public.domains ...' +ALTER TABLE public.domains ALTER notified_serial TYPE bigint USING CASE WHEN notified_serial >= 0 THEN notified_serial::bigint END; + +\echo +\echo 'Altering table public.cryptokeys ...' +ALTER TABLE public.cryptokeys ADD COLUMN published BOOL DEFAULT TRUE; + +ALTER TABLE public.cryptokeys ADD COLUMN content_new TEXT; +UPDATE public.cryptokeys SET content_new = content; +ALTER TABLE public.cryptokeys DROP COLUMN content; +ALTER TABLE public.cryptokeys RENAME COLUMN content_new TO content; + +\echo +\echo 'Creating views ...' + +\echo ' * public.v_domainmetadata' +CREATE VIEW public.v_domainmetadata AS + SELECT md.id AS metadata_id, + md.domain_id, + d.name AS domain_name, + md.kind AS metadata_kind, + md.content AS metadata_content, + d.master AS domain_master, + d.last_check AS domain_last_check, + d.type AS domain_type, + d.notified_serial AS domain_serial, + d.account AS domain_account + FROM (public.domainmetadata md + LEFT JOIN public.domains d ON ((md.domain_id = d.id))) + ORDER BY d.name, md.kind; + + +ALTER TABLE public.v_domainmetadata OWNER TO pdns; + +\echo ' * public.v_records' +CREATE VIEW public.v_records AS + SELECT r.id AS record_id, + r.domain_id, + r.name AS record_name, + r.type AS record_type, + r.content AS record_content, + r.ttl AS record_ttl, + r.prio AS record_prio, + r.disabled AS record_disabled, + r.ordername AS record_ordername, + r.auth AS record_auth, + d.name AS domain_name, + d.master AS domain_master, + d.last_check AS domain_last_check, + d.type AS domain_type, + d.notified_serial AS domain_notified_serial, + d.account AS domain_account, + c.id AS comment_id, + c.name AS comment_name, + c.type AS comment_type, + c.modified_at AS comment_modified_at, + c.account AS comment_account, + c.comment + FROM records r + LEFT JOIN domains d ON d.id = r.domain_id + LEFT JOIN comments c ON r.name::text = c.name::text AND r.type::text = c.type::text + ORDER BY d.name, r.name; + +ALTER TABLE public.v_records OWNER TO pdns; + +\echo ' * pdns_local.domains' +CREATE VIEW pdns_local.domains AS + SELECT domains.id, + domains.name, + domains.master, + domains.last_check, + domains.type, + domains.notified_serial, + domains.account + FROM public.domains; + +ALTER TABLE pdns_local.domains OWNER TO pdns_local; + +\echo ' * pdns_public.domains' +CREATE VIEW pdns_public.domains AS + SELECT domains.id, + domains.name, + domains.master, + domains.last_check, + domains.type, + domains.notified_serial, + domains.account + FROM public.domains + WHERE ((domains.account IS NULL) OR ((domains.account)::text !~* '.*(lo[ck]al|privat|intern).*'::text)); + +ALTER TABLE pdns_public.domains OWNER TO pdns_public; + +\echo ' * pdns_public.comments' +CREATE VIEW pdns_public.comments AS + SELECT comments.id, + comments.domain_id, + comments.name, + comments.type, + comments.modified_at, + comments.account, + comments.comment + FROM public.comments + WHERE (((comments.domain_id IS NULL) OR (comments.domain_id IN ( SELECT domains.id + FROM pdns_public.domains))) AND ((comments.comment IS NULL) OR ((comments.comment)::text !~* '.*(lo[ck]al|privat|intern).*'::text))); + +ALTER TABLE pdns_public.comments OWNER TO pdns_public; + +\echo ' * pdns_public.domainmetadata' +CREATE VIEW pdns_public.domainmetadata AS + SELECT domainmetadata.id, + domainmetadata.domain_id, + domainmetadata.kind, + domainmetadata.content + FROM public.domainmetadata + WHERE ((domainmetadata.domain_id IS NULL) OR (domainmetadata.domain_id IN ( SELECT domains.id + FROM pdns_public.domains))); + +ALTER TABLE pdns_public.domainmetadata OWNER TO pdns_public; + +\echo ' * pdns_local.records' +CREATE VIEW pdns_local.records AS + SELECT records.id, + records.domain_id, + records.name, + records.type, + records.content, + records.ttl, + records.prio, + records.disabled, + records.ordername, + records.auth + FROM public.records + WHERE (((records.type)::text = 'SOA'::text) OR (NOT ((records.name)::text IN ( SELECT comments.name + FROM public.comments + WHERE ((comments.comment)::text ~* '.*public.*'::text))))); + +ALTER TABLE pdns_local.records OWNER TO pdns_local; + +\echo ' * pdns_public.records' +CREATE VIEW pdns_public.records AS + SELECT records.id, + records.domain_id, + records.name, + records.type, + records.content, + records.ttl, + records.prio, + records.disabled, + records.ordername, + records.auth + FROM public.records + WHERE ((records.domain_id IN ( SELECT domains.id + FROM pdns_public.domains)) AND (((records.type)::text = 'SOA'::text) OR (NOT ((records.name)::text IN ( SELECT comments.name + FROM public.comments + WHERE ((comments.comment)::text ~* '.*(lo[ck]al|privat|intern).*'::text)))))); + + +ALTER TABLE pdns_public.records OWNER TO pdns_public; + +\echo ' * pdns_local.cryptokeys' +CREATE VIEW pdns_local.cryptokeys AS + SELECT cryptokeys.id, + cryptokeys.domain_id, + cryptokeys.flags, + cryptokeys.active, + cryptokeys.content, + cryptokeys.published + FROM public.cryptokeys; + +ALTER TABLE pdns_local.cryptokeys OWNER TO pdns_local; + +\echo ' * pdns_public.cryptokeys' +CREATE VIEW pdns_public.cryptokeys AS + SELECT cryptokeys.id, + cryptokeys.domain_id, + cryptokeys.flags, + cryptokeys.active, + cryptokeys.content, + cryptokeys.published + FROM public.cryptokeys + WHERE ((cryptokeys.domain_id IS NULL) OR (cryptokeys.domain_id IN ( SELECT domains.id + FROM pdns_public.domains))); + + +ALTER TABLE pdns_public.cryptokeys OWNER TO pdns_public; + +COMMIT; + -- 2.39.5