Update group modification time when adding or removing users from groups

Co-authored-by: nitnelave <796633+nitnelave@users.noreply.github.com>

Cargo.lock

@@ -2729,6 +2729,7 @@ dependencies = [
  "lldap_domain_handlers",
  "lldap_domain_model",
  "lldap_ldap",
+ "lldap_opaque_handler",
  "lldap_sql_backend_handler",
  "lldap_test_utils",
  "lldap_validation",

app/src/infra/attributes.rs

@@ -13,7 +13,12 @@ pub mod group {
             "creation_date" => Some(AttributeDescription {
                 attribute_identifier: name,
                 attribute_name: "creationdate",
-                aliases: vec![name, "createtimestamp", "modifytimestamp"],
+                aliases: vec![name, "createtimestamp"],
+            }),
+            "modified_date" => Some(AttributeDescription {
+                attribute_identifier: name,
+                attribute_name: "modifydate",
+                aliases: vec![name, "modifytimestamp"],
             }),
             "display_name" => Some(AttributeDescription {
                 attribute_identifier: name,
@@ -60,7 +65,17 @@ pub mod user {
             "creation_date" => Some(AttributeDescription {
                 attribute_identifier: name,
                 attribute_name: "creationdate",
-                aliases: vec![name, "createtimestamp", "modifytimestamp"],
+                aliases: vec![name, "createtimestamp"],
+            }),
+            "modified_date" => Some(AttributeDescription {
+                attribute_identifier: name,
+                attribute_name: "modifydate",
+                aliases: vec![name, "modifytimestamp"],
+            }),
+            "password_modified_date" => Some(AttributeDescription {
+                attribute_identifier: name,
+                attribute_name: "passwordmodifydate",
+                aliases: vec![name, "pwdchangedtime"],
             }),
             "display_name" => Some(AttributeDescription {
                 attribute_identifier: name,

crates/domain-model/src/model/groups.rs

@@ -14,6 +14,7 @@ pub struct Model {
     pub lowercase_display_name: String,
     pub creation_date: chrono::NaiveDateTime,
     pub uuid: Uuid,
+    pub modified_date: chrono::NaiveDateTime,
 }
 
 #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
@@ -39,6 +40,7 @@ impl From<Model> for lldap_domain::types::Group {
             uuid: group.uuid,
             users: vec![],
             attributes: Vec::new(),
+            modified_date: group.modified_date,
         }
     }
 }
@@ -51,6 +53,7 @@ impl From<Model> for lldap_domain::types::GroupDetails {
             creation_date: group.creation_date,
             uuid: group.uuid,
             attributes: Vec::new(),
+            modified_date: group.modified_date,
         }
     }
 }

crates/domain-model/src/model/users.rs

@@ -21,6 +21,8 @@ pub struct Model {
     pub totp_secret: Option<String>,
     pub mfa_type: Option<String>,
     pub uuid: Uuid,
+    pub modified_date: chrono::NaiveDateTime,
+    pub password_modified_date: chrono::NaiveDateTime,
 }
 
 impl EntityName for Entity {
@@ -40,6 +42,8 @@ pub enum Column {
     TotpSecret,
     MfaType,
     Uuid,
+    ModifiedDate,
+    PasswordModifiedDate,
 }
 
 impl ColumnTrait for Column {
@@ -56,6 +60,8 @@ impl ColumnTrait for Column {
             Column::TotpSecret => ColumnType::String(StringLen::N(64)),
             Column::MfaType => ColumnType::String(StringLen::N(64)),
             Column::Uuid => ColumnType::String(StringLen::N(36)),
+            Column::ModifiedDate => ColumnType::DateTime,
+            Column::PasswordModifiedDate => ColumnType::DateTime,
         }
         .def()
     }
@@ -121,6 +127,8 @@ impl From<Model> for lldap_domain::types::User {
             creation_date: user.creation_date,
             uuid: user.uuid,
             attributes: Vec::new(),
+            modified_date: user.modified_date,
+            password_modified_date: user.password_modified_date,
         }
     }
 }

crates/domain/src/public_schema.rs

@@ -34,6 +34,24 @@ impl From<Schema> for PublicSchema {
                 is_hardcoded: true,
                 is_readonly: true,
             },
+            AttributeSchema {
+                name: "modified_date".into(),
+                attribute_type: AttributeType::DateTime,
+                is_list: false,
+                is_visible: true,
+                is_editable: false,
+                is_hardcoded: true,
+                is_readonly: true,
+            },
+            AttributeSchema {
+                name: "password_modified_date".into(),
+                attribute_type: AttributeType::DateTime,
+                is_list: false,
+                is_visible: true,
+                is_editable: false,
+                is_hardcoded: true,
+                is_readonly: true,
+            },
             AttributeSchema {
                 name: "mail".into(),
                 attribute_type: AttributeType::String,
@@ -85,6 +103,15 @@ impl From<Schema> for PublicSchema {
                 is_hardcoded: true,
                 is_readonly: true,
             },
+            AttributeSchema {
+                name: "modified_date".into(),
+                attribute_type: AttributeType::DateTime,
+                is_list: false,
+                is_visible: true,
+                is_editable: false,
+                is_hardcoded: true,
+                is_readonly: true,
+            },
             AttributeSchema {
                 name: "uuid".into(),
                 attribute_type: AttributeType::String,

crates/domain/src/types.rs

@@ -546,6 +546,8 @@ pub struct User {
     pub creation_date: NaiveDateTime,
     pub uuid: Uuid,
     pub attributes: Vec<Attribute>,
+    pub modified_date: NaiveDateTime,
+    pub password_modified_date: NaiveDateTime,
 }
 
 #[cfg(feature = "test")]
@@ -559,6 +561,8 @@ impl Default for User {
             creation_date: epoch,
             uuid: Uuid::from_name_and_date("", &epoch),
             attributes: Vec::new(),
+            modified_date: epoch,
+            password_modified_date: epoch,
         }
     }
 }
@@ -654,6 +658,7 @@ pub struct Group {
     pub uuid: Uuid,
     pub users: Vec<UserId>,
     pub attributes: Vec<Attribute>,
+    pub modified_date: NaiveDateTime,
 }
 
 #[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
@@ -663,6 +668,7 @@ pub struct GroupDetails {
     pub creation_date: NaiveDateTime,
     pub uuid: Uuid,
     pub attributes: Vec<Attribute>,
+    pub modified_date: NaiveDateTime,
 }
 
 #[derive(Debug, Clone, PartialEq, Eq)]

crates/graphql-server/Cargo.toml

@@ -55,20 +55,24 @@ version = "1"
 mockall = "0.11.4"
 pretty_assertions = "1"
 
-#[dev-dependencies.lldap_auth]
+[dev-dependencies.lldap_auth]
-#path = "../auth"
+path = "../auth"
-#features = ["test"]
+features = ["test"]
-#
+
-#[dev-dependencies.lldap_opaque_handler]
+[dev-dependencies.lldap_domain]
-#path = "../opaque-handler"
+path = "../domain"
-#features = ["test"]
+features = ["test"]
+
+[dev-dependencies.lldap_opaque_handler]
+path = "../opaque-handler"
+features = ["test"]
 
 [dev-dependencies.lldap_test_utils]
 path = "../test-utils"
-#
+
-#[dev-dependencies.lldap_sql_backend_handler]
+[dev-dependencies.lldap_sql_backend_handler]
-#path = "../sql-backend-handler"
+path = "../sql-backend-handler"
-#features = ["test"]
+features = ["test"]
 
 [dev-dependencies.tokio]
 features = ["full"]

crates/graphql-server/src/query.rs

@@ -716,6 +716,8 @@ impl<Handler: BackendHandler> AttributeValue<Handler> {
                 let value: Option<DomainAttributeValue> = match attribute_schema.name.as_str() {
                     "user_id" => Some(user.user_id.clone().into_string().into()),
                     "creation_date" => Some(user.creation_date.into()),
+                    "modified_date" => Some(user.modified_date.into()),
+                    "password_modified_date" => Some(user.password_modified_date.into()),
                     "mail" => Some(user.email.clone().into_string().into()),
                     "uuid" => Some(user.uuid.clone().into_string().into()),
                     "display_name" => user.display_name.as_ref().map(|d| d.clone().into()),
@@ -760,6 +762,7 @@ impl<Handler: BackendHandler> AttributeValue<Handler> {
                     match attribute_schema.name.as_str() {
                         "group_id" => (group.id.0 as i64).into(),
                         "creation_date" => group.creation_date.into(),
+                        "modified_date" => group.modified_date.into(),
                         "uuid" => group.uuid.clone().into_string().into(),
                         "display_name" => group.display_name.clone().into_string().into(),
                         _ => panic!("Unexpected hardcoded attribute: {}", attribute_schema.name),
@@ -802,6 +805,7 @@ impl<Handler: BackendHandler> AttributeValue<Handler> {
                     match attribute_schema.name.as_str() {
                         "group_id" => (group.group_id.0 as i64).into(),
                         "creation_date" => group.creation_date.into(),
+                        "modified_date" => group.modified_date.into(),
                         "uuid" => group.uuid.clone().into_string().into(),
                         "display_name" => group.display_name.clone().into_string().into(),
                         _ => panic!("Unexpected hardcoded attribute: {}", attribute_schema.name),
@@ -958,6 +962,7 @@ mod tests {
                 name: "club_name".into(),
                 value: "Gang of Four".to_string().into(),
             }],
+            modified_date: chrono::Utc.timestamp_nanos(42).naive_utc(),
         });
         groups.insert(GroupDetails {
             group_id: GroupId(7),
@@ -965,6 +970,7 @@ mod tests {
             creation_date: chrono::Utc.timestamp_nanos(12).naive_utc(),
             uuid: lldap_domain::uuid!("b1a2a3a4b1b2c1c2d1d2d3d4d5d6d7d8"),
             attributes: Vec::new(),
+            modified_date: chrono::Utc.timestamp_nanos(12).naive_utc(),
         });
         mock.expect_get_user_groups()
             .with(eq(UserId::new("bob")))
@@ -993,6 +999,14 @@ mod tests {
                             "name": "mail",
                             "value": ["bob@bobbers.on"],
                           },
+                          {
+                            "name": "modified_date",
+                            "value": ["1970-01-01T00:00:00+00:00"],
+                          },
+                          {
+                            "name": "password_modified_date",
+                            "value": ["1970-01-01T00:00:00+00:00"],
+                          },
                           {
                             "name": "user_id",
                             "value": ["bob"],
@@ -1026,6 +1040,10 @@ mod tests {
                                 "name": "group_id",
                                 "value": ["3"],
                               },
+                              {
+                                "name": "modified_date",
+                                "value": ["1970-01-01T00:00:00.000000042+00:00"],
+                              },
                               {
                                 "name": "uuid",
                                 "value": ["a1a2a3a4-b1b2-c1c2-d1d2-d3d4d5d6d7d8"],
@@ -1053,6 +1071,10 @@ mod tests {
                                 "name": "group_id",
                                 "value": ["7"],
                               },
+                              {
+                                "name": "modified_date",
+                                "value": ["1970-01-01T00:00:00.000000012+00:00"],
+                              },
                               {
                                 "name": "uuid",
                                 "value": ["b1a2a3a4-b1b2-c1c2-d1d2-d3d4d5d6d7d8"],
@@ -1246,6 +1268,22 @@ mod tests {
                                     "isEditable": true,
                                     "isHardcoded": true,
                                 },
+                                {
+                                    "name": "modified_date",
+                                    "attributeType": "DATE_TIME",
+                                    "isList": false,
+                                    "isVisible": true,
+                                    "isEditable": false,
+                                    "isHardcoded": true,
+                                },
+                                {
+                                    "name": "password_modified_date",
+                                    "attributeType": "DATE_TIME",
+                                    "isList": false,
+                                    "isVisible": true,
+                                    "isEditable": false,
+                                    "isHardcoded": true,
+                                },
                                 {
                                     "name": "user_id",
                                     "attributeType": "STRING",
@@ -1291,6 +1329,14 @@ mod tests {
                                     "isEditable": false,
                                     "isHardcoded": true,
                                 },
+                                {
+                                    "name": "modified_date",
+                                    "attributeType": "DATE_TIME",
+                                    "isList": false,
+                                    "isVisible": true,
+                                    "isEditable": false,
+                                    "isHardcoded": true,
+                                },
                                 {
                                     "name": "uuid",
                                     "attributeType": "STRING",
@@ -1365,6 +1411,8 @@ mod tests {
                                 {"name": "creation_date"},
                                 {"name": "display_name"},
                                 {"name": "mail"},
+                                {"name": "modified_date"},
+                                {"name": "password_modified_date"},
                                 {"name": "user_id"},
                                 {"name": "uuid"},
                             ],

crates/ldap/src/compare.rs

@@ -124,6 +124,7 @@ mod tests {
                 users: vec![UserId::new("bob")],
                 uuid: uuid!("04ac75e0-2900-3e21-926c-2f732c26b3fc"),
                 attributes: Vec::new(),
+                modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
             }])
         });
         let ldap_handler = setup_bound_admin_handler(mock).await;
@@ -218,6 +219,7 @@ mod tests {
                 users: vec![UserId::new("bob")],
                 uuid: uuid!("04ac75e0-2900-3e21-926c-2f732c26b3fc"),
                 attributes: Vec::new(),
+                modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
             }])
         });
         let ldap_handler = setup_bound_admin_handler(mock).await;

crates/ldap/src/core/group.rs

@@ -72,6 +72,12 @@ pub fn get_group_attribute(
                 .to_rfc3339()
                 .into_bytes(),
         ],
+        GroupFieldType::ModifiedDate => vec![
+            chrono::Utc
+                .from_utc_datetime(&group.modified_date)
+                .to_rfc3339()
+                .into_bytes(),
+        ],
         GroupFieldType::Member => group
             .users
             .iter()
@@ -260,6 +266,10 @@ fn convert_group_filter(
                     code: LdapResultCode::UnwillingToPerform,
                     message: "Creation date filter for groups not supported".to_owned(),
                 }),
+                GroupFieldType::ModifiedDate => Err(LdapError {
+                    code: LdapResultCode::UnwillingToPerform,
+                    message: "Modified date filter for groups not supported".to_owned(),
+                }),
             }
         }
         LdapFilter::And(filters) => Ok(GroupRequestFilter::And(

crates/ldap/src/core/user.rs

@@ -93,6 +93,18 @@ pub fn get_user_attribute(
                 .to_rfc3339()
                 .into_bytes(),
         ],
+        UserFieldType::PrimaryField(UserColumn::ModifiedDate) => vec![
+            chrono::Utc
+                .from_utc_datetime(&user.modified_date)
+                .to_rfc3339()
+                .into_bytes(),
+        ],
+        UserFieldType::PrimaryField(UserColumn::PasswordModifiedDate) => vec![
+            chrono::Utc
+                .from_utc_datetime(&user.password_modified_date)
+                .to_rfc3339()
+                .into_bytes(),
+        ],
         UserFieldType::Attribute(attr, _, _) => get_custom_attribute(&user.attributes, &attr)?,
         UserFieldType::NoMatch => match attribute.as_str() {
             "1.1" => return None,

crates/ldap/src/core/utils.rs

@@ -239,9 +239,15 @@ pub fn map_user_field(field: &AttributeName, schema: &PublicSchema) -> UserField
             AttributeType::JpegPhoto,
             false,
         ),
-        "creationdate" | "createtimestamp" | "modifytimestamp" | "creation_date" => {
+        "creationdate" | "createtimestamp" | "creation_date" => {
             UserFieldType::PrimaryField(UserColumn::CreationDate)
         }
+        "modifytimestamp" | "modifydate" | "modified_date" => {
+            UserFieldType::PrimaryField(UserColumn::ModifiedDate)
+        }
+        "pwdchangedtime" | "passwordmodifydate" | "password_modified_date" => {
+            UserFieldType::PrimaryField(UserColumn::PasswordModifiedDate)
+        }
         "entryuuid" | "uuid" => UserFieldType::PrimaryField(UserColumn::Uuid),
         _ => schema
             .get_schema()
@@ -257,6 +263,7 @@ pub enum GroupFieldType {
     GroupId,
     DisplayName,
     CreationDate,
+    ModifiedDate,
     ObjectClass,
     Dn,
     // Like Dn, but returned as part of the attributes.
@@ -272,9 +279,8 @@ pub fn map_group_field(field: &AttributeName, schema: &PublicSchema) -> GroupFie
         "entrydn" => GroupFieldType::EntryDn,
         "objectclass" => GroupFieldType::ObjectClass,
         "cn" | "displayname" | "uid" | "display_name" | "id" => GroupFieldType::DisplayName,
-        "creationdate" | "createtimestamp" | "modifytimestamp" | "creation_date" => {
+        "creationdate" | "createtimestamp" | "creation_date" => GroupFieldType::CreationDate,
-            GroupFieldType::CreationDate
+        "modifytimestamp" | "modifydate" | "modified_date" => GroupFieldType::ModifiedDate,
-        }
         "member" | "uniquemember" => GroupFieldType::Member,
         "entryuuid" | "uuid" => GroupFieldType::Uuid,
         "group_id" | "groupid" => GroupFieldType::GroupId,

crates/ldap/src/delete.rs

@@ -154,6 +154,7 @@ mod tests {
                     uuid: uuid!("a1a2a3a4b1b2c1c2d1d2d3d4d5d6d7d8"),
                     users: Vec::new(),
                     attributes: Vec::new(),
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 }])
             });
         mock.expect_delete_group()
@@ -284,6 +285,7 @@ mod tests {
                     uuid: uuid!("a1a2a3a4b1b2c1c2d1d2d3d4d5d6d7d8"),
                     users: Vec::new(),
                     attributes: Vec::new(),
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 }])
             });
         mock.expect_delete_group()

crates/ldap/src/handler.rs

@@ -398,6 +398,7 @@ pub mod tests {
                     creation_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                     uuid: uuid!("a1a2a3a4b1b2c1c2d1d2d3d4d5d6d7d8"),
                     attributes: Vec::new(),
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 });
                 Ok(set)
             });

crates/ldap/src/modify.rs

@@ -158,6 +158,7 @@ mod tests {
                         creation_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                         uuid: uuid!("a1a2a3a4b1b2c1c2d1d2d3d4d5d6d7d8"),
                         attributes: Vec::new(),
+                        modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                     });
                 }
                 Ok(g)

crates/ldap/src/password.rs

@@ -263,6 +263,7 @@ pub mod tests {
                     creation_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                     uuid: uuid!("a1a2a3a4b1b2c1c2d1d2d3d4d5d6d7d8"),
                     attributes: Vec::new(),
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 });
                 Ok(set)
             });
@@ -520,6 +521,7 @@ pub mod tests {
             creation_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
             uuid: uuid!("a1a2a3a4b1b2c1c2d1d2d3d4d5d6d7d8"),
             attributes: Vec::new(),
+            modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
         });
         mock.expect_get_user_groups()
             .with(eq(UserId::new("bob")))

crates/ldap/src/search.rs

@@ -291,12 +291,12 @@ pub fn make_ldap_subschema_entry(schema: PublicSchema) -> LdapOp {
 }
 
 pub(crate) fn is_root_dse_request(request: &LdapSearchRequest) -> bool {
-    if request.base.is_empty() && request.scope == LdapSearchScope::Base {
+    if request.base.is_empty()
-        if let LdapFilter::Present(attribute) = &request.filter {
+        && request.scope == LdapSearchScope::Base
-            if attribute.eq_ignore_ascii_case("objectclass") {
+        && let LdapFilter::Present(attribute) = &request.filter
-                return true;
+        && attribute.eq_ignore_ascii_case("objectclass")
-            }
+    {
-        }
+        return true;
     }
     false
 }
@@ -636,8 +636,11 @@ mod tests {
                     b"( 10.5 NAME 'first_name' DESC 'LLDAP: builtin attribute' SUP String )"
                         .to_vec(),
                     b"( 10.6 NAME 'mail' DESC 'LLDAP: builtin attribute' SUP String )".to_vec(),
-                    b"( 10.7 NAME 'group_id' DESC 'LLDAP: builtin attribute' SUP Integer )"
+                    b"( 10.7 NAME 'modified_date' DESC 'LLDAP: builtin attribute' SUP DateTime )".to_vec(),
+                    b"( 10.8 NAME 'password_modified_date' DESC 'LLDAP: builtin attribute' SUP DateTime )".to_vec(),
+                    b"( 10.9 NAME 'group_id' DESC 'LLDAP: builtin attribute' SUP Integer )"
                         .to_vec(),
+                    b"( 10.10 NAME 'modified_date' DESC 'LLDAP: builtin attribute' SUP DateTime )".to_vec(),
                 ]
             }
         );
@@ -646,8 +649,8 @@ mod tests {
             LdapPartialAttribute {
                 atype: "objectClasses".to_owned(),
                 vals: vec![
-                    b"( 3.0 NAME ( 'inetOrgPerson' 'posixAccount' 'mailAccount' 'person' 'customUserClass' ) DESC 'LLDAP builtin: a person' STRUCTURAL MUST ( mail $ user_id ) MAY ( avatar $ creation_date $ display_name $ first_name $ last_name $ uuid ) )".to_vec(),
+                    b"( 3.0 NAME ( 'inetOrgPerson' 'posixAccount' 'mailAccount' 'person' 'customUserClass' ) DESC 'LLDAP builtin: a person' STRUCTURAL MUST ( mail $ user_id ) MAY ( avatar $ creation_date $ display_name $ first_name $ last_name $ modified_date $ password_modified_date $ uuid ) )".to_vec(),
-                    b"( 3.1 NAME ( 'groupOfUniqueNames' 'groupOfNames' ) DESC 'LLDAP builtin: a group' STRUCTURAL MUST ( display_name ) MAY ( creation_date $ group_id $ uuid ) )".to_vec(),
+                    b"( 3.1 NAME ( 'groupOfUniqueNames' 'groupOfNames' ) DESC 'LLDAP builtin: a group' STRUCTURAL MUST ( display_name ) MAY ( creation_date $ group_id $ modified_date $ uuid ) )".to_vec(),
                 ]
             }
         );
@@ -735,6 +738,7 @@ mod tests {
                         creation_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                         uuid: uuid!("a1a2a3a4b1b2c1c2d1d2d3d4d5d6d7d8"),
                         attributes: Vec::new(),
+                        modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                     }]),
                 }])
             });
@@ -840,6 +844,14 @@ mod tests {
                             .with_ymd_and_hms(2014, 7, 8, 9, 10, 11)
                             .unwrap()
                             .naive_utc(),
+                        modified_date: Utc
+                            .with_ymd_and_hms(2014, 7, 8, 9, 10, 11)
+                            .unwrap()
+                            .naive_utc(),
+                        password_modified_date: Utc
+                            .with_ymd_and_hms(2014, 7, 8, 9, 10, 11)
+                            .unwrap()
+                            .naive_utc(),
                     },
                     groups: None,
                 },
@@ -974,6 +986,7 @@ mod tests {
                         users: vec![UserId::new("bob"), UserId::new("john")],
                         uuid: uuid!("04ac75e0-2900-3e21-926c-2f732c26b3fc"),
                         attributes: Vec::new(),
+                        modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                     },
                     Group {
                         id: GroupId(3),
@@ -982,6 +995,7 @@ mod tests {
                         users: vec![UserId::new("john")],
                         uuid: uuid!("04ac75e0-2900-3e21-926c-2f732c26b3fc"),
                         attributes: Vec::new(),
+                        modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                     },
                 ])
             });
@@ -1072,6 +1086,7 @@ mod tests {
                     users: vec![UserId::new("bob"), UserId::new("john")],
                     uuid: uuid!("04ac75e0-2900-3e21-926c-2f732c26b3fc"),
                     attributes: Vec::new(),
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 }])
             });
         let ldap_handler = setup_bound_admin_handler(mock).await;
@@ -1122,6 +1137,7 @@ mod tests {
                     users: vec![],
                     uuid: uuid!("04ac75e0-2900-3e21-926c-2f732c26b3fc"),
                     attributes: Vec::new(),
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 }])
             });
         let ldap_handler = setup_bound_admin_handler(mock).await;
@@ -1193,6 +1209,7 @@ mod tests {
                     users: vec![],
                     uuid: uuid!("04ac75e0-2900-3e21-926c-2f732c26b3fc"),
                     attributes: Vec::new(),
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 }])
             });
         let ldap_handler = setup_bound_admin_handler(mock).await;
@@ -1244,6 +1261,7 @@ mod tests {
                         name: "Attr".into(),
                         value: "TEST".to_string().into(),
                     }],
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 }])
             });
         mock.expect_get_schema().returning(|| {
@@ -1701,6 +1719,7 @@ mod tests {
                     users: vec![UserId::new("bob"), UserId::new("john")],
                     uuid: uuid!("04ac75e0-2900-3e21-926c-2f732c26b3fc"),
                     attributes: Vec::new(),
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 }])
             });
         let ldap_handler = setup_bound_admin_handler(mock).await;
@@ -1785,6 +1804,7 @@ mod tests {
                     users: vec![UserId::new("bob"), UserId::new("john")],
                     uuid: uuid!("04ac75e0-2900-3e21-926c-2f732c26b3fc"),
                     attributes: Vec::new(),
+                    modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
                 }])
             });
         let ldap_handler = setup_bound_admin_handler(mock).await;
@@ -2045,6 +2065,7 @@ mod tests {
                     name: "club_name".into(),
                     value: "Breakfast Club".to_string().into(),
                 }],
+                modified_date: chrono::Utc.timestamp_opt(42, 42).unwrap().naive_utc(),
             }])
         });
         mock.expect_get_schema().returning(|| {

crates/sql-backend-handler/src/sql_group_backend_handler.rs

@@ -206,6 +206,7 @@ impl GroupBackendHandler for SqlBackendHandler {
             lowercase_display_name: Set(lower_display_name),
             creation_date: Set(now),
             uuid: Set(uuid),
+            modified_date: Set(now),
             ..Default::default()
         };
         Ok(self
@@ -268,10 +269,12 @@ impl SqlBackendHandler {
             .display_name
             .as_ref()
             .map(|s| s.as_str().to_lowercase());
+        let now = chrono::Utc::now().naive_utc();
         let update_group = model::groups::ActiveModel {
             group_id: Set(request.group_id),
             display_name: request.display_name.map(Set).unwrap_or_default(),
             lowercase_display_name: lower_display_name.map(Set).unwrap_or_default(),
+            modified_date: Set(now),
             ..Default::default()
         };
         update_group.update(transaction).await?;

crates/sql-backend-handler/src/sql_migrations.rs

@@ -27,6 +27,8 @@ pub enum Users {
     TotpSecret,
     MfaType,
     Uuid,
+    ModifiedDate,
+    PasswordModifiedDate,
 }
 
 #[derive(DeriveIden, PartialEq, Eq, Debug, Serialize, Deserialize, Clone, Copy)]
@@ -37,6 +39,7 @@ pub(crate) enum Groups {
     LowercaseDisplayName,
     CreationDate,
     Uuid,
+    ModifiedDate,
 }
 
 #[derive(DeriveIden, Clone, Copy)]
@@ -1112,6 +1115,77 @@ async fn migrate_to_v10(transaction: DatabaseTransaction) -> Result<DatabaseTran
     Ok(transaction)
 }
 
+async fn migrate_to_v11(transaction: DatabaseTransaction) -> Result<DatabaseTransaction, DbErr> {
+    let builder = transaction.get_database_backend();
+    // Add modified_date to users table
+    transaction
+        .execute(
+            builder.build(
+                Table::alter().table(Users::Table).add_column(
+                    ColumnDef::new(Users::ModifiedDate)
+                        .date_time()
+                        .not_null()
+                        .default(chrono::Utc::now().naive_utc()),
+                ),
+            ),
+        )
+        .await?;
+
+    // Add password_modified_date to users table
+    transaction
+        .execute(
+            builder.build(
+                Table::alter().table(Users::Table).add_column(
+                    ColumnDef::new(Users::PasswordModifiedDate)
+                        .date_time()
+                        .not_null()
+                        .default(chrono::Utc::now().naive_utc()),
+                ),
+            ),
+        )
+        .await?;
+
+    // Add modified_date to groups table
+    transaction
+        .execute(
+            builder.build(
+                Table::alter().table(Groups::Table).add_column(
+                    ColumnDef::new(Groups::ModifiedDate)
+                        .date_time()
+                        .not_null()
+                        .default(chrono::Utc::now().naive_utc()),
+                ),
+            ),
+        )
+        .await?;
+
+    // Initialize existing users with modified_date and password_modified_date = now
+    let now = chrono::Utc::now().naive_utc();
+    transaction
+        .execute(
+            builder.build(
+                Query::update()
+                    .table(Users::Table)
+                    .value(Users::ModifiedDate, now)
+                    .value(Users::PasswordModifiedDate, now),
+            ),
+        )
+        .await?;
+
+    // Initialize existing groups with modified_date = now
+    transaction
+        .execute(
+            builder.build(
+                Query::update()
+                    .table(Groups::Table)
+                    .value(Groups::ModifiedDate, now),
+            ),
+        )
+        .await?;
+
+    Ok(transaction)
+}
+
 // This is needed to make an array of async functions.
 macro_rules! to_sync {
     ($l:ident) => {
@@ -1142,6 +1216,7 @@ pub(crate) async fn migrate_from_version(
         to_sync!(migrate_to_v8),
         to_sync!(migrate_to_v9),
         to_sync!(migrate_to_v10),
+        to_sync!(migrate_to_v11),
     ];
     assert_eq!(migrations.len(), (LAST_SCHEMA_VERSION.0 - 1) as usize);
     for migration in 2..=last_version.0 {

crates/sql-backend-handler/src/sql_opaque_handler.rs

@@ -197,9 +197,12 @@ impl OpaqueHandler for SqlOpaqueHandler {
         let password_file =
             opaque::server::registration::get_password_file(request.registration_upload);
         // Set the user password to the new password.
+        let now = chrono::Utc::now().naive_utc();
         let user_update = model::users::ActiveModel {
             user_id: ActiveValue::Set(username.clone()),
             password_hash: ActiveValue::Set(Some(password_file.serialize())),
+            password_modified_date: ActiveValue::Set(now),
+            modified_date: ActiveValue::Set(now),
             ..Default::default()
         };
         user_update.update(&self.sql_pool).await?;

crates/sql-backend-handler/src/sql_tables.rs

@@ -9,7 +9,7 @@ pub type DbConnection = sea_orm::DatabaseConnection;
 #[derive(Copy, PartialEq, Eq, Debug, Clone, PartialOrd, Ord, DeriveValueType)]
 pub struct SchemaVersion(pub i16);
 
-pub const LAST_SCHEMA_VERSION: SchemaVersion = SchemaVersion(10);
+pub const LAST_SCHEMA_VERSION: SchemaVersion = SchemaVersion(11);
 
 #[derive(Copy, PartialEq, Eq, Debug, Clone, PartialOrd, Ord)]
 pub struct PrivateKeyHash(pub [u8; 32]);

crates/sql-backend-handler/src/sql_user_backend_handler.rs

@@ -190,11 +190,13 @@ impl SqlBackendHandler {
         request: UpdateUserRequest,
     ) -> Result<()> {
         let lower_email = request.email.as_ref().map(|s| s.as_str().to_lowercase());
+        let now = chrono::Utc::now().naive_utc();
         let update_user = model::users::ActiveModel {
             user_id: ActiveValue::Set(request.user_id.clone()),
             email: request.email.map(ActiveValue::Set).unwrap_or_default(),
             lowercase_email: lower_email.map(ActiveValue::Set).unwrap_or_default(),
             display_name: to_value(&request.display_name),
+            modified_date: ActiveValue::Set(now),
             ..Default::default()
         };
         let mut update_user_attributes = Vec::new();
@@ -325,6 +327,8 @@ impl UserBackendHandler for SqlBackendHandler {
             display_name: to_value(&request.display_name),
             creation_date: ActiveValue::Set(now),
             uuid: ActiveValue::Set(uuid),
+            modified_date: ActiveValue::Set(now),
+            password_modified_date: ActiveValue::Set(now),
             ..Default::default()
         };
         let mut new_user_attributes = Vec::new();
@@ -391,24 +395,70 @@ impl UserBackendHandler for SqlBackendHandler {
 
     #[instrument(skip_all, level = "debug", err, fields(user_id = ?user_id.as_str(), group_id))]
     async fn add_user_to_group(&self, user_id: &UserId, group_id: GroupId) -> Result<()> {
-        let new_membership = model::memberships::ActiveModel {
+        let user_id_owned = user_id.clone();
-            user_id: ActiveValue::Set(user_id.clone()),
+        self.sql_pool
-            group_id: ActiveValue::Set(group_id),
+            .transaction::<_, _, sea_orm::DbErr>(|transaction| {
-        };
+                Box::pin(async move {
-        new_membership.insert(&self.sql_pool).await?;
+                    let new_membership = model::memberships::ActiveModel {
+                        user_id: ActiveValue::Set(user_id_owned),
+                        group_id: ActiveValue::Set(group_id),
+                    };
+                    new_membership.insert(transaction).await?;
+
+                    // Update group modification time
+                    let now = chrono::Utc::now().naive_utc();
+                    let update_group = model::groups::ActiveModel {
+                        group_id: Set(group_id),
+                        modified_date: Set(now),
+                        ..Default::default()
+                    };
+                    update_group.update(transaction).await?;
+
+                    Ok(())
+                })
+            })
+            .await?;
         Ok(())
     }
 
     #[instrument(skip_all, level = "debug", err, fields(user_id = ?user_id.as_str(), group_id))]
     async fn remove_user_from_group(&self, user_id: &UserId, group_id: GroupId) -> Result<()> {
-        let res = model::Membership::delete_by_id((user_id.clone(), group_id))
+        let user_id_owned = user_id.clone();
-            .exec(&self.sql_pool)
+        self.sql_pool
-            .await?;
+            .transaction::<_, _, sea_orm::DbErr>(|transaction| {
-        if res.rows_affected == 0 {
+                Box::pin(async move {
-            return Err(DomainError::EntityNotFound(format!(
+                    let res = model::Membership::delete_by_id((user_id_owned.clone(), group_id))
-                "No such membership: '{user_id}' -> {group_id:?}"
+                        .exec(transaction)
-            )));
+                        .await?;
-        }
+                    if res.rows_affected == 0 {
+                        return Err(sea_orm::DbErr::Custom(format!(
+                            "No such membership: '{user_id_owned}' -> {group_id:?}"
+                        )));
+                    }
+
+                    // Update group modification time
+                    let now = chrono::Utc::now().naive_utc();
+                    let update_group = model::groups::ActiveModel {
+                        group_id: Set(group_id),
+                        modified_date: Set(now),
+                        ..Default::default()
+                    };
+                    update_group.update(transaction).await?;
+
+                    Ok(())
+                })
+            })
+            .await
+            .map_err(|e| match e {
+                sea_orm::TransactionError::Connection(sea_orm::DbErr::Custom(msg)) => {
+                    DomainError::EntityNotFound(msg)
+                }
+                sea_orm::TransactionError::Transaction(sea_orm::DbErr::Custom(msg)) => {
+                    DomainError::EntityNotFound(msg)
+                }
+                sea_orm::TransactionError::Connection(e) => DomainError::DatabaseError(e),
+                sea_orm::TransactionError::Transaction(e) => DomainError::DatabaseError(e),
+            })?;
         Ok(())
     }
 }