accounts: Store avatars in PostgreSQL

We have some issues with LDAP not handling large uploads very well.
Hence we store any avatars in PostgreSQL which should also help us to
speed up loading LDAP profiles.

Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>

diff --git a/src/backend/accounts.py b/src/backend/accounts.py
index 6310d4d346ec0cdf81e3f0055d80f832ba929e28..be791808fd2a99301e0bcb0fa1907ce8734427d5 100644 (file)
--- a/src/backend/accounts.py
+++ b/src/backend/accounts.py
@@ -1227,18 +1227,16 @@ class Account(LDAPObject):
 
        # Avatar
 
-       @property
+       @lazy_property
        def avatar_hash(self):
-               payload = (
-                       self.uid,
-                       "%s" % self.modified_at,
-               )
+               # Fetch the timestamp (or fall back to the last LDAP change)
+               t = self._fetch_avatar_timestamp() or self.modified_at
 
-               # String the payload together
-               payload = "-".join(payload)
+               # Create the payload
+               payload = "%s-%s" % (self.uid, t)
 
-               # Run MD5() over the payload
-               h = hashlib.new("md5", payload.encode())
+               # Compute a hash over the payload
+               h = hashlib.new("blake2b", payload.encode())
 
                return h.hexdigest()[:7]
 
@@ -1256,7 +1254,12 @@ class Account(LDAPObject):
                return url
 
        async def get_avatar(self, size=None):
-               photo = self._get_bytes("jpegPhoto")
+               # Check the PostgreSQL database
+               photo = self._fetch_avatar()
+
+               # Fall back to LDAP
+               if not photo:
+                       photo = self._get_bytes("jpegPhoto")
 
                # Exit if no avatar is available
                if not photo:
@@ -1266,21 +1269,90 @@ class Account(LDAPObject):
                if size is None:
                        return photo
 
+               # Compose the cache key
+               key = "accounts:%s:avatar:%s:%s" % (self.uid, self.avatar_hash, size)
+
                # Try to retrieve something from the cache
-               avatar = await self.backend.cache.get("accounts:%s:avatar:%s" % (self.dn, size))
+               avatar = await self.backend.cache.get(key)
                if avatar:
                        return avatar
 
                # Generate a new thumbnail
                avatar = util.generate_thumbnail(photo, size, square=True)
 
-               # Save to cache for 15m
-               await self.backend.cache.set("accounts:%s:avatar:%s" % (self.dn, size), avatar, 900)
+               # Save to cache for 24h
+               await self.backend.cache.set(key, avatar, 86400)
 
                return avatar
 
-       def upload_avatar(self, avatar):
-               self._set("jpegPhoto", avatar)
+       def _fetch_avatar(self):
+               """
+                       Fetches the original avatar blob as being uploaded by the user
+               """
+               res = self.db.get("""
+                       SELECT
+                               blob
+                       FROM
+                               account_avatars
+                       WHERE
+                               uid = %s
+                       AND
+                               deleted_at IS NULL
+                       """, self.uid,
+               )
+
+               if res:
+                       return res.blob
+
+       def _fetch_avatar_timestamp(self):
+               res = self.db.get("""
+                       SELECT
+                               created_at
+                       FROM
+                               account_avatars
+                       WHERE
+                               uid = %s
+                       AND
+                               deleted_at IS NULL
+                       """, self.uid,
+               )
+
+               if res:
+                       return res.created_at
+
+       async def upload_avatar(self, avatar):
+               # Remove all previous avatars
+               self.db.execute("""
+                       UPDATE
+                               account_avatars
+                       SET
+                               deleted_at = CURRENT_TIMESTAMP
+                       WHERE
+                               uid = %s
+                       AND
+                               deleted_at IS NULL
+                       """, self.uid,
+               )
+
+               # Store the new avatar in the database
+               self.db.execute("""
+                       INSERT INTO
+                               account_avatars
+                       (
+                               uid,
+                               blob
+                       )
+                       VALUES
+                       (
+                               %s, %s
+                       )
+                       """, self.uid, avatar,
+               )
+
+               # Remove anything in the LDAP database
+               photo = self._get_bytes("jpegPhoto")
+               if photo:
+                       self._delete("jpegPhoto", [photo])
 
        # Consent to promotional emails
 

diff --git a/src/web/users.py b/src/web/users.py
index 246a930070a711672e004755e0a540a5d8047b62..d88051b04eb640236c3eccb3080e215ccc6c8ed6 100644 (file)
--- a/src/web/users.py
+++ b/src/web/users.py
@@ -189,7 +189,7 @@ class EditHandler(base.BaseHandler):
                                if not mimetype.startswith("image/"):
                                        raise tornado.web.HTTPError(400, "Avatar is not an image file: %s" % mimetype)
 
-                               account.upload_avatar(data)
+                               await account.upload_avatar(data)
                        except TypeError:
                                pass