foxcpp / go-imap-sql

SQL-based storage backend for go-imap v2

go-imap-sql

SQL-based storage backend for go-imap library.

Building

Go 1.13 is required due to use of Go 1.13 error inspection features.

RDBMS support

go-imap-sql is known to work with (and constantly being tested against) following RDBMS:

• SQLite 3.25.0
• PostgreSQL 9.6

Following RDBMS have experimental support:

• CockroachDB 20.1.5

Following RDBMS were actively supported in the past, it's unknown whether they still work with go-imap-sql:

• MariaDB 10.2

IMAP Extensions Supported

• CHILDREN
• APPEND-LIMIT
• MOVE
• SPECIAL-USE
• SORT

Authentication

go-imap-sql does not implement any authentication. "password" argument of Login method is not checked and the user account is created if it does not exist. You are supposed to wrap it to implement your own authentication the way you need it.

Usernames case-insensitivity

Usernames are always converted to lower-case before doing anything. This means that if you type imapsql-ctl ... users create FOXCPP. Account with username foxcpp will be created. Also this means that you can use any case in account settings in your IMAP client.

secure_delete

You may want to overwrite deleted messages and theirs meta-data with zeroes for security/privacy reasons. For MySQL, PostgreSQL - consult documentation (AFAIK, there is no such option).

For SQLite3, you should build go-imap-sql with sqlite_secure_delete build tag. It will enable corresponding SQLite3 feature by default for all databases.

If you want to enable it per-database - you can use file:PATH?_secure_delete=ON in DSN.

UIDVALIDITY

go-imap-sql never invalidates UIDs in an existing mailbox. If mailbox is DELETE'd then UIDVALIDITY value changes.

Unlike many popular IMAP server implementations, go-imap-sql uses randomly generated UIDVALIDITY values instead of timestamps.

This makes several things easier to implement with less edge cases. And answer to the question you are already probably asked: To make go-imap-sql malfunction you need to get Go's PRNG to generate two equal integers in range of [1, 2^32-1] just at right moment (seems unlikely enough to ignore it). Even then, it will not cause much problems due to the way most client implementations work.

go-imap-sql uses separate math/rand.Rand instance and seeds it with system time on initialization (in New).

You can provide custom pre-seeded struct implementing math/rand.Source in Opts struct (PRNG field).

Maddy

You can use go-imap-sql as part of the maddy mail server.

imapsql-ctl

For direct access to database you can use imapsql-ctl console utility. See more information in separate README here.

go install github.com/foxcpp/go-imap-sql/cmd/imapsql-ctl

go-imap-sql's Issues

Expunge does two (!) full table scans

Not sure what's exactly is happening, but decreaseRefForMarked query seems to be the problem.

SQLite FTS5 as a FTS engine

Needs driver support, depends on #21 for generic interfaces, needs to be carefully designed to work well with external storage (e.g. FS, etc).

go-imap-sql v1.0 status

• Current roadmap: https://github.com/foxcpp/go-imap-sql/projects/1
  Note: "In progress" often represents state only of my local repository clone, not upstream here.
  If you want to help - don't work on tasks in In progress column unless I allow you to do so.

• Plans outline:

  • Implement basic functionality of IMAP backend
  • Reevaluate standards conformance and fix found issues
  • Extend tests to make sure these issues will not reappear in future
  • Implement interfaces required by maddy (foxcpp/maddy#15)
  • Implement support for more extensions (UIDPLUS, QUOTA, perhaps QRESYNC, CONDSTORE)
  • Evaluate implementation performance, optimize SQL queries where needed.
  • Define compatibility guarantees and design schema upgrade protocol
  • Deploy maddy with go-imap-sql to private server for extended testing
  • Fix found problems (#3)
  • Release as v1.0-beta1
  • Integrate with maddy in upstream
    ...

  No time estimates since the amount of time I can invest in go-sqlmail varies depending on the moon phase and quantum fluctuations, sorry.

Also, this issue is a good place for questions about status, plans, and other development-related questions. Feel free to ask here if you don't think your question needs a separate issue.

CC @Hexawolf @NamedKitten @emersion

Parse cachedHeader date more loosely

Currently there's one hardcoded format - "Mon, 2 Jan 2006 15:04:05 -0700", which returns 0 for a lot of other date formats used in emails, such as (CEST) at the end of the date

Use incremental BLOB I/O on SQLite

Requires driver support, which is not present at the moment.

Issues revealed by imaptest

Command:

imaptest seed=666 port=9999 copybox=INBOX2 user=testuser pass=foo mbox=~/Downloads/firefox/dovecot-crlf test=/home/foxcpp/Software/imaptest-20181206/src/tests/

mbox file: http://www.dovecot.org/tmp/dovecot-crlf

Full log: imaptest_scripted.log

Make children mailboxes check faster on PostgreSQL

We need to create a pattern_ops index so PostgreSQL can do a prefix search effectively.

Handle serialization failures

We should retry on serialization error (possibly, with a small delay). Currently we hard-fail and reject modifications.

Related to #12, we might able to decrease rate of serialization failures. But SQLite remains a problem because it always uses Serializable Isolation Level due to its design.

Implement QUOTA extension

https://tools.ietf.org/html/rfc2087
https://github.com/emersion/go-imap-quota
emersion/go-imap-quota#6

Review how UID are assigned to messages

Current approach requires us to use Serializable Isolation Level when working with database, which leads to big performance penalty. It would be nice be able to work with less strict isolation.

CockroachDB and Message Delete not working " pq: got 12 values but expected 11 "

Connected to imap server and sending a delete message gives:

imapsql: MoveMessages (copy msgs) [true 60:67 Trash]: pq: got 12 values but expected 11 {"mbox":"INBOX","mboxId":976825787344814081,"username":"[email protected]","uid":976825786701217793}

Add argon2 hash algorithm for passwords

I personally think bcrypt is ok for general purposes (for small personal mail hosting) but it might make sense to please some paranoids.

Ref.: https://web.archive.org/web/20190709224041/https://pthree.org/2016/06/28/lets-talk-password-hashing/

Domain design for backend storage?

Instead of combining the sql queries inside the backend logic, would it be possible to create domain objects so that multiple backends (SQL, key/value, disk, AWS storage, Memory, etc...) could be written to use the great backend logic you're pulling together?

In other words, simply provide a type BackendStorage interface{ ... } to whatever logic you're working on so that multiple storage solutions could be built and used.

Then again, I realize it's called "go-sqlmail", but you're building a needed tool and wanted to mention this as soon as possible to see if it could be a goal.

Implement meaningful limits for mailbox size and other data

Export/import mbox files

Makes it easy to migrate storage from other implementations.

Refactor password hashing

• bcrypt does not need a separate salt
• bcrypt is already ASCII-encoded
• base64 is more compact
• The format mismatches pass_table module from maddy

Needs to stay compatible so still handle sha3-512 and bcrypt from old DBs and generate new passwords with tags sha3-512-b64 and bcrypt-fix.

Delivery.Commit can hang

When I start the following goroutine:

d := be.base.NewDelivery()

go func() {
	for {
		time.Sleep(10 * time.Second)

		body := "From: [email protected]\n"
		body += "To: [email protected]\n"
		body += "Subject: Automated test\n"
		body += "Content-Type: text/plain; charset=UTF-8\n\n"
		body += "Hello world\n"

		d.AddRcpt(viper.GetString("user.username"), textproto.Header{})
		d.BodyRaw(strings.NewReader(body))

		err := d.Commit()
		if err != nil {
			log.Error("Unable to commit: %s", err.Error())
			continue
		}

		log.Info("Created new message")
	}
}()

It's possible for d.Commit() to deadlock when Thunderbird has connected to the server, after which a bunch more stuff in the imap server locks up.

It locks up on this line:

go-imap-sql/delivery.go

Line 376 in 2f57903

d.b.updates <- update

Am I doing something wrong, or is this a bug?

Correctly implement DELETE for mailbox with children

Set \Noselect attribute and disallow select if mailbox does have any childrens. Don't allow to delete with \Noselect and childrens.

Messages without a Content-Transfer-Encoding header pass an empty encoding to go-imap

as such, combined with emersion/go-imap#545, the encoding gets serialized as NIL which is invalid according to spec; the encoding should default in this case to 7bit:

This is the default value -- that is, "Content-Transfer-Encoding: 7BIT" is assumed if the Content-Transfer-Encoding header field is not present.

Don't keep blob in External Store when message is not added due to an error

Fix SeqSet "*" being misinterpreted as "1:*"

This would require special-case handling for it since we don't know the ID of the last message in the mailbox when querying it.

pq: count(*) must be used to call a parameterless aggregate function in flag search

fetchmail: IMAP> A0003 SEARCH UNSEEN UNDELETED
fetchmail: IMAP< A0003 NO pq: count(*) must be used to call a parameterless aggregate function

Spotted while investigating a different issue.

go-imap v2

Track go-imap v2 development. See corresponding branch for progress.

Implement context support for Delivery interface

Return an error from CheckPlain method

Remove support for storing message contents in table rows

See foxcpp/maddy@1e906dd.

It turned out to be impractical even for SQLite3. go-imap-sql code can be greatly simplified with removal of another way to store messages which requires quite a lot of special-case handling for it.

Flags/PermanentFlags somehow contains an empty string

originally reported here: emersion/go-imap#544
tl;dr: go-imap-sql inserts an empty string to Flags, which then gets incorrectly serialized in go-imap for the SELECT response and becomes impossible to get parsed back by go-imap itself

looks like the logic for collecting flags from the mailbox is wrong:
https://github.com/foxcpp/go-imap-sql/blob/c0176dad/mailbox.go#L128-L137

the simplest fix would be

if flag == "" {
    continue
}

though i'm not sure why it even happens

Support for storage compression

LZ4 can be used to reduce the size of message blobs.
It is very fast compression algorithm with nearly instantaneous decompression, we just to find a good implementation in Go.

Get rid of "marking" logic in message removal

It is inefficient and can be avoided if input SeqSet is prepared accordingly.

Implement UIDPLUS extension

https://github.com/emersion/go-imap-uidplus
https://tools.ietf.org/html/rfc4315
emersion/go-imap-uidplus#3

Don't fail with error on incompatible database schema

maddy[24867]: 25.08.19 13:32:35 /etc/maddy/maddy.conf:28: sql: incompatible database schema, upgrade required (3 < 4)

Upgrade it automatically to reduce maintenance burden. We expect people to back up DB on regular basis anyway.

CockroachDB support

CockroachDB is a cloud-native SQL database for building global, scalable cloud services that survive disasters.

CockroachDB supports the PostgreSQL wire protocol, so you can use any available PostgreSQL client drivers to connect from various languages.

go-imap-sql currenlty doesn't work with CockroachDB due to the following known problems:

• GROUP BY behaves differently
  cockroachdb/cockroach#26709

There are probably more problems, documented above are the only known ones.

go-imap-sql fully supports PostgreSQL and I would prefer to wait for CockroachDB to become more compatible with it than to introduce various hacks.

CC @NamedKitten

Computation-intensive hash algorithms for passwords

Somebody raised some concerns about salted SHA3-512 being not enough to provide reasonable security.

Thoughts?

Sorting sorts both by the selected criterion and UID

Code in messageCompare(...) with a valid sorting criterion still falls back to return buf[i].ID < buf[j].ID for some reason when it should return false.

FTS engine interfaces and integration

Fetching and doing matching against all messages is simply inefficient. Each RDBMS we support (even SQLite) includes a FTS engine implementation, we can't miss that as it allows us to have FTS enabled by default and without additional configuration.

Implement CONDSTORE extension

https://tools.ietf.org/html/rfc4551
https://github.com/foxcpp/go-sqlmail/projects/1#card-18307191 (ENABLE extension)

PostgreSQL uses default isolation level instead of Serializable

This can lead to data inconsistency.

SEARCH ALL returns duplicate entries

. SELECT INBOX
* FLAGS (\Seen \Answered \Flagged \Deleted \Draft \Recent old \Seen)
* OK [PERMANENTFLAGS (\Seen \Answered \Flagged \Deleted \Draft \* \Recent old \Seen)] Flags permitted.
* OK [UNSEEN 1] Message 1 is first unseen
* 5 EXISTS
* 5 RECENT
* OK [UIDNEXT 9] Predicted next UID
* OK [UIDVALIDITY 86221722] UIDs valid
. OK [READ-WRITE] SELECT completed
. SEARCH ALL
* SEARCH 1 2 2 3 3 4 4 5 5

Clean external store on mailbox or user removal

Current implementation seems to leave dangling objects in it.

fsstore: Split storage directory based on the ID prefix

Instead of putting all files into one directory, use first byte as a name for subdirectory.
This is important to amortize lookup time.

X-Priority is not cached while it should be

Value in cached fields list has incorrect case for the first letter ("x-Priority").