From 77cab7da289e1f2f0440ad1ef66d9d0cd9d4064c Mon Sep 17 00:00:00 2001
From: Michael Tremer <michael.tremer@ipfire.org>
Date: Sat, 6 Jan 2024 15:04:40 +0000
Subject: [PATCH] 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>
---
 src/backend/accounts.py | 102 ++++++++++++++++++++++++++++++++++------
 src/web/users.py        |   2 +-
 2 files changed, 88 insertions(+), 16 deletions(-)

diff --git a/src/backend/accounts.py b/src/backend/accounts.py
index 6310d4d3..be791808 100644
--- 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 246a9300..d88051b0 100644
--- 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
 
-- 
2.47.3