SMIME certificate does not work

melody · November 19, 2024, 2:52pm

Used Zammad version: 6.3.1
Operating system: Debian, PostgreSQL

Expected behavior:

Sign e-mails using SMIME.

Actual behavior:

Emails cannot be signed, the button is deactivated. The title tag contains the following error message:

PG::UndefinedFunction: ERROR:  operator does not exist: text[] &amp;&amp; character varying[]
LINE 1: ...&quot; WHERE (((&quot;smime<u>certificates&quot;.&quot;email</u>addresses&quot; &amp;&amp; ARRAY[&#39;...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Steps to reproduce the behavior:

Certificates and private keys are imported via Settings > System > Integrations > S/MIME.

New ticket created, button remains deactivated.

fliebe92 · November 19, 2024, 5:17pm

Hi @melody. Please update your system to the last version of Zammad.

Did you migrate from MySQL to PostgreSQL?

fliebe92 · November 19, 2024, 5:25pm

Maybe you could connect to your psql database (psql <database name> <database user>) and do a \d smime_certificates. Would be interesting.

melody · November 19, 2024, 9:06pm

Yes, we have migrated from MariaDB to PostgreSQL.
I think the Github issue refers to the bug?

github.com/zammad/zammad

Wrong type of table column after migrating from MariaDB to PostgreSQL

opened 03:51PM - 12 Nov 24 UTC

closed 02:50PM - 14 Nov 24 UTC

MarkusHeld

bug verified prioritised by payment migration / update

### Used Zammad Version 6.3 ### Environment - Installation method: possible a…ny - Operating system: possible any - Database + version: psql (PostgreSQL) 13.16 (Debian 13.16-0+deb11u1) ### Actual behaviour When migrating a MariaDB database to Postgresql, the email_address column in the smime_certificates table is saved with an incorrect type. **The following type is set for the column email_addresses in the table smime_certificates in a clean Zammad posgresql system.** ``` email_addresses | character varying[] ``` **but when I do a migration from a MariaDB to postgresql according to our documentation, the following type is set** https://docs.zammad.org/en/pre-release/appendix/migrate-to-postgresql.html ``` email_addresses | text[] ``` **The pgloader program used to migrate the database contains the following entry** ``` $ rake zammad:db:pgloader > /tmp/pgloader-command ``` ``` AFTER LOAD DO $$ alter table smime_certificates alter column email_addresses type text[] using translate(email_addresses::text, '[]', '{}')::text[] $$, $$ alter table pgp_keys alter column email_addresses type text[] using translate(email_addresses::text, '[]', '{}')::text[] $$, $$ alter table public_links alter column screen type text[] using translate(screen::text, '[]', '{}')::text[] $$ ``` **However, if the type of the email_addresses field is text, the following error is displayed when verifying an S/MIME certificate. This means the S/MIME integration does not work and must be deactivated.** ``` [1] pry(main)> SMIMECertificate.find_by_email_address('foobar').first ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: text[] && character varying[] LINE 1: ..." WHERE ((("smime_certificates"."email_addresses" && ARRAY['... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. from /opt/zammad/vendor/bundle/ruby/3.2.0/gems/activerecord-7.1.4.1/lib/active_record/connection_adapters/postgresql_adapter.rb:894:in `exec_params' Caused by PG::UndefinedFunction: ERROR: operator does not exist: text[] && character varying[] LINE 1: ..." WHERE ((("smime_certificates"."email_addresses" && ARRAY['... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. from /opt/zammad/vendor/bundle/ruby/3.2.0/gems/activerecord-7.1.4.1/lib/active_record/connection_adapters/postgresql_adapter.rb:894:in `exec_params' ``` ### Expected behaviour After a migration from MariaDB to Postgresql the S/Mime integration still works in Zammad. ### Steps to reproduce the behaviour 1. Install a system with MySQL/MariaDB backend. 2. Migrate to PostgreSQL by following [the official instructions](https://docs.zammad.org/en/latest/appendix/migrate-to-postgresql.html) (make sure to use `pgloader` utility for DB migration). 3. Run `SMIMECertificate.find_by_email_address('foobar').first` in your Rails console to trigger the error. ### Support Ticket Ticket#10164132 ### I'm sure this is a bug and no feature request or a general question. yes ```[tasklist] ### Tasks - [x] Change column array type to `varchar[]` in `pgloader` script - [x] Add DB migration to update wrong column types on affected systems - [x] Extend migration to include multi select and multi treeselect dynamic array columns in addition to static ones - [x] Tests, tests, tests (DB migration, MySQL/MariaDB simulated migration, `pgloader` script) ```

dominikklein · November 19, 2024, 9:44pm

Yes with this fix the problem should be solved.

dominikklein · November 19, 2024, 9:52pm

Ah sorry, I saw that we currently didn’t backport it to stable.

melody · November 20, 2024, 8:33am

Can you release a stable version in the short term?

melody · November 20, 2024, 10:38am

I have tested it with a new installation (version 6.4).

Each ticket agent has its own email address (agent1@company,com), we receive and send from a global email address (info@company,com).

We have an S/MIME certificate for the global email address (info@company,com). When creating a ticket, the certificate cannot be activated because Zammad requires the ticket agent to have a certificate for its own email address (agent1@company,com).

Does every ticket agent need an S/MIME certificate or can we customize it in the Zammad settings

fliebe92 · November 20, 2024, 8:31pm

We’re working on a solution.

Did you check the official documentation for the S/MIME integration?