[FR] Hide mailaddresses from SOGO GAL

[AndyX90]

Summary

It would be great to have the possibility to hide specific mail-addresses from SOGo-GAL.

Motivation

Sometimes there are mailaddresses in the global-addressbook, which should not be viewable by all users (for example boss@...).

Additional context

Maybe it would be possible to create a checkbox or something on the mailbox-page with "Hide from GAL" or something like that.

[the-voidl]

Would be nice! I also missed that feature in the past... I looked in the code and it looks doable. But therefore we have to add another column in the mailbox table and adopt all php, twig and javascript.

I could look at this in the next days and create a PR. But one question to @andryyy: I order to implement this, I assume that we need this column in the db. I haven't looked how you did such things before. Should we create that col via update.sh?

[dragoangel]

Hi, I think you better ask this future in sogo and not in the mailcow. Unfortunately I don't think it can be implemented as user visibility not only allowed via gal, but also via sharing future (imap folders, calendars and contacts). If you "hide user" all this will be impossible which dropping main logic of groupware. I don't saw any "groupware" have such option which you asking...

I think if you need hide user/group of users from other users - better move this user/group to subdomain and create alias back to main domain, with "not visible in sogo" option set.

[smarsching]

We are currently planning to migrate from Zimbra to mailcow, and I also stumbled upon this issue.

@dragoangel Being able to hide individual users from the GAL is not such an exotic feature. I know that Zimbra has it and as far as I know, Microsoft 365 and Microsoft Exchange (with on-premisies AD) offer it as well.

Unfortunately I don't think it can be implemented as user visibility not only allowed via gal, but also via sharing future (imap folders, calendars and contacts). If you "hide user" all this will be impossible which dropping main logic of groupware. I don't saw any "groupware" have such option which you asking...

Hiding a user from the GAL does not necessarily mean that sharing is not possible. It just means, that you have to know the full username (e-mail address) of that user if you want to share something with them. This is how Zimbra implements it and Exchange does it in a similar way, as far as I know.

You are right though, that this primarily is a feature to be implemented in SOGo, but SOGo has already implemented it. The only limitation is that free / busy information will not be available for such accounts (as one cannot search for them), but usually this is exactly what is intended when hiding an account from the GAL. So, being able to hide users from the GAL is just a matter of integrating this feature into mailcow:

According to the SOGo documentation, a domain can have multiple SOGoUserSources, and for each of the sources, the canAuthenticate and isAddressBook flags can be set. So, all that needs to be done is having two sources: one with canAuthenticate = YES and isAddressBook = NO, and another one with canAuthenticate = NO and isAddressBook = YES.

At the moment, the following configuration is created in bootstrap-sogo.sh (${line} is the domain name and ${gal} is YES if the GAL is enabled for this domain and NO if it is disabled for this domain):

                <dict>
                    <key>MailFieldNames</key>
                    <array>
                        <string>aliases</string>
                        <string>ad_aliases</string>
                        <string>ext_acl</string>
                    </array>
                    <key>KindFieldName</key>
                    <string>kind</string>
                    <key>DomainFieldName</key>
                    <string>domain</string>
                    <key>MultipleBookingsFieldName</key>
                    <string>multiple_bookings</string>
                    <key>listRequiresDot</key>
                    <string>NO</string>
                    <key>canAuthenticate</key>
                    <string>YES</string>
                    <key>displayName</key>
                    <string>GAL ${line}</string>
                    <key>id</key>
                    <string>${line}</string>
                    <key>isAddressBook</key>
                    <string>${gal}</string>
                    <key>type</key>
                    <string>sql</string>
                    <key>userPasswordAlgorithm</key>
                    <string>${MAILCOW_PASS_SCHEME}</string>
                    <key>prependPasswordScheme</key>
                    <string>YES</string>
                    <key>viewURL</key>
                    <string>mysql://${DBUSER}:${DBPASS}@%2Fvar%2Frun%2Fmysqld%2Fmysqld.sock/${DBNAME}/_sogo_static_view</string>
                </dict>

In order to support hiding individual users from the GAL, this could be changed like this:

                <dict>
                    <key>MailFieldNames</key>
                    <array>
                        <string>aliases</string>
                        <string>ad_aliases</string>
                        <string>ext_acl</string>
                    </array>
                    <key>KindFieldName</key>
                    <string>kind</string>
                    <key>DomainFieldName</key>
                    <string>domain</string>
                    <key>MultipleBookingsFieldName</key>
                    <string>multiple_bookings</string>
                    <key>listRequiresDot</key>
                    <string>NO</string>
                    <key>canAuthenticate</key>
                    <string>YES</string>
                    <key>displayName</key>
                    <string>Auth ${line}</string>
                    <key>id</key>
                    <string>${line}_auth</string>
                    <key>isAddressBook</key>
                    <string>NO</string>
                    <key>type</key>
                    <string>sql</string>
                    <key>userPasswordAlgorithm</key>
                    <string>${MAILCOW_PASS_SCHEME}</string>
                    <key>prependPasswordScheme</key>
                    <string>YES</string>
                    <key>viewURL</key>
                    <string>mysql://${DBUSER}:${DBPASS}@%2Fvar%2Frun%2Fmysqld%2Fmysqld.sock/${DBNAME}/_sogo_static_view</string>
                </dict>
                <dict>
                    <key>MailFieldNames</key>
                    <array>
                        <string>aliases</string>
                        <string>ad_aliases</string>
                        <string>ext_acl</string>
                    </array>
                    <key>KindFieldName</key>
                    <string>kind</string>
                    <key>DomainFieldName</key>
                    <string>domain</string>
                    <key>MultipleBookingsFieldName</key>
                    <string>multiple_bookings</string>
                    <key>listRequiresDot</key>
                    <string>NO</string>
                    <key>canAuthenticate</key>
                    <string>NO</string>
                    <key>displayName</key>
                    <string>GAL ${line}</string>
                    <key>id</key>
                    <string>${line}_gal</string>
                    <key>isAddressBook</key>
                    <string>YES</string>
                    <key>type</key>
                    <string>sql</string>
                    <key>viewURL</key>
                    <string>mysql://${DBUSER}:${DBPASS}@%2Fvar%2Frun%2Fmysqld%2Fmysqld.sock/${DBNAME}/sogo_gal_view</string>
                </dict>

Obviously, the second section would only be generated when ${gal} is YES.

This new view sogo_gal_view could be created in the bootstrap-sogo.sh script like this:

CREATE VIEW sogo_gal_view (c_uid, domain, c_name, c_password, c_cn, mail, aliases, ad_aliases, ext_acl, kind, multiple_bookings) AS 
SELECT
   mailbox.username,
   mailbox.domain,
   mailbox.username,
   IF(JSON_UNQUOTE(JSON_VALUE(attributes, '$.force_pw_update')) = '0', IF(JSON_UNQUOTE(JSON_VALUE(attributes, '$.sogo_access')) = 1, password, '{SSHA256}A123A123A321A321A321B321B321B123B123B321B432F123E321123123321321'), '{SSHA256}A123A123A321A321A321B321B321B123B123B321B432F123E321123123321321'),
   mailbox.name,
   mailbox.username,
   IFNULL(GROUP_CONCAT(ga.aliases ORDER BY ga.aliases SEPARATOR ' '), ''),
   IFNULL(gda.ad_alias, ''),
   IFNULL(external_acl.send_as_acl, ''),
   mailbox.kind,
   mailbox.multiple_bookings
FROM
   mailbox
   LEFT OUTER JOIN
      grouped_mail_aliases ga
      ON ga.username REGEXP CONCAT('(^|,)', mailbox.username, '($|,)')
   LEFT OUTER JOIN
      grouped_domain_alias_address gda
      ON gda.username = mailbox.username
   LEFT OUTER JOIN
      grouped_sender_acl_external external_acl
      ON external_acl.username = mailbox.username
WHERE
   mailbox.active = '1' AND mailbox.hide_from_gal != '1'
GROUP BY
   mailbox.username;

In order for this to work, we would have to add the column hide_from_gal to the mailbox table. As an alternative to this, we could use something like JSON_VALUE(`attributes`, '$.hide_from_gal') != '1', but I am not sure about the performance of filtering based on an JSON attribute.

Then, the only thing left to do would be adding the necessary code for the UI and database upgrade.

I am willing to contribute the code (though I might need a few pointers for the UI part and DB upgrade code), but I would first like to get some feedback from the mailcow maintainers, whether they are willing to accept such a “hide from GAL” feature into their codebase.

This could also be a first step into the direction of adding support for arbitrary non-mailbox entries to the GAL, as was requested in #773 and in the discussion forum, if desired.

[smarsching]

So, I spend a bit of time and tried this approach on our test system (using the JSON attribute hide_from_gal). This change does indeed hide the users marked this way from the GAL, but they are still found through the search in the sharing dialog. Apparently, SOGo uses the auth sources and not the address books when searching for potential sharing partners, so even users that are not visible in the GAL can be found through the sharing dialog.

Actually, there are two issues for this in the SOGo issue tracker: 4811, 5514

Fixing this is not trivial, because according to the comments in these issues, SOGo will not allow sharing with a user that cannot be found via the search, even if the full username is entered.

In case someone is still interested in only hiding users from the GAL, below is the patch for bootstrap-sogo.sh that I used. When this patch is applied, a user can be hidden by running

UPDATE mailbox SET attributes = JSON_SET(attributes, '$.hide_from_gal', '1') WHERE username = 'myuser@example.com';

in the MariaDB shell. And here is the patch:

diff --git a/data/Dockerfiles/sogo/bootstrap-sogo.sh b/data/Dockerfiles/sogo/bootstrap-sogo.sh
index aa15525c..b24bb56b 100755
--- a/data/Dockerfiles/sogo/bootstrap-sogo.sh
+++ b/data/Dockerfiles/sogo/bootstrap-sogo.sh
@@ -24,7 +24,7 @@ while [[ "${DBV_NOW}" != "${DBV_NEW}" ]]; do
 done
 echo "DB schema is ${DBV_NOW}"

-# Recreate view
+# Recreate auth view
 if [[ "${MASTER}" =~ ^([yY][eE][sS]|[yY])+$ ]]; then
   echo "We are master, preparing sogo_view..."
   mysql --socket=/var/run/mysqld/mysqld.sock -u ${DBUSER} -p${DBPASS} ${DBNAME} -e "DROP VIEW IF EXISTS sogo_view"
@@ -62,7 +62,7 @@ EOF
     if [[ ! -z $(mysql --socket=/var/run/mysqld/mysqld.sock -u ${DBUSER} -p${DBPASS} ${DBNAME} -B -e "SELECT 'OK' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sogo_view'") ]]; then
       VIEW_OK=OK
     else
-      echo "Will retry to setup SOGo view in 3s..."
+      echo "Will retry to setup SOGo auth view in 3s..."
       sleep 3
     fi
   done
@@ -71,7 +71,60 @@ else
     if [[ ! -z $(mysql --socket=/var/run/mysqld/mysqld.sock -u ${DBUSER} -p${DBPASS} ${DBNAME} -B -e "SELECT 'OK' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sogo_view'") ]]; then
       VIEW_OK=OK
     else
-      echo "Waiting for SOGo view to be created by master..."
+      echo "Waiting for SOGo auth view to be created by master..."
+      sleep 3
+    fi
+  done
+fi
+
+# Recreate GAL view
+if [[ "${MASTER}" =~ ^([yY][eE][sS]|[yY])+$ ]]; then
+  echo "We are master, preparing sogo_gal_view..."
+  mysql --socket=/var/run/mysqld/mysqld.sock -u ${DBUSER} -p${DBPASS} ${DBNAME} -e "DROP VIEW IF EXISTS sogo_gal_view"
+  while [[ ${GAL_VIEW_OK} != 'OK' ]]; do
+    mysql --socket=/var/run/mysqld/mysqld.sock -u ${DBUSER} -p${DBPASS} ${DBNAME} << EOF
+CREATE VIEW sogo_gal_view (c_uid, domain, c_name, c_password, c_cn, mail, aliases, ad_aliases, ext_acl, kind, multiple_bookings) AS
+SELECT
+   mailbox.username,
+   mailbox.domain,
+   mailbox.username,
+   '{SSHA256}!',
+   mailbox.name,
+   mailbox.username,
+   IFNULL(GROUP_CONCAT(ga.aliases ORDER BY ga.aliases SEPARATOR ' '), ''),
+   IFNULL(gda.ad_alias, ''),
+   IFNULL(external_acl.send_as_acl, ''),
+   mailbox.kind,
+   mailbox.multiple_bookings
+FROM
+   mailbox
+   LEFT OUTER JOIN
+      grouped_mail_aliases ga
+      ON ga.username REGEXP CONCAT('(^|,)', mailbox.username, '($|,)')
+   LEFT OUTER JOIN
+      grouped_domain_alias_address gda
+      ON gda.username = mailbox.username
+   LEFT OUTER JOIN
+      grouped_sender_acl_external external_acl
+      ON external_acl.username = mailbox.username
+WHERE
+   mailbox.active = '1' AND NOT JSON_CONTAINS(attributes, '"1"', '$.hide_from_gal')
+GROUP BY
+   mailbox.username;
+EOF
+    if [[ ! -z $(mysql --socket=/var/run/mysqld/mysqld.sock -u ${DBUSER} -p${DBPASS} ${DBNAME} -B -e "SELECT 'OK' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sogo_gal_view'") ]]; then
+      GAL_VIEW_OK=OK
+    else
+      echo "Will retry to setup SOGo GAL view in 3s..."
+      sleep 3
+    fi
+  done
+else
+  while [[ ${GAL_VIEW_OK} != 'OK' ]]; do
+    if [[ ! -z $(mysql --socket=/var/run/mysqld/mysqld.sock -u ${DBUSER} -p${DBPASS} ${DBNAME} -B -e "SELECT 'OK' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sogo_gal_view'") ]]; then
+      GAL_VIEW_OK=OK
+    else
+      echo "Waiting for SOGo GAL view to be created by master..."
       sleep 3
     fi
   done
@@ -195,11 +248,11 @@ while read -r line gal
                     <key>canAuthenticate</key>
                     <string>YES</string>
                     <key>displayName</key>
-                    <string>GAL ${line}</string>
+                    <string>Auth ${line}</string>
                     <key>id</key>
-                    <string>${line}</string>
+                    <string>${line}_auth</string>
                     <key>isAddressBook</key>
-                    <string>${gal}</string>
+                    <string>NO</string>
                     <key>type</key>
                     <string>sql</string>
                     <key>userPasswordAlgorithm</key>
@@ -209,6 +262,36 @@ while read -r line gal
                     <key>viewURL</key>
                     <string>mysql://${DBUSER}:${DBPASS}@%2Fvar%2Frun%2Fmysqld%2Fmysqld.sock/${DBNAME}/_sogo_static_view</string>
                 </dict>" >> /var/lib/sogo/GNUstep/Defaults/sogod.plist
+  if [[ "${gal}" = YES ]]; then
+    echo "                <dict>
+                    <key>MailFieldNames</key>
+                    <array>
+                        <string>aliases</string>
+                        <string>ad_aliases</string>
+                        <string>ext_acl</string>
+                    </array>
+                    <key>KindFieldName</key>
+                    <string>kind</string>
+                    <key>DomainFieldName</key>
+                    <string>domain</string>
+                    <key>MultipleBookingsFieldName</key>
+                    <string>multiple_bookings</string>
+                    <key>listRequiresDot</key>
+                    <string>NO</string>
+                    <key>canAuthenticate</key>
+                    <string>NO</string>
+                    <key>displayName</key>
+                    <string>GAL ${line}</string>
+                    <key>id</key>
+                    <string>${line}_gal</string>
+                    <key>isAddressBook</key>
+                    <string>YES</string>
+                    <key>type</key>
+                    <string>sql</string>
+                    <key>viewURL</key>
+                    <string>mysql://${DBUSER}:${DBPASS}@%2Fvar%2Frun%2Fmysqld%2Fmysqld.sock/${DBNAME}/sogo_gal_view</string>
+                </dict>" >> /var/lib/sogo/GNUstep/Defaults/sogod.plist
+  fi
   # Generate alternative LDAP authentication dict, when SQL authentication fails
   # This will nevertheless read attributes from LDAP
   line=${line} envsubst < /etc/sogo/plist_ldap >> /var/lib/sogo/GNUstep/Defaults/sogod.plist

If you do not want to rebuild the SOGo docker image, you have to bind-mount the patched version of bootstrap-sogo.sh into the sogo-mailcow container at /bootstrap-sogo.sh.

[andryyy]

That’s not just an auth view. Wondering why you changed it.

[smarsching]

I changed it to have a better differentiation between the existing view and the new GAL view. It was only after testing the changes that I realized that this view is not just used for authentication (the SOGo documentation was a bit vague in this regard).

Using a term like “users” would probably be better than “auth”. However, I decided that due to the mailboxes still being visible through the “sharing” search, I wouldn’t keep the changes and rather go with the “separate domain” approach for now. So, I didn’t bother changing it later and just dumped the changes as they were into the comment, just in case someone else is interested in using them.