OpenBSD Journal

OpenSMTPD Meets SQLite

Contributed by tbert on from the when-can-we-use-excel-for-this dept.

Gilles Chehade(gilles@) gives us a preview of an upcoming OpenSMTPD feature:

During the r2k12 hackathon in Paris, Marc Espie committed SQLite to OpenBSD's base system.

This has the side effect that OpenSMTPD can start using it and while we agreed that we did not want it as a strong dependency, the backends API allows us to make it a soft dependency that can be removed without breaking the daemon if someone *really* does not want SQLite linked.

Today I decided to give it a try and implement a SQLite backend to the map API. About ten minutes later (yes, really ten minutes !), I had a working prototype that was suboptimal and that didn't make use of SQL capabilities.

An hour later, I have a SQLite backend that will use multiple tables with different structures and that can be used to lookup aliases, virtual domains and credentials for authenticated relaying.

gilles@ walks you through it below the fold.

First you create a database with the following schema.sql:

-- 
-- TABLES REQUIRED BY THE MAPS BACKEND
-- 

CREATE TABLE IF NOT EXISTS aliases (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS secrets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
relay VARCHAR(255) UNIQUE NOT NULL,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS virtual (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
);

Then you declare your map with source "sqlite":

map "aliases" { source sqlite "/etc/mail/sqlite.db" }
map "virtmap" { source sqlite "/etc/mail/sqlite.db" }
map "secrets" { source sqlite "/etc/mail/sqlite.db" }

accept for local alias aliases deliver to mbox
accept for virtual virtmap deliver to maildir
accept for all relay via "mail.example.com" tls auth "secrets"

And voila ! The lookups are performed at runtime, as usual, which means that you can add virtual domains, aliases or new credentials through SQL queries to the sqlite.db database.

The diff will only apply to OpenSMTPD for OpenBSD -current, it will not work as is on -portable but it should be committed pretty soon.

Give it a spin on your setup if this is something you might find useful. More systems means better test coverage, and better code at the other end!

(Comments are closed)


Comments
  1. By chris cappuccio (chriscappuccio) chris@nmedia.net on www.nmedia.net/chris/

    OpenBSD again fails to web scale! Where's the Mongo DB version? WTF? Web 3.0 forever!

    Comments
    1. By Gilles Chehade (gilles) on https://www.poolp.org/~gilles/

      > OpenBSD again fails to web scale! Where's the Mongo DB version? WTF? Web 3.0 forever!

      You have the tools to write a Mongo DB backend now ;-p

    2. By tbert (tbert) on

      > OpenBSD again fails to web scale! Where's the Mongo DB version? WTF? Web 3.0 forever!

      Only /dev/null is web scale.

      Comments
      1. By Miod Vallat (miod) on

        > > OpenBSD again fails to web scale! Where's the Mongo DB version? WTF? Web 3.0 forever!
        >
        > Only /dev/null is web scale.

        But it's full!

        Comments
        1. By Otto Moerbeek (otto) on http://www.drijf.net

          > > > OpenBSD again fails to web scale! Where's the Mongo DB version? WTF? Web 3.0 forever!
          > >
          > > Only /dev/null is web scale.
          >
          > But it's full!

          Never if you buy the AutoBucket feature!

  2. By John Lloyd (bitminer) j@bitminer.ca on

    name VARCHAR(255) NOT NULL

    You are a 2**8-1 bigot -- why only 255 bytes?

    A little more seriously, the attributes (column names) are not exactly unique. There is

    name, an alias for email forwarded to, oh, look, an address.

    Why "name" for an e-mail address. Why not call it incoming_email? That's what it is, no?

    OK, so what I'm complaining about is very typical of simple data models. They are so simple people don't bother to get them right. The attribute called "name" has very little to do with the actual concept of a human name. It is an e-mail address. Please call it something related to that.


    --J





    Comments
    1. By Gilles Chehade (gilles) on https://www.poolp.org/~gilles/

      > name VARCHAR(255) NOT NULL
      >
      > You are a 2**8-1 bigot -- why only 255 bytes?
      >
      > A little more seriously, the attributes (column names) are not exactly unique. There is
      >
      > name, an alias for email forwarded to, oh, look, an address.
      >
      > Why "name" for an e-mail address. Why not call it incoming_email? That's what it is, no?
      >
      > OK, so what I'm complaining about is very typical of simple data models. They are so simple people don't bother to get them right. The attribute called "name" has very little to do with the actual concept of a human name. It is an e-mail address. Please call it something related to that.
      >

      ...

      you do realize that you are complaining about an experiment, not even something that was committed ?

      I tweaked the tables until I got the C code as simple as possible, if I had to worry about getting the most accurate name or the most accurate type (which is not enforced by SQLite anyways) it would have taken much more time to ... experiment.

      What you see is not what will be committed so please be patient and bear with a design that's not final from the start.

      Gilles

      Comments
      1. By John Lloyd (bitminer) on


        > you do realize that you are complaining about an experiment, not even something that was committed ?

        That is good news. I would rather influence things early rather than later. Assuming, of course, you are willing to be influenced.

        >
        > I tweaked the tables until I got the C code as simple as possible, if I had to worry about getting the most accurate name or the most accurate type (which is not enforced by SQLite anyways) it would have taken much more time to ... experiment.
        >
        > What you see is not what will be committed so please be patient and bear with a design that's not final from the start.
        >
        > Gilles

        OK, I'm commenting on what I see. If you have other expectations on when people should make comments then let us know.


        --J

        Comments
        1. By Gilles Chehade (gilles) on https://www.poolp.org/~gilles/

          > Why "name" for an e-mail address. Why not call it incoming_email?
          > That's what it is, no?

          That's a comment, and a valid one for the matter.


          > OK, so what I'm complaining about is very typical of simple data models. They are so simple people don't bother to get them right. The attribute called "name" has very little to do with the actual concept of a human name. It is an e-mail address. Please call it something related to that.

          That's not a comment, that's you complaining that I did not do the right thing and telling me what I should do with a directive tone ... while you did not even bother to send a diff and an explanation as to why your way is better (which will probably be the case since I agree that the current schema is not correct).

          Just to be clear, comments and critiques are more than welcome but when they are given using that tone, I will not give the slightest shit about them ... unless you send me a diff ;-)

      2. By Marc Espie (espie) on

        > > name VARCHAR(255) NOT NULL

        > I tweaked the tables until I got the C code as simple as possible, if I had to worry about getting the most accurate name or the most accurate type (which is not enforced by SQLite anyways) it would have taken much more time to ... experiment.

        This is actually funny. Your DB background shows Gilles.

        Like, anyone who actually writes SQLite code and knows sqlite doesn't really care won't even use VARCHAR(255), but TEXT instead.

        So come on, do your experiments right ! use the proper name !

        oh hey, and unique in the correct columns makes sense too... yep, even for a stupid experiment.

        Comments
        1. By Gilles Chehade (gilles) on https://www.poolp.org/~gilles/

          > > > name VARCHAR(255) NOT NULL
          >
          > > I tweaked the tables until I got the C code as simple as possible, if I had to worry about getting the most accurate name or the most accurate type (which is not enforced by SQLite anyways) it would have taken much more time to ... experiment.
          >
          > This is actually funny. Your DB background shows Gilles.
          >
          > Like, anyone who actually writes SQLite code and knows sqlite doesn't really care won't even use VARCHAR(255), but TEXT instead.
          >
          > So come on, do your experiments right ! use the proper name !
          >

          tsss :-)


          > oh hey, and unique in the correct columns makes sense too... yep, even for a stupid experiment.

          I didn't get that ?
          what columns are unique besides the relay column ?

        2. By Gilles Chehade (gilles) on https://www.poolp.org/~gilles/

          > > > name VARCHAR(255) NOT NULL
          >
          > > I tweaked the tables until I got the C code as simple as possible, if I had to worry about getting the most accurate name or the most accurate type (which is not enforced by SQLite anyways) it would have taken much more time to ... experiment.
          >
          > This is actually funny. Your DB background shows Gilles.
          >
          > Like, anyone who actually writes SQLite code and knows sqlite doesn't really care won't even use VARCHAR(255), but TEXT instead.
          >
          > So come on, do your experiments right ! use the proper name !
          >

          tsss :-)


          > oh hey, and unique in the correct columns makes sense too... yep, even for a stupid experiment.

          I didn't get that ?
          what columns are unique besides the relay column ?

          Comments
          1. By Marc Espie (espie) on

            Oh wow, aliases are not unique, go figure.

            On the other hand, there are addresses in both virtual and aliases, so it would make sense to add an indirection there.


            Then again, since you only select() from the database, proper VIEWs are in order.

Credits

Copyright © - Daniel Hartmeier. All rights reserved. Articles and comments are copyright their respective authors, submission implies license to publish on this web site. Contents of the archive prior to as well as images and HTML templates were copied from the fabulous original deadly.org with Jose's and Jim's kind permission. This journal runs as CGI with httpd(8) on OpenBSD, the source code is BSD licensed. undeadly \Un*dead"ly\, a. Not subject to death; immortal. [Obs.]