I'm running Apache James 3.9.0 with the Guice/PostgreSQL configuration,
using Roundcube as the webmail client and its ManageSieve plugin for
managing Sieve filters.
I've encountered what appears to be a bug in the PostgreSQL Sieve
repository implementation. Updating an existing active Sieve script
using PUTSCRIPT causes the script to become inactive.
I've traced the behavior through the ManageSieve protocol logs and the
compiled James 3.9.0 classes.
SYMPTOMS
When a user edits an existing Sieve filter in Roundcube and saves it,
the script contents are successfully updated in PostgreSQL. However, the
corresponding record in the sieve_scripts table changes:
is_active true -> false
activation_date_time timestamp -> NULL
Consequently, none of the user's Sieve filters execute until the script
is reactivated.
This happens when editing an individual filter, not just when creating
or deleting scripts.
MANAGESIEVE PROTOCOL EVIDENCE
Roundcube's ManageSieve debug log shows the following sequence:
C: LISTSCRIPTS
S: "roundcube" ACTIVE
S: OK
C: PUTSCRIPT "roundcube" {1570+}
S: OK
The script was explicitly reported as ACTIVE immediately before the update.
James successfully accepts the replacement script and returns OK, but
afterward the PostgreSQL record indicates that the script is inactive.
Roundcube does not issue a subsequent SETACTIVE command, which I believe
is correct behavior. According to RFC 5804, replacing an existing active
script using PUTSCRIPT should preserve its active status.
ROOT CAUSE IDENTIFIED IN JAMES 3.9.0
I inspected the compiled classes using javap.
The relevant classes are:
james-server-data-postgres-3.9.0.jar
org.apache.james.sieve.postgres.PostgresSieveRepository
org.apache.james.sieve.postgres.PostgresSieveScriptDAO
In PostgresSieveRepository.putScript(), the repository constructs a new
PostgresSieveScript object.
The bytecode contains:
68: iconst_0
69: invokevirtual #81
// PostgresSieveScript$Builder.isActive:(Z)
In other words, the repository explicitly sets:
.isActive(false)
This occurs regardless of whether the script being replaced is currently
active.
The repository then calls:
postgresSieveScriptDAO.upsertScript(script)
Examining PostgresSieveScriptDAO.upsertScript() reveals that it uses a
PostgreSQL INSERT ... ON CONFLICT DO UPDATE operation.
The conflict handler updates the following fields:
script_size
script_content
is_active
activation_date_time
Because the repository constructs the replacement object with
isActive(false) and no activation timestamp, the upsert overwrites the
existing activation information.
The effective SQL behavior is equivalent to:
INSERT INTO sieve_scripts (...)
VALUES (...)
ON CONFLICT (username, script_name)
DO UPDATE SET
script_size = EXCLUDED.script_size,
script_content = EXCLUDED.script_content,
is_active = EXCLUDED.is_active,
activation_date_time = EXCLUDED.activation_date_time;
This explains the observed behavior.
SUGGESTED CORRECTION
I believe the simplest correction would be in
PostgresSieveScriptDAO.upsertScript().
When updating an existing script, the ON CONFLICT DO UPDATE clause
should update only:
.set(SCRIPT_SIZE, script.getScriptSize())
.set(SCRIPT_CONTENT, script.getScriptContent())
It should not overwrite:
.set(IS_ACTIVE, script.isActive())
.set(ACTIVATION_DATE_TIME, script.getActivationDateTime())
This would preserve the existing activation state when replacing a
script, while allowing newly created scripts to default to inactive.
Activation and deactivation would continue to be handled by the existing
SETACTIVE implementation.
QUESTIONS
1. Is this a known issue in James 3.9.0's PostgreSQL Sieve repository?
2. Has it already been corrected in a subsequent James release?
3. Does the proposed change to PostgresSieveScriptDAO.upsertScript()
look appropriate, or is there another reason the activation fields are
deliberately updated during PUTSCRIPT?
For my installation, I'm considering a temporary PostgreSQL trigger to
preserve the activation fields during script-content updates, avoiding a
custom James build.
I can provide additional bytecode output or ManageSieve logs if helpful.
Thanks!
Jerry
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
