Opened 6 years ago

Last modified 5 years ago

#6534 assigned Bug/Something is broken

roundcube default identities are bad

Reported by: https://id.mayfirst.org/dkg Owned by: https://id.mayfirst.org/dkg
Priority: High Component: Tech
Keywords: roundcube red Cc:
Sensitive: no

Description

The first time an MF/PL member logs into roundcube, it looks like they get their "from" address in their default identity set to $USERNAME@mail.mayfirst.org. Unless we can resolve #5588, this is bound to fail when people try to reply to it.

Even if we could resolve #5588, $USERNAME@mail.mayfirst.org is probably not the identity most people want to use.

So, could we figure out a way to autopopulate the identities table with the info we know from red about which e-mail addresses map to which user accounts?

I'm thinking a plugin that would that would do something like the following (maybe upon each login?):

  • connect to the red db with a user account that has severely-limited privileges
  • query the red database for all e-mail addresses set to deliver to the user account
  • for each such address:
    • if no roundcube "identity" with that address exists for the current user, create a new identity with that address.

To get something like this to work, we would probably want a new user account for the red db that can only access a specialized view mapping user accounts to e-mail addresses. Or, even better -- a function which takes a user account as a parameter and return a list of e-mail addresses -- this would prevent a compromised roundcube instance from being able to fully-enumerate all user accounts and e-mail addresses.

Change History (4)

comment:1 Changed 6 years ago by https://id.mayfirst.org/jamie

I think you'll want something like we have with horde (#5883). And since they are both on stallman, you should be able to use the file created by horde (see script to generate user email map).

comment:2 Changed 6 years ago by https://id.mayfirst.org/dkg

I've just cleaned up the user e-mail map generation scripts to avoid a race condition, and to avoid the www-data user actually owning the resulting file. Those fixes are pushed to the puppet git repo.

However, i don't think this solves the problem in a way that helps our members using roundcube the most.

  • it provides only one e-mail address per user account (arbitrarily chosen by the database apparently, since the query in mf-user-email-map-generate has a GROUP BY, but the filed email_address is not declared within an aggregate function) -- our members might have more than one.
  • it allows the www-data user to enumerate all user accounts and all e-mail addresses.

Also, roundcube does not run as the www-data user, so it doesn't currently have access to this table that i don't really want access to anyway :P

Here's what i'm proposing:

in seso:

CREATE PROCEDURE email_addresses_for_user (user_account varchar(255))
  SELECT email_address 
 FROM red_item_email_address JOIN red_item USING (item_id)
 WHERE item_status = 'active' AND
   email_address_recipient = user_account AND
   email_address_recipient NOT LIKE '%@%';
CREATE USER 'roundcube'@'stallman.mayfirst.org' IDENTIFIED BY 'SOMEREALPASSWORD';
CREATE USER 'roundcube-dev'@'stallman.mayfirst.org' IDENTIFIED BY 'SOMEOTHERPASSWORD';
GRANT EXECUTE ON PROCEDURE email_addresses_for_user TO 'roundcube'@'stallman.mayfirst.org';
GRANT EXECUTE ON PROCEDURE email_addresses_for_user TO 'roundcube-dev'@'stallman.mayfirst.org';

as roundcube-code@stallman, put these new credentials into the appropriate config files and ensure that their ACLs are still set properly.

Add a new roundcube plugin (roll it out initially on roundcube.dev.mayfirst.org) that uses these credentials to talk to the remote db server and do (after authentication?):

CALL email_addresses_for_user(%s);

(passing in the authenticated username as %s, of course), and then walk through the responses, setting up a user identity for each one if it doesn't already exist.

I'd be happy to include a configuration checkbox as well so that users can decline this auto-population of default in the future if they choose.

Any objections to this proposal?

comment:3 Changed 6 years ago by https://id.mayfirst.org/ross

  • Owner set to https://id.mayfirst.org/dkg
  • Status changed from new to assigned

Wow dkg this sounds like a great plan! I fully support you moving forward with it.

comment:4 Changed 5 years ago by https://id.mayfirst.org/dskallman

Is there an update on this, it's been a while since the last response.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.