From f0206ad0d8c1cd50833ad2928c3831ae5e307b40 Mon Sep 17 00:00:00 2001
From: Jakub Czajka <jakub@ekhem.eu.org>
Date: Sat, 29 Oct 2022 10:34:54 +0200
Subject: [PATCH] [dovecot] Authenticate users found in the address book.

Table `users` is an address book. User `dovecot` queries it to
authenticate clients and redirect email to mailboxes.
---
 dovecot/10-auth.conf         |  4 ++--
 dovecot/README               | 28 ++++++++++++++++++++++++++++
 dovecot/dovecot-sql.conf.ext | 14 +++++++-------
 postgres/pg_hba.conf         |  1 +
 4 files changed, 38 insertions(+), 9 deletions(-)

diff --git a/dovecot/10-auth.conf b/dovecot/10-auth.conf
index 978e8ae..ee3adfd 100644
--- a/dovecot/10-auth.conf
+++ b/dovecot/10-auth.conf
@@ -119,8 +119,8 @@ auth_mechanisms = plain
 #!include auth-deny.conf.ext
 #!include auth-master.conf.ext
 
-!include auth-system.conf.ext
-#!include auth-sql.conf.ext
+#!include auth-system.conf.ext
+!include auth-sql.conf.ext
 #!include auth-ldap.conf.ext
 #!include auth-passwdfile.conf.ext
 #!include auth-checkpassword.conf.ext
diff --git a/dovecot/README b/dovecot/README
index 5e6f544..8d57ec8 100644
--- a/dovecot/README
+++ b/dovecot/README
@@ -76,3 +76,31 @@ Each package corresponds to a plugin:
 ```
 $ apt install dovecot-imapd dovecot-pgsql
 ```
+
+Tables
+------
+
+Create database `mail_db`.
+
+```
+CREATE DATABASE mail_db;
+```
+
+Execute `mail_db_create.sql`.
+
+```
+psql --dbname=mail_db --file=databases/mail/mail_db_create.sql
+```
+
+Administration
+--------------
+
+Add a user.
+
+```
+psql --username=postgres --dbname=<database> \
+  --command="INSERT INTO users \
+
+             VALUES ('user', 'domain.com', MD5('<password>'), \
+	             '/home/debian', id -u, id -g)"
+```
diff --git a/dovecot/dovecot-sql.conf.ext b/dovecot/dovecot-sql.conf.ext
index 0bc854b..628746f 100644
--- a/dovecot/dovecot-sql.conf.ext
+++ b/dovecot/dovecot-sql.conf.ext
@@ -71,7 +71,7 @@ driver = pgsql
 #   connect = host=sql.example.com dbname=virtual user=virtual password=blarg
 #   connect = /etc/dovecot/authdb.sqlite
 #
-#connect =
+connect = dbname=mail_db user=dovecot
 
 # Default password scheme.
 #
@@ -107,9 +107,9 @@ driver = pgsql
 #   password_query = SELECT userid AS user, pw AS password \
 #     FROM users WHERE userid = '%u' AND active = 'Y'
 #
-#password_query = \
-#  SELECT username, domain, password \
-#  FROM users WHERE username = '%n' AND domain = '%d'
+password_query = \
+  SELECT username, domain, password \
+  FROM users WHERE username = '%n' AND domain = '%d' AND active = 'Y'
 
 # userdb query to retrieve the user information. It can return fields:
 #   uid - System UID (overrides mail_uid setting)
@@ -127,9 +127,9 @@ driver = pgsql
 #   user_query = SELECT dir AS home, user AS uid, group AS gid FROM users where userid = '%u'
 #   user_query = SELECT home, 501 AS uid, 501 AS gid FROM users WHERE userid = '%u'
 #
-#user_query = \
-#  SELECT home, uid, gid \
-#  FROM users WHERE username = '%n' AND domain = '%d'
+user_query = \
+  SELECT home, uid, gid \
+  FROM users WHERE username = '%n' AND domain = '%d' AND active = 'Y'
 
 # If you wish to avoid two SQL lookups (passdb + userdb), you can use
 # userdb prefetch instead of userdb sql in dovecot.conf. In that case you'll
diff --git a/postgres/pg_hba.conf b/postgres/pg_hba.conf
index b6595d8..394c489 100644
--- a/postgres/pg_hba.conf
+++ b/postgres/pg_hba.conf
@@ -90,6 +90,7 @@ local   all             postgres                                peer
 
 # TYPE  DATABASE        USER            ADDRESS                 METHOD
 
+local   mail_db         dovecot                                 trust
 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
-- 
2.39.5