From 976a2e5704fb2b33c79a617a0994f1c5a72e7300 Mon Sep 17 00:00:00 2001 From: Mischa Peters Date: Sun, 3 Jul 2016 23:06:22 +0200 Subject: Added a lot more context to the sqlite queries and options --- extras/tables/table-sqlite/table-sqlite.5 | 142 +++++++++++++++++++++++------- 1 file changed, 112 insertions(+), 30 deletions(-) diff --git a/extras/tables/table-sqlite/table-sqlite.5 b/extras/tables/table-sqlite/table-sqlite.5 index a2d8c21..9d1da30 100644 --- a/extras/tables/table-sqlite/table-sqlite.5 +++ b/extras/tables/table-sqlite/table-sqlite.5 @@ -28,8 +28,10 @@ mail daemon. The format described here applies to tables as defined in .Xr smtpd.conf 5 . .Sh SQLITE TABLE -A SQLite table allows the storing of usernames, passwords, aliases, and domains -in a format that is shareable across various machines that support SQLite3 +A sqlite table allows the storing of usernames, passwords, aliases, and domains +in a format that is shareable across various machines that support +.Xr sqlite3 1 +(SQLite version 3). .Pp The table is used by .Xr smtpd 8 @@ -37,43 +39,123 @@ when authenticating a user, when user information such as user-id and/or home directory is required for a delivery, when a domain lookup may be required, and/or when looking for an alias. .Pp -A SQLite table consists of one or more SQLite3 Databases with one or more tables. +A sqlite table consists of one or more +.Xr sqlite3 1 +databases with one or more tables. .Pp If the table is used for authentication, the password should be encrypted using the .Xr crypt 3 -function. -Such passwords can be generated using the +function. Such passwords can be generated using the .Xr encrypt 1 utility or .Xr smtpctl 8 encrypt command. + .Sh SQLITE TABLE CONFIG FILE -.Cd dbpath -.Dl This is the path to where the DB is located with the dbname. -.Cd query_alias -.Dl This is used to provide a query to look up aliases. All the question marks are replaced -.Dl with the appropriate data, in this case it would be the left hand side of the SMTP address. -.Dl This expects one varchar to be returned with the user name the alias resolves to. -.Cd query_domain -.Dl This is used to provide a query for a domain query call. All the question marks are replaced -.Dl with the appropriate data, in this case it would be the right hand side of the SMTP address. -.Dl This expects one varchar to be returned with a matching domain name. -.Cd query_userinfo -.Dl This is used to provide a query for looking up user information. -.Dl All the question marks are replaced with the appropriate data, in this case it -.Dl would be the left hand side of the SMTP address. -.Dl This expects three fields to be returned an int containing a UID, an int containing a GID -.Dl and a varchar containing the home directory for the user. -.Cd query_credentials -.Dl This is used to provide a query for looking up credentials. All the question marks are replaced -.Dl with the appropriate data, in this case it would be the left hand side of the SMTP address. -.Dl the query expects that there are two varchars returned one with a -.Dl user name one with a password in -.Xr encrypt 1 -.Dl format. + +The following configuration options are available: +.Pp +.Bl -tag -width Ds +.It Xo +.Ic dbpath +.Ar file +.Xc +This is the path to where the DB is located with the dbname. +For example: +.Bd -literal -offset indent +dbpath /etc/mail/smtp.sqlite +.Ed +.Pp + +.It Xo +.Ic query_alias +.Ar SQL statement +.Xc +This is used to provide a query to look up aliases. The question mark +is replaced with the appropriate data. For alias it is the left hand side of +the SMTP address. This expects one VARCHAR to be returned with the user name +the alias resolves to. +.Pp + +.It Xo +.Ic query_credentials +.Ar SQL statement +.Xc +This is used to provide a query for looking up user credentials. The question +mark is replaced with the appropriate data. For credentials it is the left +hand side of the SMTP address. The query expects that there are two VARCHARS +returned, one with a user name and one with a password in +.Xr crypt 3 +format. +.Pp + +.It Xo +.Ic query_domain +.Ar SQL statement +.Xc +This is used to provide a query for looking up a domain. The question mark +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. +.El + +A generic SQL statement would be something like: +.Bd -literal -offset indent +query_ SELECT value FROM table WHERE key=?; +.Ed + +.Sh EXAMPLES +Those provided here a known to work. + +.Ic Pa sqlite3 schema +.Bd -literal -compact +CREATE TABLE aliases ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + email VARCHAR(255) NOT NULL, + destination VARCHAR(255) NOT NULL +); +CREATE TABLE credentials ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + email VARCHAR(255) NOT NULL, + password VARCHAR(255) NOT NULL +); +CREATE TABLE domains ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + domain VARCHAR(255) NOT NULL +); +.Ed + +.Ic Pa /etc/mail/sqlite.conf +.Bd -literal -compact +dbpath /etc/mail/smtp.sqlite +query_alias SELECT destination FROM aliases WHERE email=?; +query_credentials SELECT email, password FROM credentials WHERE email=?; +query_domain SELECT domain FROM domains WHERE domain=?; +.Ed + +.Ic Pa /etc/mail/smtpd.conf +.Bd -literal -compact +table domains sqlite:/etc/mail/sqlite.conf +table virtuals sqlite:/etc/mail/sqlite.conf +table credentials sqlite:/etc/mail/sqlite.conf +.Ed + +.Sh FILES +.Bl -tag -width "/etc/mail/sqlite.conf" -compact +.It Pa /etc/mail/sqlite.conf +Suggested +.Xr table-sqlite 8 +configuration file. +.It Pa /etc/mail/smtp.sqlite +Suggested +.Xr sqlite3 1 +database file. +.El + .Sh SEE ALSO -.Xr encrypt 1 , .Xr smtpd.conf 5 , .Xr smtpctl 8 , -.Xr smtpd 8 +.Xr smtpd 8 , +.Xr encrypt 1 , +.Xr crypt 3 -- cgit v1.2.3-59-g8ed1b