SMIME meta information valid start/end dates

mnestor · October 26, 2023, 1:56pm

It is great that email addresses are now stored in the table but we have lost the certificate start/end times. Doh!

zammad/zammad/blob/0b29d3c61c088dc0e5a3179d465697a158a95aca/db/migrate/20230726082734_smime_meta_information_table.rb#L27


      
              add_columns(t)
            end
          
            SMIMECertificate.reset_column_information
          end
          
          def remove_columns(t)
            t.remove_index :modulus
            t.remove_index :subject
          
            t.remove :subject, :doc_hash, :not_before_at, :not_after_at
          end
          
          def rename_columns(t)
            t.rename :modulus, :uid
            t.rename :raw, :pem
          end
          
          def add_columns(t)
            if Rails.application.config.db_column_array
              t.column :email_addresses, :string, null: true, array: true

Instead of asking for a way to automagically obtain new certs It would be great if we could get the validity start/end times back into the table.

This information is useful when we need to figure out which certs to find and and load. It is not a wonderful solution but what we are doing is:
Direct DB query to find users that have expired or missing certificates. Then we can take and do our LDAP lookup to get the certs and use the Zammad api to add them.

I have attempted to rewrite this to instead query our LDAP for certs that are new in the last day but that information isn’t there. I already have multiple enhancements open with the group in charge of that for little things like this and negative hope I will get something.

I could just query every user and try to update their cert but that is about 90k…

MrGeneration · October 26, 2023, 2:23pm

Please use the provided template.

fliebe92 · October 27, 2023, 9:32am

Hi @mnestor. Please use the API to fulfill your needs, there is a possibility to get all certificates with the information you need (GET /api/v1/integration/smime/certificate).

Using the database directly should be avoided whenever possible.

mnestor · October 27, 2023, 3:23pm

Since we can’t search based on validity dates. The process is:

Get all the certs (which parses the certs since the fields were dropped)
Filter the certs
Get all the users
Build a new array of users with missing or expired

That is rather heavy. I miss my little direct database join that returned within 10 seconds. Just getting all the certs takes 2 minutes.

tschaefer · October 29, 2023, 3:29pm

The database is definitely the wrong way to go. We “often” alter tables and do migrations. In other words, you can not walk out on the data. Our pretension to the REST API is to stay stable and backward compatible. If changes are needed, we communicate them in the Release Notes or even if possible in advance.

The endpoint my colleague proposed to you is the way to go. There are further endpoints for managing SMIME certificates and keys.

$ rails routes | grep smime
                                       api_v1_integration_smime POST                        /api/v1/integration/smime(.:format)                                                               integration/smime#search
                           api_v1_integration_smime_certificate POST                        /api/v1/integration/smime/certificate(.:format)                                                   integration/smime#certificate_add
                                                                DELETE                      /api/v1/integration/smime/certificate(.:format)                                                   integration/smime#certificate_delete
                                                                GET                         /api/v1/integration/smime/certificate(.:format)                                                   integration/smime#certificate_list
                           api_v1_integration_smime_private_key POST                        /api/v1/integration/smime/private_key(.:format)                                                   integration/smime#private_key_add
                                                                DELETE                      /api/v1/integration/smime/private_key(.:format)                                                   integration/smime#private_key_delete
                                                                GET                         /api/v1/integration/smime/certificate_download/:id(.:format)                                      integration/smime#certificate_download
                                                                GET                         /api/v1/integration/smime/private_key_download/:id(.:format)                                      integration/smime#private_key_download

For the previously named API endpoint, I can think about adding some filtering functionality, so it is e.g. possible to list information on expired-only certificates.

Would that help you?