Currently I'm using user_query = SELECT 1000 AS uid, 1000 AS gid, '/srv/vmail/%2.256Hu/%Lu' AS home, ... so I'm hashing based on %u (basically). But in my SQL db I have a "unique_identifier" field, which never changes, even when the user is changing his/her email address (due to marriage or the like). What I'd really like to do is to use %u to find the value of the unique_identifier field, hash THAT value and use "AS home". But how? That way I can rename users without shuffling directories around the filesystem. -- [*] sys4 AG http://sys4.de, +49 (89) 30 90 46 64 Franziskanerstra?e 15, 81669 M?nchen Sitz der Gesellschaft: M?nchen, Amtsgericht M?nchen: HRB 199263 Vorstand: Patrick Ben Koetter, Marc Schiffbauer Aufsichtsratsvorsitzender: Florian Kirstein
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, 30 Sep 2014, Ralf Hildebrandt wrote:> Currently I'm using > user_query = SELECT 1000 AS uid, 1000 AS gid, '/srv/vmail/%2.256Hu/%Lu' AS home, ... > > so I'm hashing based on %u (basically). But in my SQL db I have a > "unique_identifier" field, which never changes, even when the user is > changing his/her email address (due to marriage or the like). > > What I'd really like to do is to use %u to find the value of the > unique_identifier field, hash THAT value and use "AS home". But how?Dovecot cannot help you to hash that value, but if you use a SQL server, you can create a function, which tranforms the unique_identifier into any string you like. Then use user_query = SELECT 1000 AS uid, 1000 AS gid, toHomeDir(unique_identifier) AS home, ... - -- Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux) iQEVAwUBVCrAPXz1H7kL/d9rAQLcGgf/a9ok9TVEscjoyGhuLEpF1RlyG1EhQO+B in5mseexWUPdOhcK/BFkc2FasuAaW8kQ7E+tnRY3h76MQn61c//soATMDI+iJx8X wNVzBRu2YgcISwwfALIQmcVHN3mIbem327Z5nKX+bXYnfoDFvcSAdTohM2YFSw+A xO/oCucW2Kgtv1rQQ1AL88N86VJLTGhx+uowLGrOPTX8Q1zgzH97ii+Ujki+dtnF AWYLaH8lH//DE4jiqmBLQW6flhXM0xeOYa10oAhkLA2WS8I9GVbuBE4TpYc0OiLt IqtmOmRr4ZJkAuKWEIiVOnksAWWK/vLZZeEJGE/E3No/iKE8QA+0DA==z4O7 -----END PGP SIGNATURE-----
The way I do is to have a trigger. Whenever a field changes, there is a
trigger associated with that, this trigger populates the appropriate
field for that user being modified. I update the field using trigger for
both inserts & updates.
EX: in MYSQL this is the trigger for new user addition. I just add the
email address and password to the db using 'INSERT INTO'..The home
directory is updated by this trigger. ( I have a similar trigger for
updates)
*************************** 1. row ***************************
Trigger: TRIG_HOME_INS
Event: INSERT
Table: user
Statement: BEGIN
SET
new.home=concat('/path/to/mdfiles/',substring_index(new.email,'@',-1),'/',substring_index(new.email,'@',1));
end
Timing: BEFORE
Created: NULL
sql_mode:
Definer: mailadmin at localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
you could have a trigger that updates the appropriate field when the
name changes etc...
-Vijay
On 30/09/14 7:59 PM, Ralf Hildebrandt wrote:> Currently I'm using
> user_query = SELECT 1000 AS uid, 1000 AS gid,
'/srv/vmail/%2.256Hu/%Lu' AS home, ...
>
> so I'm hashing based on %u (basically). But in my SQL db I have a
> "unique_identifier" field, which never changes, even when the
user is
> changing his/her email address (due to marriage or the like).
>
> What I'd really like to do is to use %u to find the value of the
> unique_identifier field, hash THAT value and use "AS home". But
how?
>
> That way I can rename users without shuffling directories around the
> filesystem.
>