diff options
author | Mischa Peters <mpeters@high5.nl> | 2016-07-04 19:11:45 +0200 |
---|---|---|
committer | Mischa Peters <mpeters@high5.nl> | 2016-07-04 19:11:45 +0200 |
commit | 1c335aac4a5ddb063cf913977d2833c92a546fc0 (patch) | |
tree | 4c4821a4fe306baf251c386ff60cedf86bc29f1c | |
parent | Changed Mdocdate (diff) | |
download | OpenSMTPD-extras-1c335aac4a5ddb063cf913977d2833c92a546fc0.tar.xz OpenSMTPD-extras-1c335aac4a5ddb063cf913977d2833c92a546fc0.zip |
changed SQL statements
-rw-r--r-- | extras/tables/table-mysql/table-mysql.5 | 233 | ||||
-rw-r--r-- | extras/tables/table-sqlite/table-sqlite.5 | 30 | ||||
-rw-r--r-- | extras/tables/table-sqlite/table_sqlite.sublime-workspace | 234 |
3 files changed, 482 insertions, 15 deletions
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 <eric@openbsd.org> +.\" +.\" 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 <credentials> +accept from any for domain <domains> virtual <virtuals> 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": + { + } +} |