home from SQL

[Ralf Hildebrandt]

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.

[Steffen Kaiser]

Post by Ralf Hildebrandt
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

[Vijay Rajah]

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

Post by Ralf Hildebrandt
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.