Hello ports@,
Below is an update to the table_sqlite(5) man page from the
opensmtpd-extras package to reflect the new syntax of OpenSMTPD and
clarifications and example usage of query_addrname and query_userinfo.
I am still figuring out how to use the remaining 3 queries in the TODO
section.
Thanks,
Matt
--- table-sqlite.5 Mon Sep 3 10:48:42 2018
+++ table-sqlite.5 Mon Sep 3 10:45:53 2018
@@ -98,6 +98,27 @@
is replaced with the appropriate data. For the domain it would be the
right hand side of the SMTP address. This expects one VARCHAR to be returned
with a matching domain name.
+.Pp
+
+.It Xo
+.Ic query_userinfo
+.Ar SQL statement
+.Xc
+This is used to provide a query for looking up users listed in a userbase
+table. The question mark is replaced with the appropriate data. For userinfo,
+the left hand side is the virtual user name. The query expects that there are
+three VARCHARS returned: a uid, a gid, and a home directory.
+.Pp
+
+.It Xo
+.Ic query_addrname
+.Ar SQL statement
+.Xc
+This is used to provide a query for looking up a hostname associated with an
+IP address. The question mark is replaced with the appropriate data. For
+addrname, the left hand side is the IP address. This expects one VARCHAR to
+be returned with the host name that the IP address resolves to.
+.Pp
.El
A generic SQL statement would be something like:
@@ -106,16 +127,15 @@
.Ed
.Sh EXAMPLES
-Example based on the OpenSMTPD FAQ: Building a Mail Server
-The filtering part is excluded in this example.
-The configuration below is for a medium-size mail server which handles
+The configuration below is for a server which can handle
multiple domains with multiple virtual users and is based on several
assumptions. One is that a single system user named vmail is used for all
-virtual users. This user needs to be created:
+virtual users and the vmail gid and uid are 2000.
+This user needs to be created:
.Bd -literal
-# useradd -g =uid -c "Virtual Mail" -d /var/vmail -s /sbin/nologin vmail
+# useradd -u 2000 -g =uid -c "Virtual Mail" -d /var/vmail -s
/sbin/nologin vmail
# mkdir /var/vmail
# chown vmail:vmail /var/vmail
.Ed
@@ -136,6 +156,19 @@
id INTEGER PRIMARY KEY AUTOINCREMENT,
domain VARCHAR(255) NOT NULL
);
+CREATE TABLE userinfo (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ user VARCHAR(255) NOT NULL,
+ uid VARCHAR(255) NOT NULL,
+ gid VARCHAR(255) NOT NULL,
+ home VARCHAR(255) NOT NULL
+);
+CREATE TABLE addrnames (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ ip VARCHAR(255) NOT NULL,
+ host VARCHAR(255) NOT NULL
+);
+
INSERT INTO domains VALUES (1, "example.com");
INSERT INTO domains VALUES (2, "example.net");
INSERT INTO domains VALUES (3, "example.org");
@@ -143,14 +176,19 @@
INSERT INTO virtuals VALUES (1, "ab...@example.com", "b...@example.com");
INSERT INTO virtuals VALUES (2, "postmas...@example.com", "b...@example.com");
INSERT INTO virtuals VALUES (3, "webmas...@example.com", "b...@example.com");
-INSERT INTO virtuals VALUES (4, "b...@example.com", "vmail");
+INSERT INTO virtuals VALUES (4, "b...@example.com", "bob");
INSERT INTO virtuals VALUES (5, "ab...@example.net", "al...@example.net");
INSERT INTO virtuals VALUES (6, "postmas...@example.net", "al...@example.net");
INSERT INTO virtuals VALUES (7, "webmas...@example.net", "al...@example.net");
-INSERT INTO virtuals VALUES (8, "al...@example.net", "vmail");
+INSERT INTO virtuals VALUES (8, "al...@example.net", "alice");
+INSERT INTO userinfo VALUES (1, "bob", "2000", "2000", "/var/vmail");
+INSERT INTO userinfo VALUES (2, "alice", "2000", "2000", "/var/vmail");
+
INSERT INTO credentials VALUES (1, "b...@example.com",
"$2b$08$ANGFKBL.BnDLL0bUl7I6aumTCLRJSQluSQLuueWRG.xceworWrUIu");
INSERT INTO credentials VALUES (2, "al...@example.net",
"$2b$08$AkHdB37kaj2NEoTcISHSYOCEBA5vyW1RcD8H1HG.XX0P/G1KIYwii");
+
+INSERT INTO addrnames VALUES (1, "192.0.2.1", "mail.example.org");
.Ed
.Ic Pa /etc/mail/sqlite.conf
@@ -159,6 +197,8 @@
query_alias SELECT destination FROM virtuals WHERE email=?;
query_credentials SELECT email, password FROM credentials WHERE email=?;
query_domain SELECT domain FROM domains WHERE domain=?;
+query_userinfo SELECT uid, gid, home FROM userinfo WHERE user=?;
+query_addrname SELECT host FROM addrnames WHERE ip=?;
.Ed
.Ic Pa /etc/mail/smtpd.conf
@@ -166,9 +206,14 @@
table domains sqlite:/etc/mail/sqlite.conf
table virtuals sqlite:/etc/mail/sqlite.conf
table credentials sqlite:/etc/mail/sqlite.conf
-listen on egress port 25 tls pki mail.example.com
-listen on egress port 587 tls-require pki mail.example.com auth <credentials>
-accept from any for domain <domains> virtual <virtuals> deliver to mbox
+table userinfo sqlite:/etc/mail/sqlite.conf
+table addrnames sqlite:/etc/mail/sqlite.conf
+
+listen on egress port 25 tls pki mail.example.com hostnames <addrnames>
+listen on egress port 587 tls-require pki mail.example.com auth
<credentials> hostnames <addrnames>
+
+action "action01" mbox userbase <userinfo> virtual <virtuals>
+match from any for domain <domains> action "action01"
.Ed
.Sh FILES
@@ -187,10 +232,8 @@
Documenting the following query options:
.Bd -literal -offset indent -compact
.Ic query_netaddr
-.Ic query_userinfo
.Ic query_source
.Ic query_mailaddr
-.Ic query_addrname
.Ed
.Sh SEE ALSO
