Daniel Schütze
2019-Jan-04 09:11 UTC
Unexpected accounts showing up in replication status (doveadm replicator status '*')
I have a replication set up with two dovecot 2.3.4 servers. Messages appear to be replicating fine between them, however there is an oddity when I run doveadm replicator status '*' and I'm worried work is being carried out needlessly. Both machines have identical configs and have a mysql backend for username/passwords.? Both machines point to the same mysql server and same tables. When I run doveadm user '*' On either machine I am presented with the same correct list of users.? The users are presented in the form: username and not username at domain. When I run doveadm replicator status '*' On either machine I receive a list of usernames which includes not only username but also username at domain.? Both of these "accounts" have fast sync, full sync and success sync entries and these are not the same times between username and username at domain (hence I'm worried unnecessary work is being carried out). My ee dovecot-sql.conf.ext Has these entires re usernames user_query = SELECT home, uid, gid FROM users WHERE username = '%n' password_query = SELECT username AS user, domain, password, home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid FROM users WHERE username = '%u' [Having copied these in I do see the different usage of %u and %n] iterate_query = SELECT username AS user,domain FROM users The users table is as follows +--------------+--------------+------+-----+---------+-------+ | Field??????? | Type???????? | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | username???? | varchar(128) | NO?? |???? | NULL??? |?????? | | domain?????? | varchar(128) | NO?? |???? | NULL??? |?????? | | password???? | varchar(64)? | NO?? |???? | NULL??? |?????? | | home???????? | varchar(255) | NO?? |???? | NULL??? |?????? | | uid????????? | int(11)????? | NO?? |???? | NULL??? |?????? | | gid????????? | int(11)????? | NO?? |???? | NULL??? |?????? | | active?????? | char(1)????? | NO?? |???? | Y?????? |?????? | | mail_replica | varchar(255) | NO?? |???? | NULL??? |?????? | +--------------+--------------+------+-----+---------+-------+ I assume that the duplication of username with and without domains isn't as intended, any suggestions where I am going wrong? I did previously have the servers running with the iterate query commented out and when that was the case doveadm user '*' returned the correct number of accounts but in the format username at domain and the replicator status again 'double listed' accounts as described above. Thank you for any tips! -- Daniel Sch?tze Director ------------------ CWA International Balmoral House 9 John Street London WC1N 2ES (t) + 44 (0)20 7242 8444 (e) dms at cwa.uk.com (w) http://www.cwa.uk.com/
Daniel Schütze
2019-Jan-21 10:03 UTC
Unexpected accounts showing up in replication status (doveadm replicator status '*')
Just an update that I think I know what has been going on. I believe I've been tripped up by two things. 1) Not always clearly reading the actual component being referred to when "userid", "user" and "username" are used in official online documentation or other web sources. 2) Setting up dovecot to accept delivery / auth on %n and dropping the domain. For #1 it is descriptions such as the following from the online documentation which tripped me up %u user full username (e.g. user at domain) %n username user part in user at domain, same as %u if there's no domain i.e. %n referring both to "username" and "user" where for testing at example.com %n is testing and %u is testing at example.com Another one which could be clearer is the dovecot-sql.conf.ext file gives a example database with username and domain fields and the same file has an example password_query with the userid field. For #2 failure to get to grips with #1 and also various examples online which aren't fully context explained meant I had an inconsistent set of queries. After getting to grips with that I changed my mysql auth to be consistent and always return username and domain e.g. [Note in my database username contains "testing" and domain "example.com") password_query = SELECT username, domain [etc] user_query = [does not return username or domain] iterate_query = SELECT username,domain FROM users i.e. note they all now return two separate fields with "testing" and "example.com" which dovecot is merging as per the documentation. With that set up both dovadm user '*' and doveadm -A [etc] return and use testing at example.com The last problem I had was that in my mail delivery (from an alias file) I was using testing: "dovecot-lda -d testing" And that seems to trigger replication to trigger on testing as well as testing at example.com being triggered when the user made changes to their account. So having changed the mysql as per above and the delivery to testing: "dovecot-lda -d testing at example.com" and having removed all entries without @domain from replication with doveadm replicator remove [entries without domains] doveadm repliciator status now returns the correct number of accounts and doveadm repliciator status '*' does not have "duplicate" entries. Fingers crossed that is it and there are no other call which will cause replication on "testing" as separate from "testing at example.com" On 04/01/19 09:11, Daniel Sch?tze wrote:> I have a replication set up with two dovecot 2.3.4 servers. > > Messages appear to be replicating fine between them, however there is > an oddity when I run doveadm replicator status '*' and I'm worried > work is being carried out needlessly. > > Both machines have identical configs and have a mysql backend for > username/passwords.? Both machines point to the same mysql server and > same tables. > > When I run > > doveadm user '*' > > On either machine I am presented with the same correct list of users.? > The users are presented in the form: username and not username at domain. > > When I run > > doveadm replicator status '*' > > On either machine I receive a list of usernames which includes not > only username but also username at domain.? Both of these "accounts" have > fast sync, full sync and success sync entries and these are not the > same times between username and username at domain (hence I'm worried > unnecessary work is being carried out). > > My ee dovecot-sql.conf.ext > > Has these entires re usernames > > user_query = SELECT home, uid, gid FROM users WHERE username = '%n' > > password_query = SELECT username AS user, domain, password, home AS > userdb_home, uid AS userdb_uid, gid AS userdb_gid FROM users WHERE > username = '%u' > > [Having copied these in I do see the different usage of %u and %n] > > iterate_query = SELECT username AS user,domain FROM users > > The users table is as follows > > +--------------+--------------+------+-----+---------+-------+ > | Field??????? | Type???????? | Null | Key | Default | Extra | > +--------------+--------------+------+-----+---------+-------+ > | username???? | varchar(128) | NO?? |???? | NULL??? |?????? | > | domain?????? | varchar(128) | NO?? |???? | NULL??? |?????? | > | password???? | varchar(64)? | NO?? |???? | NULL??? |?????? | > | home???????? | varchar(255) | NO?? |???? | NULL??? |?????? | > | uid????????? | int(11)????? | NO?? |???? | NULL??? |?????? | > | gid????????? | int(11)????? | NO?? |???? | NULL??? |?????? | > | active?????? | char(1)????? | NO?? |???? | Y?????? |?????? | > | mail_replica | varchar(255) | NO?? |???? | NULL??? |?????? | > +--------------+--------------+------+-----+---------+-------+ > > I assume that the duplication of username with and without domains > isn't as intended, any suggestions where I am going wrong? > > I did previously have the servers running with the iterate query > commented out and when that was the case > > doveadm user '*' > > returned the correct number of accounts but in the format > username at domain and the replicator status again 'double listed' > accounts as described above. > > Thank you for any tips! > > >-------------- next part -------------- An HTML attachment was scrubbed... URL: <https://dovecot.org/pipermail/dovecot/attachments/20190121/57b8eb62/attachment.html>