[Dovecot] Quota with dict backend - sql does not get registered
Hello everyone,
I've been working with the quota plugin, attempting to use the dict backend. It seems the sql dict class is not getting loaded. I dropped a couple lines in dict_class_lookup(dict.c) and it seems the only class that gets loaded is "proxy". Any idea how to get the sql driver to load in the quota/dict module?
My authentication goes through the sql module and is working perfectly, so I know that the module and db are talking in general.
I've scoured the documentation and dug through the code but I can't seem to discover the solution. There is "Dynamic SQL drivers" in the compiling from source section:
http://wiki.dovecot.org/CompilingSource#line-158
I compiled the driver_mysql.so, copied it to lib/dovecot/, symlinked it into auth/, imap/, pop3/ and lda/, then tried-- but it still only showed "proxy" as being loaded. After that I added it to the mail_plugins list,
mail_plugins = driver_mysql quota
but it didn't like that and spat out some linking errors:
Edlopen(/usr/local/dovecot-1.0.rc2/lib/dovecot/imap/driver_mysql.so) failed: /usr/local/dovecot-1.0.rc2/lib/dovecot/imap/driver_mysql.so: undefined symbol: sql_not_connected_result
So, where do I go from here? Any insights would be appreciated and I'll keep digging.
Regards,
David
On Fri, 2006-07-28 at 15:56 -0700, David Jonas wrote:
Hello everyone,
I've been working with the quota plugin, attempting to use the dict backend. It seems the sql dict class is not getting loaded. I dropped a couple lines in dict_class_lookup(dict.c) and it seems the only class that gets loaded is "proxy". Any idea how to get the sql driver to load in the quota/dict module?
I added the code to load the driver in src/imap/main.c following the example from dict-client registration. Modified the Makefile, etc, got it to compile. But it seg faults when imap is run. Traced it to src/lib-dict/dict-sql.c:
/* @UNSAFE */ drivers = array_get(&sql_drivers, &count); dict_sql_classes = i_new(struct dict, count + 1);
So, the comments don't lie. It is unsafe. Looks like that is why the dict-server is the default with the proxy backend only. I guess I'll poke around more. Advice on how to skirt this would be appreciated.
Was all this already talked about on the list, hence no reply? I couldn't find anything...
David
On Mon, 2006-07-31 at 12:19 -0700, David Jonas wrote:
On Fri, 2006-07-28 at 15:56 -0700, David Jonas wrote:
Hello everyone,
I've been working with the quota plugin, attempting to use the dict backend. It seems the sql dict class is not getting loaded. I dropped a couple lines in dict_class_lookup(dict.c) and it seems the only class that gets loaded is "proxy". Any idea how to get the sql driver to load in the quota/dict module?
I added the code to load the driver in src/imap/main.c following the example from dict-client registration. Modified the Makefile, etc, got it to compile. But it seg faults when imap is run. Traced it to src/lib-dict/dict-sql.c:
/* @UNSAFE */ drivers = array_get(&sql_drivers, &count); dict_sql_classes = i_new(struct dict, count + 1);
So, the comments don't lie. It is unsafe.
Actually the @UNSAFE means pretty different thing. It's just a pointer that the code is doing there memory allocation calculations and that if you aren't careful you can cause security holes in there.
Looks like that is why the dict-server is the default with the proxy backend only. I guess I'll poke around more. Advice on how to skirt this would be appreciated.
After I just yesterday added Berkeley DB support for lib-dict, I thought maybe everyone should just use dict via the proxy. The dict server is linked against all the necessary libraries, and it's probably faster to make it go through dict server anyway. Otherwise it gets a bit difficult to figure out how the linking should work..
On Mon, 2006-07-31 at 22:43 +0300, Timo Sirainen wrote:
After I just yesterday added Berkeley DB support for lib-dict, I thought maybe everyone should just use dict via the proxy. The dict server is linked against all the necessary libraries, and it's probably faster to make it go through dict server anyway. Otherwise it gets a bit difficult to figure out how the linking should work..
Cool, thanks Tim. I tried the proxy before but was getting funny results. So now I've focused my efforts on working those out. I've come across some interesting stuff. First, it appears that the order of fields/values in the SQL insert queries in src/lib-dict/dict-sql.c are out of order. It seemed to me a confirmed error upon evaluating the ON DUPLICATE KEY UPDATE portion of the query, not just me messing up the config. Diff is at the bottom. I'd make a formal patch for you, but there is some other hoky things going on. Here's my logs. I'm running down these bugs now: Jul 31 15:56:06 uniroyal dovecot: dict: sql dict: commit failed: Not connected to database Jul 31 15:56:06 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't update quota Jul 31 15:56:06 uniroyal dovecot: dict: sql dict: commit failed: Not connected to database Jul 31 15:56:06 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't update quota Jul 31 15:56:06 uniroyal dovecot: IMAP(djonas@): Sending log messages too fast, throttling.. Jul 31 15:56:06 uniroyal dovecot: dict: sql dict: commit failed: Not connected to database Jul 31 15:56:07 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't update quota Jul 31 15:56:08 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't update quota Jul 31 15:56:09 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't update quota Even though dict says the commit failed, Not Connected, the db gets updated. Why there are three "Couldn't update quota" messages as well I have not discovered. Also, as a side note, I changed my mail_log_prefix to "%Us(%u@%d): " yet I don't ever see the domain portion. This also happens in the quota plugin. The user gets set to just the part before the @. Thanks for the help. ---- Reorder SQL arguments diff ---- --- dict-sql.c.orig 2006-07-31 16:01:45.000000000 -0700 +++ dict-sql.c 2006-07-31 16:07:12.000000000 -0700 @@ -310,8 +310,8 @@ "ON DUPLICATE KEY UPDATE %s = '%s'", dict->table, dict->select_field, dict->where_field, dict->username_field, - sql_escape_string(dict->db, key), sql_escape_string(dict->db, value), + sql_escape_string(dict->db, key), sql_escape_string(dict->db, dict->username), dict->select_field, sql_escape_string(dict->db, value)); @@ -320,8 +320,8 @@ "INSERT INTO %s (%s, %s) VALUES ('%s', '%s') " "ON DUPLICATE KEY UPDATE %s = '%s'", dict->table, dict->select_field, dict->where_field, - sql_escape_string(dict->db, key), sql_escape_string(dict->db, value), + sql_escape_string(dict->db, key), dict->select_field, sql_escape_string(dict->db, value)); } @@ -344,19 +344,19 @@ t_push(); if (priv) { query = t_strdup_printf( - "INSERT INTO %s (%s, %s, %s) VALUES ('%s', %lld, '%s') " + "INSERT INTO %s (%s, %s, %s) VALUES (%lld, '%s', '%s') " "ON DUPLICATE KEY UPDATE %s = %s + %lld", dict->table, dict->select_field, dict->where_field, - dict->username_field, - sql_escape_string(dict->db, key), diff, + dict->username_field, diff, + sql_escape_string(dict->db, key), sql_escape_string(dict->db, dict->username), dict->select_field, dict->select_field, diff); } else { query = t_strdup_printf( - "INSERT INTO %s (%s, %s) VALUES (%s, %lld) " + "INSERT INTO %s (%s, %s) VALUES (%lld, '%s') " "ON DUPLICATE KEY UPDATE %s = %s + %lld", - dict->table, dict->select_field, dict->where_field, - sql_escape_string(dict->db, key), diff, + dict->table, dict->select_field, dict->where_field, diff, + sql_escape_string(dict->db, key), dict->select_field, dict->select_field, diff); } sql_update(ctx->sql_ctx, query);
On Mon, 2006-07-31 at 16:17 -0700, David Jonas wrote:
On Mon, 2006-07-31 at 22:43 +0300, Timo Sirainen wrote: [snip] Cool, thanks Tim. I tried the proxy before but was getting funny results. So now I've focused my efforts on working those out. I've come across some interesting stuff.
Sorry I wrote your name wrong, Timo. Won't happen again.
[snip]
Also, as a side note, I changed my mail_log_prefix to "%Us(%u@%d): " yet I don't ever see the domain portion. This also happens in the quota plugin. The user gets set to just the part before the @.
Just wanted to say that this was a configuration problem and I wanted to state how and why so others don't have the same problem.
Dovecot sets the username to what it exactly is in the database, or password file, etc. This probably isn't a problem with most lookups, but with the versatility of the SQL backend you can get yourself into trouble like I did. My query was like this:
SELECT user, password, target AS userdb_home, 89 AS userdb_uid, 89 AS userdb_gid FROM accounts WHERE class='pop' AND domain='%d' AND user='%n'
As you can see domain and user are two different fields. So I was getting just the user part of the username set by dovecot. The query should have been:
SELECT CONCAT(user,'@',domain) AS user, password, target AS userdb_home, 89 AS userdb_uid, 89 AS userdb_gid FROM accounts WHERE class='pop' AND domain='%d' AND user='%n'
Then things like the quota plugin will have the full username to work with and variable expansion will work were it's needed.
[snip]
participants (2)
-
David Jonas
-
Timo Sirainen