Repository: lldap/lldap (language: rust)

Issue:
[BUG] It's possible to add a user several times to the same group by graphql api
**Describe the bug**
It's possible to add a user several times to the same group by graphql api

**To Reproduce**
You can use [bootstrap.sh](https://github.com/lldap/lldap/blob/main/example_configs/bootstrap/bootstrap.md) to quickly deploy lldap instance in the needed state:

1. Download [bootstrap.sh](https://github.com/lldap/lldap/blob/main/example_configs/bootstrap/bootstrap.sh)
2. Create a `docker-compose.yaml` with the next content:

<details><summary> docker-compose.yaml</font></summary> 

```yaml
version: "3"

services:
  lldap:
    image: lldap/lldap:v0.5.0
    volumes:
      - ./bootstrap.sh:/bootstrap/bootstrap.sh
      - ./user.json:/bootstrap/user-configs/user.json
      - ./groups.json:/bootstrap/group-configs/groups.json
    ports:
      - "3890:3890" # For LDAP
      - "17170:17170" # For the web front-end
    environment:
      # envs required for lldap
      - LLDAP_LDAP_USER_EMAIL=admin@hiso.icu
      - LLDAP_LDAP_USER_PASS=changeme
      - LLDAP_LDAP_BASE_DN=dc=example,dc=com

      # envs required for bootstrap.sh
      - LLDAP_URL=http://localhost:17170
      - LLDAP_ADMIN_USERNAME=admin
      - LLDAP_ADMIN_PASSWORD=changeme
      - USER_CONFIGS_DIR=/bootstrap/user-configs
      - GROUP_CONFIGS_DIR=/bootstrap/group-configs
      - DO_CLEANUP=true
```

</details>

3. Create a `groups.json` file with the next content:


<details><summary> groups.json</font></summary> 

```json
{"name": "group-1"}
{"name": "group-2"}
```

</details>

4. Create a `user.json` file with the next content:


<details><summary> user.json</font></summary> 

```json
{
  "id": "username",
  "email": "username@example.com",
  "groups": [
    "group-1",
    "group-1",
    "group-1",
    "group-1",
    "group-2"
  ]
}
```

</details>

5. Run the following commands: 

```bash
docker compose up -d
docker compose exec lldap /bootstrap/bootstrap.sh
docker compose exec lldap apk add openldap-clients
docker compose exec lldap ldapsearch -LLL -H ldap://localhost:3890 -D uid=admin,ou=people,dc=example,dc=com -w 'changeme' -b "ou=people,dc=example,dc=com" "uid=username" memberof
```

**Expected behavior**
LLDAP doesn't duplicate `memberof` fields

**Logs**
![image](https://github.com/lldap/lldap/assets/20245745/bf01d4e0-79ff-43b2-8445-9faba822d542)

**Additional context**
I faced this issue when tried to integrate lldap with sftpgo with the option `primary-group-prefix` - to automatically add a user to the group, but because I run the bootstrap script regularly it adds users to the same groups every run, and because of this sftpgo failed to add users because expects that `primary-group-prefix` filter will match only one group.


Discussion / hints:

Fix patch:
diff --git a/server/src/domain/sql_migrations.rs b/server/src/domain/sql_migrations.rs
index b52ffdaf..69d8e33a 100644
--- a/server/src/domain/sql_migrations.rs
+++ b/server/src/domain/sql_migrations.rs
@@ -5,8 +5,8 @@ use crate::domain::{
 use itertools::Itertools;
 use sea_orm::{
     sea_query::{
-        self, all, BlobSize::Blob, ColumnDef, Expr, ForeignKey, ForeignKeyAction, Func, Index,
-        Query, Table, Value,
+        self, all, Alias, BinOper, BlobSize::Blob, ColumnDef, Expr, ForeignKey, ForeignKeyAction,
+        Func, Index, Query, SimpleExpr, Table, Value,
     },
     ConnectionTrait, DatabaseTransaction, DbErr, DeriveIden, FromQueryResult, Iden, Order,
     Statement, TransactionTrait,
@@ -949,6 +949,86 @@ async fn migrate_to_v7(transaction: DatabaseTransaction) -> Result<DatabaseTrans
         .await?;
     Ok(transaction)
 }
+
+async fn migrate_to_v8(transaction: DatabaseTransaction) -> Result<DatabaseTransaction, DbErr> {
+    let builder = transaction.get_database_backend();
+    // Remove duplicate memberships.
+    #[derive(FromQueryResult)]
+    #[allow(dead_code)]
+    struct MembershipInfo {
+        user_id: UserId,
+        group_id: GroupId,
+        cnt: i64,
+    }
+    let mut delete_queries = MembershipInfo::find_by_statement(
+        builder.build(
+            Query::select()
+                .from(Memberships::Table)
+                .columns([Memberships::UserId, Memberships::GroupId])
+                .expr_as(
+                    Expr::count(Expr::col((Memberships::Table, Memberships::UserId))),
+                    Alias::new("cnt"),
+                )
+                .group_by_columns([Memberships::UserId, Memberships::GroupId])
+                .cond_having(all![SimpleExpr::Binary(
+                    Box::new(Expr::col((Memberships::Table, Memberships::UserId)).count()),
+                    BinOper::GreaterThan,
+                    Box::new(SimpleExpr::Value(1.into()))
+                )]),
+        ),
+    )
+    .all(&transaction)
+    .await?
+    .into_iter()
+    .map(
+        |MembershipInfo {
+             user_id,
+             group_id,
+             cnt,
+         }| {
+            builder
+                .build(
+                    Query::delete()
+                        .from_table(Memberships::Table)
+                        .cond_where(all![
+                            Expr::col(Memberships::UserId).eq(user_id),
+                            Expr::col(Memberships::GroupId).eq(group_id)
+                        ])
+                        .limit(cnt as u64 - 1),
+                )
+                .to_owned()
+        },
+    )
+    .peekable();
+    if delete_queries.peek().is_some() {
+        match transaction.get_database_backend() {
+            sea_orm::DatabaseBackend::Sqlite => {
+                return Err(DbErr::Migration(format!(
+                    "The Sqlite driver does not support LIMIT in DELETE. Run these queries manually:\n{}" , delete_queries.map(|s| s.to_string()).join("\n"))));
+            }
+            sea_orm::DatabaseBackend::MySql | sea_orm::DatabaseBackend::Postgres => {
+                for query in delete_queries {
+                    transaction.execute(query).await?;
+                }
+            }
+        }
+    }
+    transaction
+        .execute(
+            builder.build(
+                Index::create()
+                    .if_not_exists()
+                    .name("unique-memberships")
+                    .table(Memberships::Table)
+                    .col(Memberships::UserId)
+                    .col(Memberships::GroupId)
+                    .unique(),
+            ),
+        )
+        .await?;
+    Ok(transaction)
+}
+
 // This is needed to make an array of async functions.
 macro_rules! to_sync {
     ($l:ident) => {
@@ -976,6 +1056,7 @@ pub async fn migrate_from_version(
         to_sync!(migrate_to_v5),
         to_sync!(migrate_to_v6),
         to_sync!(migrate_to_v7),
+        to_sync!(migrate_to_v8),
     ];
     assert_eq!(migrations.len(), (LAST_SCHEMA_VERSION.0 - 1) as usize);
     for migration in 2..=last_version.0 {
diff --git a/server/src/domain/sql_tables.rs b/server/src/domain/sql_tables.rs
index aac10c66..0f518a09 100644
--- a/server/src/domain/sql_tables.rs
+++ b/server/src/domain/sql_tables.rs
@@ -11,7 +11,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(7);
+pub const LAST_SCHEMA_VERSION: SchemaVersion = SchemaVersion(8);
 
 #[derive(Copy, PartialEq, Eq, Debug, Clone, PartialOrd, Ord)]
 pub struct PrivateKeyHash(pub [u8; 32]);


Test patch: