From bd5cbebfa419d5b28cadc4c33044edb3419a5280 Mon Sep 17 00:00:00 2001
From: Jakub Czajka <jakub@ekhem.eu.org>
Date: Sun, 6 Nov 2022 15:37:16 +0100
Subject: [PATCH] [opendkim] Lookup KeyTable and SigningTable with SQL.

See `man opendkim.conf` for more information on KeyTable and
SigningTable. `opendkim` connects to postgres via ::1/128.
---
 databases/mail/mail_db_create.sql | 29 +++++++++++++++++++++++++++++
 databases/mail/mail_db_drop.sql   | 17 +++++++++++++++++
 opendkim/README                   |  2 +-
 opendkim/opendkim.conf            |  4 ++++
 postgres/pg_hba.conf              |  1 +
 5 files changed, 52 insertions(+), 1 deletion(-)

diff --git a/databases/mail/mail_db_create.sql b/databases/mail/mail_db_create.sql
index a1c1f50..7da4ccb 100644
--- a/databases/mail/mail_db_create.sql
+++ b/databases/mail/mail_db_create.sql
@@ -1,13 +1,28 @@
 -- Copyright (c) 2022 Jakub Czajka <jakub@ekhem.eu.org>
 -- License: 0BSD.
 
+CREATE TABLE IF NOT EXISTS dkim_keys (
+    name VARCHAR(64) PRIMARY KEY,
+    path VARCHAR(128) NOT NULL
+);
+
 CREATE TABLE IF NOT EXISTS users (
     username VARCHAR(128) PRIMARY KEY,
     domain VARCHAR(128) NOT NULL,
     password VARCHAR(64) NOT NULL,
+    dkim_key VARCHAR(64) REFERENCES dkim_keys (name),
     active CHAR(1) DEFAULT 'Y' NOT NULL
 );
 
+CREATE OR REPLACE VIEW users_dkim_keys (key, username, domain, selector, path)
+  AS SELECT CONCAT(name, '._domainkey.', domain),
+	    CONCAT(username, '@', domain),
+	    domain,
+	    name,
+	    path
+     FROM users INNER JOIN dkim_keys
+       ON dkim_key = name;
+
 DO $$
 BEGIN
     IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = 'dovecot')
@@ -23,4 +38,18 @@ BEGIN
 		        ON DATABASE %I
 			TO dovecot', current_database());
     END IF;
+
+    IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = 'dkim')
+    THEN
+	CREATE ROLE dkim LOGIN;
+
+	GRANT SELECT
+	ON TABLE users_dkim_keys
+	TO dkim;
+
+        /* Execute for the current database. */
+	EXECUTE FORMAT('GRANT CONNECT
+                        ON DATABASE %I
+                        TO dkim', current_database());
+    END IF;
 END$$;
diff --git a/databases/mail/mail_db_drop.sql b/databases/mail/mail_db_drop.sql
index ba1f0cc..77b8d73 100644
--- a/databases/mail/mail_db_drop.sql
+++ b/databases/mail/mail_db_drop.sql
@@ -16,6 +16,23 @@ BEGIN
 
 	DROP ROLE dovecot;
     END IF;
+
+    IF EXISTS (SELECT * FROM pg_user WHERE usename = 'dkim')
+    THEN
+        REVOKE SELECT
+	ON TABLE users_dkim_keys
+	FROM dkim;
+
+	EXECUTE
+            FORMAT('REVOKE CONNECT
+		    ON DATABASE %I
+		    FROM dkim;', current_database());
+
+	DROP ROLE dkim;
+    END IF;
 END$$;
 
+DROP VIEW IF EXISTS users_dkim_keys;
+
 DROP TABLE IF EXISTS users;
+DROP TABLE IF EXISTS dkim_keys;
diff --git a/opendkim/README b/opendkim/README
index fb0f443..449e7a1 100644
--- a/opendkim/README
+++ b/opendkim/README
@@ -18,5 +18,5 @@ Install
 -------
 
 ```
-$ apt install opendkim
+$ apt install opendkim libopendbx1-pgsql
 ```
diff --git a/opendkim/opendkim.conf b/opendkim/opendkim.conf
index 891acb3..b64f8f0 100644
--- a/opendkim/opendkim.conf
+++ b/opendkim/opendkim.conf
@@ -49,3 +49,7 @@ PidFile			/run/opendkim/opendkim.pid
 # by the package dns-root-data.
 TrustAnchorFile		/usr/share/dns/root.key
 #Nameservers		127.0.0.1
+
+# Connect to postgres on localhost:5432 as dkim.
+KeyTable                dsn:pgsql://dkim@5432+localhost/mail_db/table=users_dkim_keys?keycol=key?datacol=domain,selector,path
+SigningTable            dsn:pgsql://dkim@5432+localhost/mail_db/table=users_dkim_keys?keycol=username?datacol=key
diff --git a/postgres/pg_hba.conf b/postgres/pg_hba.conf
index 394c489..87c9bb7 100644
--- a/postgres/pg_hba.conf
+++ b/postgres/pg_hba.conf
@@ -91,6 +91,7 @@ local   all             postgres                                peer
 # TYPE  DATABASE        USER            ADDRESS                 METHOD
 
 local   mail_db         dovecot                                 trust
+host    mail_db         dkim            ::1/128                 trust
 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
-- 
2.39.5