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 From 3277ce8ccca2e2355eb91e3a28aee508da8b1bd5 Mon Sep 17 00:00:00 2001 From: Mischa Peters Date: Sun, 3 Jul 2016 23:08:01 +0200 Subject: Added a lot more context to the sqlite queries and options --- extras/tables/table-sqlite/table-sqlite.5 | 2 ++ 1 file changed, 2 insertions(+) diff --git a/extras/tables/table-sqlite/table-sqlite.5 b/extras/tables/table-sqlite/table-sqlite.5 index 9d1da30..935ec71 100644 --- a/extras/tables/table-sqlite/table-sqlite.5 +++ b/extras/tables/table-sqlite/table-sqlite.5 @@ -139,6 +139,8 @@ query_domain SELECT domain FROM domains WHERE domain=?; 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 587 tls-require pki mx.domain.tld auth +accept from any for domain virtual deliver to mbox .Ed .Sh FILES -- cgit v1.2.3-59-g8ed1b From aa12e9ac0f45244c5d290cd3a0cd483639597fce Mon Sep 17 00:00:00 2001 From: Mischa Peters Date: Mon, 4 Jul 2016 11:15:20 +0200 Subject: Added default config file, more content around example --- extras/tables/table-sqlite/sqlite.conf | 45 +++++++++++++++++++++++++++++++ extras/tables/table-sqlite/table-sqlite.5 | 44 +++++++++++++++++++++++++----- 2 files changed, 82 insertions(+), 7 deletions(-) create mode 100644 extras/tables/table-sqlite/sqlite.conf diff --git a/extras/tables/table-sqlite/sqlite.conf b/extras/tables/table-sqlite/sqlite.conf new file mode 100644 index 0000000..277afc9 --- /dev/null +++ b/extras/tables/table-sqlite/sqlite.conf @@ -0,0 +1,45 @@ +# +# Sample sqlite configuration file +# + +# sqlite example schema +# +#CREATE TABLE virtuals ( +# 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 +#); + +# DB file location +# +dbpath /etc/mail/smtp.sqlite + +# Alias lookup query +# +# rows >= 0 +# fields == 1 (email VARCHAR) +# +query_alias SELECT destination FROM virtuals WHERE email=? + +# Domain lookup query +# +# rows == 1 +# fields == 1 (domain VARCHAR) +# +query_domain SELECT domain FROM domains WHERE domain=?; + +# Credentials lookup query +# +# rows == 1 +# fields == 2 (email VARCHAR, password VARCHAR) +# +query_credentials SELECT email, password FROM credentials WHERE email=?; diff --git a/extras/tables/table-sqlite/table-sqlite.5 b/extras/tables/table-sqlite/table-sqlite.5 index 935ec71..39d7442 100644 --- a/extras/tables/table-sqlite/table-sqlite.5 +++ b/extras/tables/table-sqlite/table-sqlite.5 @@ -61,7 +61,7 @@ The following configuration options are available: .Ic dbpath .Ar file .Xc -This is the path to where the DB is located with the dbname. +This is the path to where the DB file is located. For example: .Bd -literal -offset indent dbpath /etc/mail/smtp.sqlite @@ -106,11 +106,23 @@ query_ SELECT value FROM table WHERE key=?; .Ed .Sh EXAMPLES -Those provided here a known to work. +Example based on the OpenSMTPD FAQ: Building a Mail Server +The filtering part is excluded in this example. -.Ic Pa sqlite3 schema +The configuration below is for a medium-size mail server which handles +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: + +.Bd -literal +# useradd -g =uid -c "Virtual Mail" -d /var/vmail -s /sbin/nologin vmail +# mkdir /var/vmail +# chown vmail:vmail /var/vmail +.Ed + +.Ic Pa sqlite schema .Bd -literal -compact -CREATE TABLE aliases ( +CREATE TABLE virtuals ( id INTEGER PRIMARY KEY AUTOINCREMENT, email VARCHAR(255) NOT NULL, destination VARCHAR(255) NOT NULL @@ -124,12 +136,27 @@ CREATE TABLE domains ( id INTEGER PRIMARY KEY AUTOINCREMENT, domain 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"); + +insert into virtuals values (1, "abuse@example.com", "bob@example.com"); +insert into virtuals values (2, "postmaster@example.com", "bob@example.com"); +insert into virtuals values (3, "webmaster@example.com", "bob@example.com"); +insert into virtuals values (4, "bob@example.com", "vmail"); +insert into virtuals values (5, "abuse@example.net", "alice@example.net"); +insert into virtuals values (6, "postmaster@example.net", "alice@example.net"); +insert into virtuals values (7, "webmaster@example.net", "alice@example.net"); +insert into virtuals values (8, "alice@example.net", "vmail"); + +insert into credentials values (1, "bob@example.com", "$2b$08$ANGFKBL.BnDLL0bUl7I6aumTCLRJSQluSQLuueWRG.xceworWrUIu"); +insert into credentials values (2, "alice@example.net", "$2b$08$AkHdB37kaj2NEoTcISHSYOCEBA5vyW1RcD8H1HG.XX0P/G1KIYwii"); .Ed .Ic Pa /etc/mail/sqlite.conf .Bd -literal -compact dbpath /etc/mail/smtp.sqlite -query_alias SELECT destination FROM aliases WHERE email=?; +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=?; .Ed @@ -139,14 +166,15 @@ query_domain SELECT domain FROM domains WHERE domain=?; 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 587 tls-require pki mx.domain.tld auth +listen on egress port 25 tls pki mail.example.com +listen on egress port 587 tls-require pki mail.example.com auth accept from any for domain virtual deliver to mbox .Ed .Sh FILES .Bl -tag -width "/etc/mail/sqlite.conf" -compact .It Pa /etc/mail/sqlite.conf -Suggested +Default .Xr table-sqlite 8 configuration file. .It Pa /etc/mail/smtp.sqlite @@ -155,6 +183,8 @@ Suggested database file. .El +.Sh TODO + .Sh SEE ALSO .Xr smtpd.conf 5 , .Xr smtpctl 8 , -- cgit v1.2.3-59-g8ed1b From 068310450e437cb66e20ca03fe2c19e4b31dace4 Mon Sep 17 00:00:00 2001 From: Mischa Peters Date: Mon, 4 Jul 2016 11:21:22 +0200 Subject: Changed Mdocdate --- extras/tables/table-sqlite/table-sqlite.5 | 10 +++++++++- 1 file changed, 9 insertions(+), 1 deletion(-) diff --git a/extras/tables/table-sqlite/table-sqlite.5 b/extras/tables/table-sqlite/table-sqlite.5 index 39d7442..69dc3c8 100644 --- a/extras/tables/table-sqlite/table-sqlite.5 +++ b/extras/tables/table-sqlite/table-sqlite.5 @@ -14,7 +14,7 @@ .\" OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. .\" .\" -.Dd $Mdocdate: June 17 2014 $ +.Dd $Mdocdate: July 4 2016 $ .Dt TABLE_SQLITE 5 .Os .Sh NAME @@ -184,6 +184,14 @@ database file. .El .Sh TODO +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 .Xr smtpd.conf 5 , -- cgit v1.2.3-59-g8ed1b From 1c335aac4a5ddb063cf913977d2833c92a546fc0 Mon Sep 17 00:00:00 2001 From: Mischa Peters Date: Mon, 4 Jul 2016 19:11:45 +0200 Subject: changed SQL statements --- extras/tables/table-mysql/table-mysql.5 | 233 ++++++++++++++++++++ extras/tables/table-sqlite/table-sqlite.5 | 30 +-- .../table-sqlite/table_sqlite.sublime-workspace | 234 +++++++++++++++++++++ 3 files changed, 482 insertions(+), 15 deletions(-) create mode 100644 extras/tables/table-mysql/table-mysql.5 create mode 100644 extras/tables/table-sqlite/table_sqlite.sublime-workspace diff --git a/extras/tables/table-mysql/table-mysql.5 b/extras/tables/table-mysql/table-mysql.5 new file mode 100644 index 0000000..ee1e39c --- /dev/null +++ b/extras/tables/table-mysql/table-mysql.5 @@ -0,0 +1,233 @@ +.\" +.\" Copyright (c) 2013 Eric Faurot +.\" +.\" Permission to use, copy, modify, and distribute this software for any +.\" purpose with or without fee is hereby granted, provided that the above +.\" copyright notice and this permission notice appear in all copies. +.\" +.\" THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES +.\" WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF +.\" MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR +.\" ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES +.\" WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN +.\" ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF +.\" OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. +.\" +.Dd $Mdocdate: July 4 2016 $ +.Dt MYSQL_SQLITE 5 +.Os +.Sh NAME +.Nm table_mysql +.Nd format description for smtpd MySQL or MariaDB tables +.Sh DESCRIPTION +This manual page documents the file format of MySQL or MariaDB tables used +by the +.Xr smtpd 8 +mail daemon. +.Pp +The format described here applies to tables as defined in +.Xr smtpd.conf 5 . +.Sh MYSQL TABLE +A mysql table allows the storing of usernames, passwords, aliases, and domains +in a format that is shareable across various machines that support +.Xr mysql 1 . +.Pp +The table is used by +.Xr smtpd 8 +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 MySQL table consists of one or more +.Xr mysql 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 +.Xr encrypt 1 +utility or +.Xr smtpctl 8 +encrypt command. + +.Sh MYSQL TABLE CONFIG FILE + +The following configuration options are available: +.Pp +.Bl -tag -width Ds +.It Xo +.Ic host +.Ar hostname +.Xc +This is the host running MySQL or MariaDB. +For example: +.Bd -literal -offset indent +host db.example.com +.Ed +.Pp + +.It Xo +.Ic username +.Ar username +.Xc +The username required to talk to the MySQL or MariaDB database. +For example: +.Bd -literal -offset indent +username maildba +.Ed +.Pp + +.It Xo +.Ic password +.Ar password +.Xc +The password required to talk to the MySQL or MariaDB database. +For example: +.Bd -literal -offset indent +password OpenSMTPDRules! +.Ed +.Pp + +.It Xo +.Ic database +.Ar databse +.Xc +The name of the MySQL or MariaDB database. +For example: +.Bd -literal -offset indent +databse opensmtpdb +.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 +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 +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: + +.Bd -literal +# useradd -g =uid -c "Virtual Mail" -d /var/vmail -s /sbin/nologin vmail +# mkdir /var/vmail +# chown vmail:vmail /var/vmail +.Ed + +.Ic Pa sqlite schema +.Bd -literal -compact +CREATE TABLE virtuals ( + 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 +); +insert into domains values (1, "example.com"); +insert into domains values (2, "example.net"); +insert into domains values (3, "example.org"); + +insert into virtuals values (1, "abuse@example.com", "bob@example.com"); +insert into virtuals values (2, "postmaster@example.com", "bob@example.com"); +insert into virtuals values (3, "webmaster@example.com", "bob@example.com"); +insert into virtuals values (4, "bob@example.com", "vmail"); +insert into virtuals values (5, "abuse@example.net", "alice@example.net"); +insert into virtuals values (6, "postmaster@example.net", "alice@example.net"); +insert into virtuals values (7, "webmaster@example.net", "alice@example.net"); +insert into virtuals values (8, "alice@example.net", "vmail"); + +insert into credentials values (1, "bob@example.com", "$2b$08$ANGFKBL.BnDLL0bUl7I6aumTCLRJSQluSQLuueWRG.xceworWrUIu"); +insert into credentials values (2, "alice@example.net", "$2b$08$AkHdB37kaj2NEoTcISHSYOCEBA5vyW1RcD8H1HG.XX0P/G1KIYwii"); +.Ed + +.Ic Pa /etc/mail/sqlite.conf +.Bd -literal -compact +dbpath /etc/mail/smtp.sqlite +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=?; +.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 +listen on egress port 25 tls pki mail.example.com +listen on egress port 587 tls-require pki mail.example.com auth +accept from any for domain virtual deliver to mbox +.Ed + +.Sh FILES +.Bl -tag -width "/etc/mail/sqlite.conf" -compact +.It Pa /etc/mail/sqlite.conf +Default +.Xr table-sqlite 8 +configuration file. +.It Pa /etc/mail/smtp.sqlite +Suggested +.Xr sqlite3 1 +database file. +.El + +.Sh TODO +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 +.Xr smtpd.conf 5 , +.Xr smtpctl 8 , +.Xr smtpd 8 , +.Xr encrypt 1 , +.Xr crypt 3 diff --git a/extras/tables/table-sqlite/table-sqlite.5 b/extras/tables/table-sqlite/table-sqlite.5 index 69dc3c8..d0963a6 100644 --- a/extras/tables/table-sqlite/table-sqlite.5 +++ b/extras/tables/table-sqlite/table-sqlite.5 @@ -136,21 +136,21 @@ CREATE TABLE domains ( id INTEGER PRIMARY KEY AUTOINCREMENT, domain 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"); - -insert into virtuals values (1, "abuse@example.com", "bob@example.com"); -insert into virtuals values (2, "postmaster@example.com", "bob@example.com"); -insert into virtuals values (3, "webmaster@example.com", "bob@example.com"); -insert into virtuals values (4, "bob@example.com", "vmail"); -insert into virtuals values (5, "abuse@example.net", "alice@example.net"); -insert into virtuals values (6, "postmaster@example.net", "alice@example.net"); -insert into virtuals values (7, "webmaster@example.net", "alice@example.net"); -insert into virtuals values (8, "alice@example.net", "vmail"); - -insert into credentials values (1, "bob@example.com", "$2b$08$ANGFKBL.BnDLL0bUl7I6aumTCLRJSQluSQLuueWRG.xceworWrUIu"); -insert into credentials values (2, "alice@example.net", "$2b$08$AkHdB37kaj2NEoTcISHSYOCEBA5vyW1RcD8H1HG.XX0P/G1KIYwii"); +INSERT INTO domains VALUES (1, "example.com"); +INSERT INTO domains VALUES (2, "example.net"); +INSERT INTO domains VALUES (3, "example.org"); + +INSERT INTO virtuals VALUES (1, "abuse@example.com", "bob@example.com"); +INSERT INTO virtuals VALUES (2, "postmaster@example.com", "bob@example.com"); +INSERT INTO virtuals VALUES (3, "webmaster@example.com", "bob@example.com"); +INSERT INTO virtuals VALUES (4, "bob@example.com", "vmail"); +INSERT INTO virtuals VALUES (5, "abuse@example.net", "alice@example.net"); +INSERT INTO virtuals VALUES (6, "postmaster@example.net", "alice@example.net"); +INSERT INTO virtuals VALUES (7, "webmaster@example.net", "alice@example.net"); +INSERT INTO virtuals VALUES (8, "alice@example.net", "vmail"); + +INSERT INTO credentials VALUES (1, "bob@example.com", "$2b$08$ANGFKBL.BnDLL0bUl7I6aumTCLRJSQluSQLuueWRG.xceworWrUIu"); +INSERT INTO credentials VALUES (2, "alice@example.net", "$2b$08$AkHdB37kaj2NEoTcISHSYOCEBA5vyW1RcD8H1HG.XX0P/G1KIYwii"); .Ed .Ic Pa /etc/mail/sqlite.conf diff --git a/extras/tables/table-sqlite/table_sqlite.sublime-workspace b/extras/tables/table-sqlite/table_sqlite.sublime-workspace new file mode 100644 index 0000000..ebe250d --- /dev/null +++ b/extras/tables/table-sqlite/table_sqlite.sublime-workspace @@ -0,0 +1,234 @@ +{ + "auto_complete": + { + "selected_items": + [ + ] + }, + "buffers": + [ + ], + "build_system": "", + "build_system_choices": + [ + ], + "build_varint": "", + "command_palette": + { + "height": 47.0, + "last_filter": "uniq", + "selected_items": + [ + [ + "uniq", + "Permute Lines: Unique" + ], + [ + "sort", + "Sort Lines" + ], + [ + "lower", + "Convert Case: Lower Case" + ], + [ + "Snippet: ", + "Snippet: #!/usr/bin/env" + ], + [ + "gist", + "Gist: Add File to Gist" + ], + [ + "packag", + "Package Control: Install Package" + ], + [ + "mark", + "Markdown Preview: Preview in Browser" + ], + [ + "install", + "Package Control: Install Package" + ] + ], + "width": 485.0 + }, + "console": + { + "height": 126.0, + "history": + [ + "import urllib.request,os,hashlib; h = '2915d1851351e5ee549c20394736b442' + '8bc59f460fa1548d1514676163dafc88'; pf = 'Package Control.sublime-package'; ipp = sublime.installed_packages_path(); urllib.request.install_opener( urllib.request.build_opener( urllib.request.ProxyHandler()) ); by = urllib.request.urlopen( 'http://packagecontrol.io/' + pf.replace(' ', '%20')).read(); dh = hashlib.sha256(by).hexdigest(); print('Error validating download (got %s instead of %s), please try manual install' % (dh, h)) if dh != h else open(os.path.join( ipp, pf), 'wb' ).write(by)" + ] + }, + "distraction_free": + { + "menu_visible": true, + "show_minimap": false, + "show_open_files": false, + "show_tabs": false, + "side_bar_visible": false, + "status_bar_visible": false + }, + "file_history": + [ + "/Users/mischa/Development/OpenSMTPD-extras/extras/tables/table-sqlite/table_sqlite.c", + "/Users/mischa/Development/OpenSMTPD-extras/extras/tables/table-sqlite/table-sqlite.5", + "/Users/mischa/Development/notes/ansible.md", + "/Users/mischa/Documents/High5!/Customers/nl.kwaadraad/kwadraadsetup/apache/conf/httpd.conf", + "/Users/mischa/Downloads/undeadly/cgi.c", + "/Users/mischa/Documents/relayd-httpd.txt", + "/Users/mischa/Downloads/myadmin", + "/Users/mischa/Documents/High5!/Colo/BSD/poudriere.txt", + "/Users/mischa/LightCyber/Magna/Demo-Standalone-UI-v3.1/Magna.exe.config", + "/Users/mischa/LightCyber/Magna/StandaloneMagnaUI-echo_rc3b-628/Magna.exe.config", + "/Users/mischa/Desktop/traffic_2016-06-15_12-18-55.txt", + "/Users/mischa/Desktop/kwaadraad-accounts.txt", + "/Users/mischa/LightCyber/Customers/ru.irz/traffic_2016-06-07_09_43_24.txt", + "/Users/mischa/Desktop/traffic_2016-06-07_09_43_24.txt", + "/Users/mischa/LightCyber/Customers/nl.gelre_ziekenhuis/Screen Shot 2016-04-14 at 11.22.56.png", + "/Users/mischa/Desktop/zpool.txt", + "/Users/mischa/Desktop/boot-big.txt", + "/Users/mischa/Documents/TXT & Markdown/new-jail-order.txt", + "/Users/mischa/Desktop/iohyve-move.txt", + "/Users/mischa/Downloads/openup", + "/Users/mischa/Downloads/TODO/openbsd-remote-upgrade.txt", + "/Users/mischa/Desktop/zfs-fix.txt", + "/Users/mischa/Desktop/Allianz Demo", + "/Users/mischa/Library/Application Support/Sublime Text 3/Packages/Gist/Gist.sublime-settings", + "/Users/mischa/Library/Application Support/Sublime Text 3/Packages/User/Preferences.sublime-settings", + "/Users/mischa/Documents/TXT & Markdown/freebsd-soekris-vdsl.md" + ], + "find": + { + "height": 23.0 + }, + "find_in_files": + { + "height": 93.0, + "where_history": + [ + "" + ] + }, + "find_state": + { + "case_sensitive": true, + "find_history": + [ + "SQ", + "sqlite", + "CustomLog", + "customLog", + ".crt", + "/bin", + "nc" + ], + "highlight": true, + "in_selection": false, + "preserve_case": false, + "regex": false, + "replace_history": + [ + ".key", + "/c/bin", + "ncat" + ], + "reverse": false, + "show_context": true, + "use_buffer2": true, + "whole_word": false, + "wrap": true + }, + "groups": + [ + { + "sheets": + [ + ] + } + ], + "incremental_find": + { + "height": 23.0 + }, + "input": + { + "height": 31.0 + }, + "layout": + { + "cells": + [ + [ + 0, + 0, + 1, + 1 + ] + ], + "cols": + [ + 0.0, + 1.0 + ], + "rows": + [ + 0.0, + 1.0 + ] + }, + "menu_visible": true, + "output.find_results": + { + "height": 0.0 + }, + "pinned_build_system": "Packages/Markdown Preview/Markdown.sublime-build", + "project": "table_sqlite.sublime-project", + "replace": + { + "height": 42.0 + }, + "save_all_on_build": true, + "select_file": + { + "height": 0.0, + "last_filter": "", + "selected_items": + [ + ], + "width": 0.0 + }, + "select_project": + { + "height": 0.0, + "last_filter": "", + "selected_items": + [ + ], + "width": 0.0 + }, + "select_symbol": + { + "height": 0.0, + "last_filter": "", + "selected_items": + [ + ], + "width": 0.0 + }, + "selected_group": 0, + "settings": + { + }, + "show_minimap": true, + "show_open_files": true, + "show_tabs": true, + "side_bar_visible": true, + "side_bar_width": 194.0, + "status_bar_visible": true, + "template_settings": + { + } +} -- cgit v1.2.3-59-g8ed1b From 3f12823dd8dbfdfedab9f6aad1178f048b77dd1f Mon Sep 17 00:00:00 2001 From: Mischa Peters Date: Mon, 4 Jul 2016 19:26:57 +0200 Subject: Added table-mysql manual and default config file --- extras/tables/table-mysql/mysql.conf | 48 +++++++++++++++++ extras/tables/table-mysql/table-mysql.5 | 92 +++++++++++++++++++-------------- 2 files changed, 100 insertions(+), 40 deletions(-) create mode 100644 extras/tables/table-mysql/mysql.conf diff --git a/extras/tables/table-mysql/mysql.conf b/extras/tables/table-mysql/mysql.conf new file mode 100644 index 0000000..8c24359 --- /dev/null +++ b/extras/tables/table-mysql/mysql.conf @@ -0,0 +1,48 @@ +# +# Sample MySQL or MariaDB configuration file +# + +# MySQL or MariaDB example schema +# +#CREATE TABLE domains ( +# id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, +# domain VARCHAR(255) NOT NULL DEFAULT '' +#); +#CREATE TABLE virtuals ( +# id INTEGER AUTO_INCREMENT PRIMARY KEY, +# email VARCHAR(255) NOT NULL DEFAULT '', +# destination VARCHAR(255) NOT NULL DEFAULT '' +#); +#CREATE TABLE credentials ( +# id INTEGER AUTO_INCREMENT PRIMARY KEY, +# email VARCHAR(255) NOT NULL DEFAULT '', +# password VARCHAR(255) NOT NULL DEFAULT '' +#); + +# Database definition +# +host localhost +username username +password password +database databasename + +# Alias lookup query +# +# rows >= 0 +# fields == 1 (email VARCHAR) +# +query_alias SELECT destination FROM virtuals WHERE email=? + +# Domain lookup query +# +# rows == 1 +# fields == 1 (domain VARCHAR) +# +query_domain SELECT domain FROM domains WHERE domain=?; + +# Credentials lookup query +# +# rows == 1 +# fields == 2 (email VARCHAR, password VARCHAR) +# +query_credentials SELECT email, password FROM credentials WHERE email=?; diff --git a/extras/tables/table-mysql/table-mysql.5 b/extras/tables/table-mysql/table-mysql.5 index ee1e39c..af94556 100644 --- a/extras/tables/table-mysql/table-mysql.5 +++ b/extras/tables/table-mysql/table-mysql.5 @@ -14,7 +14,7 @@ .\" OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. .\" .Dd $Mdocdate: July 4 2016 $ -.Dt MYSQL_SQLITE 5 +.Dt TABLE_MYSQL 5 .Os .Sh NAME .Nm table_mysql @@ -52,7 +52,6 @@ utility or encrypt command. .Sh MYSQL TABLE CONFIG FILE - The following configuration options are available: .Pp .Bl -tag -width Ds @@ -138,6 +137,7 @@ query_ SELECT value FROM table WHERE key=?; .Ed .Sh EXAMPLES +.Ss GENERIC EXAMPLE Example based on the OpenSMTPD FAQ: Building a Mail Server The filtering part is excluded in this example. @@ -152,42 +152,45 @@ virtual users. This user needs to be created: # chown vmail:vmail /var/vmail .Ed -.Ic Pa sqlite schema +.Ic Pa MySQL schema .Bd -literal -compact +CREATE TABLE domains ( + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + domain VARCHAR(255) NOT NULL DEFAULT '' +); CREATE TABLE virtuals ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - email VARCHAR(255) NOT NULL, - destination VARCHAR(255) NOT NULL + id INTEGER AUTO_INCREMENT PRIMARY KEY, + email VARCHAR(255) NOT NULL DEFAULT '', + destination VARCHAR(255) NOT NULL DEFAULT '' ); CREATE TABLE credentials ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - email VARCHAR(255) NOT NULL, - password VARCHAR(255) NOT NULL + id INTEGER AUTO_INCREMENT PRIMARY KEY, + email VARCHAR(255) NOT NULL DEFAULT '', + password VARCHAR(255) NOT NULL DEFAULT '' ); -CREATE TABLE domains ( - id INTEGER PRIMARY KEY AUTOINCREMENT, - domain 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"); - -insert into virtuals values (1, "abuse@example.com", "bob@example.com"); -insert into virtuals values (2, "postmaster@example.com", "bob@example.com"); -insert into virtuals values (3, "webmaster@example.com", "bob@example.com"); -insert into virtuals values (4, "bob@example.com", "vmail"); -insert into virtuals values (5, "abuse@example.net", "alice@example.net"); -insert into virtuals values (6, "postmaster@example.net", "alice@example.net"); -insert into virtuals values (7, "webmaster@example.net", "alice@example.net"); -insert into virtuals values (8, "alice@example.net", "vmail"); - -insert into credentials values (1, "bob@example.com", "$2b$08$ANGFKBL.BnDLL0bUl7I6aumTCLRJSQluSQLuueWRG.xceworWrUIu"); -insert into credentials values (2, "alice@example.net", "$2b$08$AkHdB37kaj2NEoTcISHSYOCEBA5vyW1RcD8H1HG.XX0P/G1KIYwii"); +INSERT INTO domains VALUES (1, "example.com"); +INSERT INTO domains VALUES (2, "example.net"); +INSERT INTO domains VALUES (3, "example.org"); + +INSERT INTO virtuals VALUES (1, "abuse@example.com", "bob@example.com"); +INSERT INTO virtuals VALUES (2, "postmaster@example.com", "bob@example.com"); +INSERT INTO virtuals VALUES (3, "webmaster@example.com", "bob@example.com"); +INSERT INTO virtuals VALUES (4, "bob@example.com", "vmail"); +INSERT INTO virtuals VALUES (5, "abuse@example.net", "alice@example.net"); +INSERT INTO virtuals VALUES (6, "postmaster@example.net", "alice@example.net"); +INSERT INTO virtuals VALUES (7, "webmaster@example.net", "alice@example.net"); +INSERT INTO virtuals VALUES (8, "alice@example.net", "vmail"); + +INSERT INTO credentials VALUES (1, "bob@example.com", "$2b$08$ANGFKBL.BnDLL0bUl7I6aumTCLRJSQluSQLuueWRG.xceworWrUIu"); +INSERT INTO credentials VALUES (2, "alice@example.net", "$2b$08$AkHdB37kaj2NEoTcISHSYOCEBA5vyW1RcD8H1HG.XX0P/G1KIYwii"); .Ed -.Ic Pa /etc/mail/sqlite.conf +.Ic Pa /etc/mail/mysql.conf .Bd -literal -compact -dbpath /etc/mail/smtp.sqlite +host db.example.com +username maildba +password OpenSMTPDRules! +database opensmtpdb 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=?; @@ -195,24 +198,33 @@ query_domain SELECT domain FROM domains WHERE domain=?; .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 +table domains mysql:/etc/mail/mysql.conf +table virtuals mysql:/etc/mail/mysql.conf +table credentials mysql:/etc/mail/mysql.conf listen on egress port 25 tls pki mail.example.com listen on egress port 587 tls-require pki mail.example.com auth accept from any for domain virtual deliver to mbox .Ed +.Ss MOVING FROM POSTFIX (& POSTFIXADMIN) +.Ic Pa /etc/mail/mysql.conf +.Bd -literal -compact +host db.example.com +username postfix +password PostfixOutOpenSMTPDin +database postfix +query_alias SELECT destination FROM alias WHERE email=?; +query_credentials SELECT username, password FROM mailbox WHERE username=?; +query_domain SELECT domain FROM domain WHERE domain=?; +.Ed +The rest of the config remains the same. + .Sh FILES -.Bl -tag -width "/etc/mail/sqlite.conf" -compact -.It Pa /etc/mail/sqlite.conf +.Bl -tag -width "/etc/mail/mysql.conf" -compact +.It Pa /etc/mail/mysql.conf Default -.Xr table-sqlite 8 +.Xr table-mysql 8 configuration file. -.It Pa /etc/mail/smtp.sqlite -Suggested -.Xr sqlite3 1 -database file. .El .Sh TODO -- cgit v1.2.3-59-g8ed1b