We have what is a pretty common setup: Postfix & Dovecot both
authenticating against a PostfixAdmin database (called "mails") stored
in PostgreSQL running on the localhost.
For SOGo to authenticate against a database, we setup the following view
against the "mailbox" table in our "mails" database.
------------------------------------------
CREATE OR REPLACE VIEW sogo_authentication AS
SELECT
mailbox.username AS c_uid,
mailbox.username AS c_name,
mailbox.password AS c_password,
mailbox.name AS c_cn,
mailbox.username AS mail
FROM mailbox
WHERE
mailbox.active = true;
ALTER TABLE sogo_authentication OWNER TO postgres;
GRANT ALL ON TABLE sogo_authentication TO postgres;
GRANT SELECT ON TABLE sogo_authentication TO mails_reader;
------------------------------------------
The "sogo" database user (login role) is a member of the "mails_reader"
group (group role) in PostgreSQL. That gives it read-only access to the
view. That all works perfectly. It leverages our existing
authentication system used for our mail server.
We also had to configure the pg_hba.conf file (note that in a
pg_hba.conf file, more specific entries should be listed first, so you
may need to move these up towards the top of the list).
------------------------------------------
host sogo sogo 127.0.0.1/32 md5
host mails sogo 127.0.0.1/32 md5
------------------------------------------
On the SOGo side, we have the following in /etc/sogo/sogo.conf in order
to connect to that for authentication.
SOGoUserSources =
(
{
type = sql;
id = directory;
displayName = "SOGo Account Listing";
viewURL =
"postgresql://sogo:[email protected]:5432/mails/sogo_authentication";
canAuthenticate = YES;
isAddressBook = YES;
userPasswordAlgorithm = md5-crypt;
}
);
...
I have figured out that if I add the following field to the "mailbox"
table and export in the view without the "x_sogo_" prefix, it shows up
in the SOGo Web client address book as the work number.
x_sogo_c_telephonenumber character varying(255) -> c_telephonenumber (in
the VIEW)
The question I have is this, if we want to add other fields to the
mailbox table (and the "sogo_authentication" view), is there a list
somewhere of what field name and data type that SOGo is expecting for
each new field?
--
[email protected]
https://inverse.ca/sogo/lists
