Hi,

I think there is no reason to be offlist.

Your setup should work, only problem could be the freebsd userid. My setup has only one user for all accounts and no "real" users. I only have a vmail user.

Maybe a tutorial for dovecot and mysql only would help you ( I don't have a link here, but google has)

hope that helps

Harald

Am 06.07.2018 um 15:18 schrieb Graham S. Jarvis:

MANY thanks Harald,

it was the SQL that is my problem ;)

I don't know whether the detail below belongs in the mailing list - so I thought I'd pm you with this first.

Two questions: A) If I read the dovecot documentation right - it is going to need to say where the mail is - home directory (and Maildir or mbox ?).

My setup is fairly small/restricted - I want to give the committee members of a non-profit a way of changing the IMAP/SMTP passwords for their Thunderbird/Apple Mail clients by using their Joomla login password. I'm going to use FreeBSD's adduser command to add any new committee members and this creates a standard directory structure under /usr/home/<USERNAME> and I'll (manually) make sure that <USERNAME> is the same as the Joomla User name that they created.  The Virtual Users Map and Aliases files to point the the actual home (Maildir) directories. Once it's done it won't change very often. I'm assuming that Dovecot will first do the SQL lookup and only try /etc/passwd if it fails - so I can adduser with a locked password, so that the alias map works and Postfix finds the Maildir.

B) I didn't yet get my mind around Joomla groups and for example how to give all committee members a way to change the password for their mail accounts but only some of the members access to the front-end Mailing list management (ACYMailer). I think just creating a new group "committee_mail" is good enough and this will be the <ID OF THE JOOMLA GROUP> in your SQL. Otherwise I might want to have the SQL check the existence of various Joomla groups.

What do you think?

-Graham-

...

---

Message: 1 Date: Thu, 5 Jul 2018 17:37:44 +0200 From: Harald Leithner leithner@itronic.at To: dovecot@dovecot.org Subject: Re: user/password Authentication via Joomla database Message-ID: 180fd3b8-3369-9102-0216-94e41033f379@itronic.at Content-Type: text/plain; charset="windows-1252"

Hi,

this shouldn't be to hard.

Your dovecot-sql.conf.ext could look something like this:

---

driver = mysql connect = host=<host> dbname=<dbname> user=<username> password=<password>

default_pass_scheme = BLF-CRYPT password_query =
SELECT
u.username,
u.password,
FROM _users u
LEFT JOIN _user_usergroup_map g ON u.id = g.user_id
WHERE
u.username = '%u' AND u.block = 0 AND g.group_id = <ID OF THE JOOMLA GROUP>

user_query =
SELECT
u.username,
u.password,
FROM _users u
LEFT JOIN _user_usergroup_map g ON u.id = g.user_id
WHERE
u.username = '%u' AND u.block = 0 AND g.group_id = <ID OF THE JOOMLA GROUP>

iterate_query = SELECT username AS user FROM _users u
LEFT JOIN _user_usergroup_map g ON u.id = g.user_id
WHERE
u.block = 0 AND g.group_id = <ID OF THE JOOMLA GROUP>

---

But I'm not sure if dovecot supports Blowfish $2y$ Version used by Joomla as default.

Also I didn't tested this configuration, so there maybe someflaws in it.

bye Harald

Am 05.07.2018 um 16:05 schrieb Graham S. Jarvis:

...

Hello,

did anyone ever set up Authentication via lookups to a Joomla (mysql) database?

What I'd like to do is lookup Joomla users (who are in specific/multiple groups) in the Joomla database and then fallback to /etc/passwd authentication to let system users authenticate.

I'm running : # 2.2.35 (b1cb664): /usr/local/etc/dovecot/dovecot.conf # OS: FreeBSD 11.1-RELEASE-p10 amd64

Thanks in advance,

-Graham-

-- Harald Leithner

ITronic Wiedner Hauptstraße 120/5.1, 1050 Wien, Austria Tel: +43-1-545 0 604 Mobil: +43-699-123 78 4 78 Mail: leithner@itronic.at | itronic.at