LDAP change/specify the BASE DN


#1
  • Used Zammad version: 2.9
  • Used Zammad installation source: (source, package, …) package
  • Operating system: centos

Forgive me if this is still a feature request, but I’m wondering if anyone has been successful at getting more granular with the Base DN, in a update friendly way. We have 461,718 users in LDAP and growing everyday. This has been the one thing that bothers me about Zammad, housing all those users in the database, chewing up disk space and the impact on performance. I only need around a 100 of those users. A simple LDAP query for authentication would be so much nicer than sync…

Anyone have any tricks or will there be more options in the near future?

I’ve found these older posts but checking if there’s anything else or updates.

Thanks!


#2

No idea about the base DN, but might a user filter help? For example, I’m using (&(objectClass=posixAccount)(givenName=*)) which causes Zammad only to import users that have a first name. (That’s admittedly a bit of a hack; I rather wanted to filter by group membership, but our LDAP is a bit crappy and doesn’t have memberOf nor useful user attributes, and filtering by first name was the next best thing. You might have more luck depending on your LDAP structure…?)


#3

Thanks, Martin. User filtering will definitely help!

I’ve assigned user group filters in our test environment following https://confluence.atlassian.com/kb/how-to-write-ldap-search-filters-792496933.html

We’ll see how it goes…


#4

I got a user group filter to work in my test environment.

User Filter Examle:
(&(objectCategory=Person)(sAMAccountName=*)(memberOf=CN=Zammad Users,OU=Global Groups,OU=Department,DC=company,DC=dot,DC=com))

When I enable the filter, scheduler gets angry, LDAP sync gets stuck and also indexjob, causing thousands of jobs to fail.

Following @MrGeneration instructions on [solved] 1772421 background jobs in queue fixed all the failing jobs, took all night to finish reindexing.

So due to my poor initial LDAP config (not Zammads fault! although a LDAP query would be better imo than sync) I have close to 500,000 unwanted users…not sure if expert mode was available when we rolled out Zammad a couple years ago…anyways hoping the delete feature comes out soon since we hoped it would have been released at the end of 2018.

Thanks


#5

If you change your base dn and the user in database can’t be seen by Zammad (because of the new filter) anymore, it will set the user to inactive.

You can use the following code to remove all inactive users (and their tickets).

beware, this deletes data and might hit productive data if not done carefull, have a backup

# Dry-run
User.transaction do
  User.joins(roles: :permissions).where(active: false, roles: { active: true }, permissions: { name: 'ticket.customer', active: true }).where.not(id: 1).find_each do |user|
    puts "Customer #{user.login}/#{user.email} has #{Ticket.where(customer_id: user.id).count} tickets"

    Ticket.where(customer: user).find_each do |ticket|
      puts "  Deleting ticket #{ticket.number}..."
      ticket.destroy
    end

    puts "  Deleting user #{user.login}/#{user.email}..."
    user.destroy
  end

  raise ActiveRecord::Rollback
end

# Actual deletion
User.joins(roles: :permissions).where(active: false, roles: { active: true }, permissions: { name: 'ticket.customer', active: true }).where.not(id: 1).find_each do |user|
  puts "Customer #{user.login}/#{user.email} has #{Ticket.where(customer_id: user.id).count} tickets"

  Ticket.where(customer: user).find_each do |ticket|
    puts "  Deleting ticket #{ticket.number}..."
    ticket.destroy
  end

  puts "  Deleting user #{user.login}/#{user.email}..."
  user.destroy
end

#6

Thanks @MrGeneration , I applied the filter to our production zammad and it’s caused the ldap sync to get stuck.

  • 88117 background jobs in queue
  • Stuck import backend ‘Import::Ldap’ detected. Last update: 2019-04-08 09:25:12 UTC

The activity stream is constantly updating users outside of the filter to inactive, could this be causing LDAP to get stuck?

I’m updated to the latest version of 2.9
4 cores 32gb ram…

I can provide production logs if needed.

Thanks


#7

System is back to normal now :grin: I had to manually stop the ldap sync each time it would get stuck, and restart the zammad service until the job count got down to zero, then the ldap sync finished successfully.

I tried the code in my test zammad to removed users but it failed after a few deletes. Might just wait for an official delete feature, any idea how much longer?

Thanks Zammad team and community, y’all are saints! :pray:


#8

I have a guess but would love to see the output to be correct before giving you commands out of blue :slight_smile:


#9
Loading production environment (Rails 5.1.6.2)
irb(main):001:0> User.joins(roles: :permissions).where(active: false, roles: { active: true }, permissions: { name: 'ticket.customer', active: true }).where.not(id: 1).find_each do |user|
irb(main):002:1*   puts "Customer #{user.login}/#{user.email} has #{Ticket.where(customer_id: user.id).count} tickets"
irb(main):003:1> 
irb(main):004:1*   Ticket.where(customer: user).find_each do |ticket|
irb(main):005:2*     puts "  Deleting ticket #{ticket.number}..."
irb(main):006:2>     ticket.destroy
irb(main):007:2>   end
irb(main):008:1> 
irb(main):009:1*   puts "  Deleting user #{user.login}/#{user.email}..."
irb(main):010:1>   user.destroy
irb(main):011:1> end
Customer xxxxx/xxxxx@xxx.xxx has 0 tickets
  Deleting user xxxxx/xxxxx@wsu.edu...
Group.find(1) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: d215cb11-a847-4273-9532-27d45988c0d8) to DelayedJob(default) with arguments: "User", 438
Group.find(2) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 46dca51c-263b-4397-8373-66c8e797ee27) to DelayedJob(default) with arguments: "User", 438
Group.find(3) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 6262140f-4ad1-4265-ad02-2a0ff7a80ad9) to DelayedJob(default) with arguments: "User", 438
Group.find(4) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 0a15f6f7-5f7c-481c-b032-91bd313f38b8) to DelayedJob(default) with arguments: "User", 438
Group.find(5) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 94624260-2cdc-46d2-af5c-fd408d63815b) to DelayedJob(default) with arguments: "User", 438
Group.find(6) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 6c4328e8-d01b-446f-ab2f-ebf9a8f86915) to DelayedJob(default) with arguments: "User", 438
Group.find(7) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 635e3de6-419a-4860-9c63-2ca34eb252bf) to DelayedJob(default) with arguments: "User", 438
Group.find(8) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: d0f4881d-4af0-4cef-bfad-997f23a9ba74) to DelayedJob(default) with arguments: "User", 438
Group.find(9) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: e4fb3168-f640-43b9-a503-ef615e2550a3) to DelayedJob(default) with arguments: "User", 438
Group.find(10) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 9682cbe4-4d0a-43db-967c-6662b9117fdd) to DelayedJob(default) with arguments: "User", 438
Group.find(11) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: a813108a-a750-47c6-9879-2dc5b45f9499) to DelayedJob(default) with arguments: "User", 438
Group.find(12) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 6a2df706-c58a-465b-83b3-b1c9c7f55faa) to DelayedJob(default) with arguments: "User", 438
Group.find(13) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 29bcc963-0cfc-4218-a7f5-9a0b1705bbc2) to DelayedJob(default) with arguments: "User", 438
Group.find(14) notify TOUCH 2019-04-09 18:54:06 UTC
User.find(438) notify TOUCH 2019-04-09 18:54:06 UTC
Enqueued SearchIndexJob (Job ID: 32b68146-2fee-456b-8d0d-c892def209f1) to DelayedJob(default) with arguments: "User", 438
# curl -X DELETE "http://127.0.0.1:9200/zammad_production/StatsStore/6?pipeline=zammad-attachment"
# 400
ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR:  update or delete on table "users" violates foreign key constraint "fk_rails_5f245fd6a7" on table "tags"
DETAIL:  Key (id)=(438) is still referenced from table "tags".
: DELETE FROM "users" WHERE "users"."id" = $1
        from (irb):10:in `block in irb_binding'
        from (irb):1
irb(main):012:0> 

Hey it’s test and I’ve got a full VM backup, send me out of the blue commands :stuck_out_tongue_winking_eye:

Thanks, Marcel…we all owe u beer :beers:


#10

I prefer being on point hehe. But your error message is quite interesting, it indicates trouble with a tag which is unusual.

I will provide all codes that should ensure there is no foreign key left.
In the above case the last line should be relevant. As you don’t have a mail address there, you can just set u_id=438 (in your special case as this is the bad ID of the affected user)

u_id = User.find_by(email: '<TARGET_CUSTOMER_EMAIL>').id

ActivityStream.where(created_by_id: u_id).update_all(created_by_id: 1)
History.where(created_by_id: u_id).update_all(created_by_id: 1)
Ticket::Article.where(created_by_id: u_id).update_all(created_by_id: 1)
Ticket::Article.where(updated_by_id: u_id).update_all(updated_by_id: 1)
Store.where(created_by_id: u_id).update_all(created_by_id: 1)
StatsStore.where(created_by_id: u_id).update_all(created_by_id: 1)
OnlineNotification.find_by(user_id: u_id).destroy!
Tag.where(created_by_id: u_id).update_all(created_by_id: 1)

If it still nukes you, let me know.

Thanks, if it’s okay, I prefer Rum and Whiskey. :smiley:


#11

No luck, I tried both email and u_id=438…maybe operator error on my part?

Loading production environment (Rails 5.1.6.2)
irb(main):001:0> u_id=438
=> 438
irb(main):002:0> 
irb(main):003:0* ActivityStream.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):004:0> History.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):005:0> Ticket::Article.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):006:0> Ticket::Article.where(updated_by_id: u_id).update_all(updated_by_id: 1)
=> 0
irb(main):007:0> Store.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):008:0> StatsStore.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):009:0> OnlineNotification.find_by(user_id: u_id).destroy!
=> #<OnlineNotification id: 7840, o_id: 475, object_lookup_id: 2, type_lookup_id: 2, user_id: 438, seen: false, updated_by_id: 23, created_by_id: 23, created_at: "2018-08-21 21:28:00", updated_at: "2018-08-21 21:28:00">
irb(main):010:0> Tag.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):011:0> User.joins(roles: :permissions).where(active: false, roles: { active: true }, permissions: { name: 'ticket.customer', active: true }).where.not(id: 1).find_each do |user|
irb(main):012:1*   puts "Customer #{user.login}/#{user.email} has #{Ticket.where(customer_id: user.id).count} tickets"
irb(main):013:1> 
irb(main):014:1*   Ticket.where(customer: user).find_each do |ticket|
irb(main):015:2*     puts "  Deleting ticket #{ticket.number}..."
irb(main):016:2>     ticket.destroy
irb(main):017:2>   end
irb(main):018:1> 
irb(main):019:1*   puts "  Deleting user #{user.login}/#{user.email}..."
irb(main):020:1>   user.destroy
irb(main):021:1> end
Customer xxxxxxx/xxxxxxx@wsu.edu has 0 tickets
  Deleting user xxxxxxx/xxxxxxx@wsu.edu...
Group.find(1) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: c3e9177c-00fb-4f37-bad8-8f0fbd923179) to DelayedJob(default) with arguments: "User", 438
Group.find(2) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 7084264c-63c7-442b-9898-cca98c49d49d) to DelayedJob(default) with arguments: "User", 438
Group.find(3) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 97119b63-6ec4-427f-ba59-897de669d174) to DelayedJob(default) with arguments: "User", 438
Group.find(4) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: f7bd7467-e9af-46ed-8e52-446d78908ba7) to DelayedJob(default) with arguments: "User", 438
Group.find(5) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 18111a74-6972-4c3e-850b-1753f8f3507f) to DelayedJob(default) with arguments: "User", 438
Group.find(6) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: ddb57887-cb3e-4d39-a7cb-2afcea370c97) to DelayedJob(default) with arguments: "User", 438
Group.find(7) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 756e7e37-28d5-4de0-90d0-f4f03fa61bc0) to DelayedJob(default) with arguments: "User", 438
Group.find(8) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: b4613a38-c9cc-487b-a9f8-ee645e28adb4) to DelayedJob(default) with arguments: "User", 438
Group.find(9) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: f8794f13-2826-4582-80e1-368130a9f4ad) to DelayedJob(default) with arguments: "User", 438
Group.find(10) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 83bed5dc-293b-4705-bdbc-3d61768ae900) to DelayedJob(default) with arguments: "User", 438
Group.find(11) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 6b777c8f-9e1b-45ab-b799-3b4eea15ecc8) to DelayedJob(default) with arguments: "User", 438
Group.find(12) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: ec11468b-474f-4691-950c-2cd11d56e6d5) to DelayedJob(default) with arguments: "User", 438
Group.find(13) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 496ab84b-c2f3-4f95-a7d8-0f7a60555709) to DelayedJob(default) with arguments: "User", 438
Group.find(14) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 02a7a455-0496-4420-98b7-33682b687cba) to DelayedJob(default) with arguments: "User", 438
# curl -X DELETE "http://127.0.0.1:9200/zammad_production/StatsStore/6?pipeline=zammad-attachment"
# 400
ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR:  update or delete on table "users" violates foreign key constraint "fk_rails_939b990649" on table "tickets"
DETAIL:  Key (id)=(438) is still referenced from table "tickets".
: DELETE FROM "users" WHERE "users"."id" = $1
        from (irb):20:in `block in irb_binding'
        from (irb):11
irb(main):022:0> 

This person was an agent at one time, now a inactive customer, but still shows them as part of a group. Their tickets have been deleted. Other inactive customers (never part of any group besides customers) were deleting ok.

:tumbler_glass::yum: