commit 901f37eab4617b0686fd075703e73415617df65c
from: Oliver Lowe <o@olowe.co>
date: Sun Feb 27 00:25:27 2022 UTC

Allow management of mailboxes from an account

This introduces a new type, called account. An account can manage
domains and, in turn, mailboxes.

commit - 23b343202bc8dc2d8b5becc72cf4f0b660f4e075
commit + 901f37eab4617b0686fd075703e73415617df65c
blob - 68d707dcb9a1b960396164937f6416eb77a2c5a8
blob + 732dd8990717ea227577300add0fde6f7ea00231
--- accounts.conf
+++ accounts.conf
@@ -1,4 +1,4 @@
 dbpath /mail/lib/accounts.db
 query_alias SELECT destination FROM aliases WHERE recipient=?;
-query_credentials SELECT username, password FROM users WHERE username=?;
+query_credentials SELECT address, password FROM mailboxes WHERE address=?;
 query_domain SELECT domain FROM domains WHERE domain=?;
blob - 41066eef9490b51e7974d4ca7208c571f9022fe5
blob + 69392b2ac07db29c7e2f48d7cc220d13960b25cf
--- dovecot-sql.conf.ext
+++ dovecot-sql.conf.ext
@@ -1,6 +1,4 @@
 driver = sqlite
 connect = /mail/lib/accounts.db
 default_pass_scheme = BLF-CRYPT
-password_query = SELECT username AS user, password FROM users WHERE username = '%u'
-
-#iterate_query = SELECT username AS user FROM users
+password_query = SELECT address AS user, password FROM mailboxes WHERE address = '%u'
blob - 5b2fd74c54ac1ee9f3160916521c10fdee29d982
blob + c021ed5090519afa2c0e43c4d3cc68d4c7135c95
--- init.sql
+++ init.sql
@@ -1,16 +1,25 @@
-CREATE TABLE aliases (
+CREATE TABLE accounts (
 	id INTEGER PRIMARY KEY AUTOINCREMENT,
-	recipient VARCHAR(255) NOT NULL,
-	destination VARCHAR(255) NOT NULL
+	name TEXT NOT NULL,
+	username TEXT NOT NULL,
+	password TEXT NOT NULL
 );
 
-CREATE TABLE users (
+CREATE TABLE domains (
 	id INTEGER PRIMARY KEY AUTOINCREMENT,
-	username VARCHAR(255) NOT NULL,
-	password VARCHAR(255) NOT NULL
+	account_id INTEGER NOT NULL,
+	domain VARCHAR(255) NOT NULL
 );
 
-CREATE TABLE domains (
+CREATE TABLE mailboxes (
 	id INTEGER PRIMARY KEY AUTOINCREMENT,
-	domain VARCHAR(255) NOT NULL
+	address VARCHAR(255) NOT NULL,
+	password VARCHAR(255) NOT NULL,
+	domain_id INTEGER NOT NULL
 );
+
+CREATE TABLE aliases (
+	id INTEGER PRIMARY KEY AUTOINCREMENT,
+	recipient VARCHAR(255) NOT NULL,
+	destination VARCHAR(255) NOT NULL
+);