From 2e51a1f1c655884c65911f1322ecc15dfea84551 Mon Sep 17 00:00:00 2001
From: Frank Brehm <frank.brehm@pixelpark.com>
Date: Thu, 2 Nov 2017 14:47:34 +0100
Subject: [PATCH] Creating additional DB ussers in dns/schema.pgsql.sql

---
 dns/schema.pgsql.sql | 44 ++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 44 insertions(+)

diff --git a/dns/schema.pgsql.sql b/dns/schema.pgsql.sql
index 5694232..f77a545 100644
--- a/dns/schema.pgsql.sql
+++ b/dns/schema.pgsql.sql
@@ -2,20 +2,46 @@
 CREATE DATABASE pdns WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
 CREATE DATABASE pdnsadm WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
 
+CREATE DATABASE pdns_local WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
+CREATE DATABASE pdns_public WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
+
 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';
+COMMENT ON ROLE pdns IS 'User for PowerDNS';
+GRANT dns TO pdns;
 GRANT ALL ON DATABASE "pdns" TO pdns;
 ALTER DATABASE pdns OWNER TO pdns;
 
 CREATE USER pdnsadm WITH PASSWORD 'Eig}o3ojoh@w';
+COMMENT ON ROLE pdnsadm IS 'User for different PowerDNS GUIs';
+GRANT dns TO pdnsadm;
 GRANT ALL ON DATABASE "pdnsadm" TO pdnsadm;
 ALTER DATABASE pdnsadm OWNER TO pdnsadm;
 
+CREATE USER pdns_local WITH PASSWORD 'Neingeichee(ghahn7ah';
+COMMENT ON ROLE pdns IS 'User for local PowerDNS';
+GRANT dns TO pdns_local;
+GRANT ALL ON DATABASE "pdns_local" TO pdns_local;
+ALTER DATABASE pdns_local OWNER TO pdns_local;
+
+CREATE USER pdns_public WITH PASSWORD 'ahr7die3gee]s4vahCoh';
+COMMENT ON ROLE pdns IS 'User for public PowerDNS';
+GRANT dns TO pdns_public;
+GRANT ALL ON DATABASE "pdns_public" TO pdns_public;
+ALTER DATABASE pdns_public OWNER TO pdns_public;
+
 COMMIT;
 
+-- ----------------------------------------------------------------------
+
 BEGIN WORK;
 
+-- ----------------
 CREATE TABLE domains (
   id                    SERIAL PRIMARY KEY,
   name                  VARCHAR(255) NOT NULL,
@@ -32,6 +58,9 @@ 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;
 
+GRANT SELECT ON TABLE domains TO dns;
+
+-- ----------------
 CREATE TABLE records (
   id                    SERIAL PRIMARY KEY,
   domain_id             INT DEFAULT NULL,
@@ -58,6 +87,9 @@ 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;
 
+GRANT SELECT ON TABLE records TO dns;
+
+-- ----------------
 CREATE TABLE supermasters (
   ip                    INET NOT NULL,
   nameserver            VARCHAR(255) NOT NULL,
@@ -67,6 +99,9 @@ CREATE TABLE supermasters (
 
 ALTER TABLE IF EXISTS supermasters OWNER TO pdns;
 
+GRANT SELECT ON TABLE supermasters TO dns;
+
+-- ----------------
 CREATE TABLE comments (
   id                    SERIAL PRIMARY KEY,
   domain_id             INT NOT NULL,
@@ -88,6 +123,9 @@ 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;
 
+GRANT SELECT ON TABLE comments TO dns;
+
+-- ----------------
 CREATE TABLE domainmetadata (
   id                    SERIAL PRIMARY KEY,
   domain_id             INT REFERENCES domains(id) ON DELETE CASCADE,
@@ -100,6 +138,9 @@ 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;
 
+GRANT SELECT ON TABLE domainmetadata TO dns;
+
+-- ----------------
 CREATE TABLE cryptokeys (
   id                    SERIAL PRIMARY KEY,
   domain_id             INT REFERENCES domains(id) ON DELETE CASCADE,
@@ -113,6 +154,7 @@ 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 (
   id                    SERIAL PRIMARY KEY,
   name                  VARCHAR(255),
@@ -126,4 +168,6 @@ 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;
 
+GRANT SELECT ON TABLE tsigkeys TO dns;
+
 COMMIT;
-- 
2.39.5