Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#114 SQL 'User Groups'

open
nobody
None
5
2014-02-28
2014-02-28
Charles
No

Hello all,

I'd really like to see if this is possible to accomplish without too much work involved.

I need the ability to create a 'group' in postfixadmin, and add 'users' (email accounts) to them. Users should be able to be members of multiple groups. It would be nice if there was support for nested groups, but that would just be a bonus...

Why I need this...

We are in the process of implementing the SOGo Groupware server. It is really, really slick, but there is one management shortcoming that is really going to make my life much more difficult than it should be.

Their system works perfectly with postfixadmin DB for everything, but, for managing ACLs, they only support Groups using Active Directory integration (ie, Active Directory Security Groups). They don't have any way to create their own SQL based groups.

They did say, however, that if postfixadmin supported SQL based groups (ie, creating a 'Group', and adding email accounts to it), then it would be a simple matter to constructy an SQL Query that would allow their ACL system to support SQL based groups for managing ACLs.

So, the question is, is this a really complex request, or relatively simple?

Thx...

Charles

Related

Feature Requests: #114

Discussion

  • So you basically need additional fields in the mailbox table ("member of group foo", "member of group bar"), right?

    If it's OK for you to define the list of available groups via config.local.php, then I'd recommend to upgrade to svn trunk and to write a $CONF['mailbox_struct_hook'] function (see "x_struct_admin_modify()" in config.inc.php for an example). With this function, you'll automatically get the edit form updated. I also plan to write a list view that adopts to the struct.

    You'll also need to add the fields to the database manually. Field types for boolean are:
    - mysql: tinyint(1) NOT NULL DEFAULT 0
    - postgresql: BOOLEAN NOT NULL DEFAULT 'f'

    If you want this to be fully manageable in the web interface (including creating groups), then you'll probably need to write an own Handler class. That isn't too difficult, but more work than using a mailbox_struct_hook function. The existing Handler classes should give you some inspiration ;-)

     
  • Charles
    Charles
    2014-02-28

    Thanks Christian, but to confirm:

    It appears you are saying I'd need a boolean field for each group? That doesn't seem to workable (having to add a new field every time a new group is created)... I was thinking a new Table (Group_Members or something), that would then contain group names and membership...

    I just had an idea... maybe better would be to combine this request with another one.

    There is an open feature request that you had said 'should be available;, so apparently if it got done by someone else you'd be willing to merge the code.

    The feature request in questions was adding 'custom field' support:

    https://sourceforge.net/p/postfixadmin/feature-requests/49/

    If you would be willing to provide some more detail on how precisely to get this SQL Groups feature done in such a way that the feature for a way to add custom fields was worked into it, that would make sense to a programmer, I have some hours available to me with the SOGo company that I might be able to leverage to get this done, if it doesn't take too long.

    Remember, ianap, just a beggar (and user/lover of wonderful free/open source software like postfixadmin)...

    :)

    Thx Christian,

    Charles

     
  • [quote]It appears you are saying I'd need a boolean field for each group?[/quote]

    Correct, and that's basically the same as feature request 49 contains and what $CONF[*_struct_hook] provides.

    If you want to be able to create groups "on the fly" with the web interface, you'll need to

    a) create something like a XgroupHandler, which uses a table "x_group" with a colum "group name" + the usual stuff (created, modified, active). That's an easy way to get familiar with the *Handler programming. (I'd recommend to start with a copy of AliasdomainHandler.php, which is also quite simple code.)

    b) create a x_group_members table with username, group + the usual stuff

    c) extend MailboxHandler to manage the x_group_members table. AdminHandler does something similar for domain_admin table. You can probably steal some code from it - especially the storemore() and read_from_db_postprocess() functions. Also have a look at initStruct for the needed SQL magic.

    BTW: the x_ prefix is meant to avoid collisions with changes in the official code.

    There's currently no hook etc. to add functions to a class, so you'll have to edit MailboxHandler.php. (If you have a good idea how to solve this without editing MailboxHandler.php, please tell me - I thought about some variants, but nothing looked "good enough"[tm].)

     
  • Charles
    Charles
    2014-03-06

    Thanks Michael, that does indeed sound like what we want/need.

    Christian - I'm really interested in getting this done, and since I have
    someone who will probably be willing and able to do the coding:

    Would you be willing to provide some assistance in the form of advice as
    far as how we should go about doing this to ensure you will be willing
    and able to merge the result into the core code for postfixadmin?

    Basically, the requirement is simply that an SQL View be able to be
    constructed that can be used to determine Group Membership for any given
    user... I think...

    Actually... I'll ask the SOGo guy to define this requirement more
    specifically. I know when we set this up, he said something about
    needing to create a View.

    I'll send a follow-up once they have responded.

    Thanks,

    Charles

    On 2/28/2014 2:12 PM, Michael Neuffer neuffer@neuffer.com wrote:

    Hi Charles,
    below is what I've just send to the list, but still waits for moderator
    approval, unfortunately I don't have time to describe it more nicely
    right now:

    Hi Christian

    I think what he needs is something different:

    2 Tables

    1. Group definitions:
      groups ( group char(20), group_id numeric, enabled boolean )
    2. Group membership definitions
      group_members ( mailbox, group_id numeric )

    Something like this should be enough.

    That way you're flexible and don't have to change config files
    or additional fields in the mailbox table.

    And yes, what I wrote above is horrible to read and no sql, but it
    should show the idea of what needs to be done.

    Cheers
    Mike

    On 28.02.2014 19:16, Charles wrote:

    Thanks Christian, but to confirm:

    It /appears/ you are saying I'd need a boolean field for each group?
    That doesn't seem to workable (having to add a new field every time a
    new group is created)... I was thinking a new Table (Group_Members or
    something), that would then contain group names and membership...

    I just had an idea... maybe better would be to combine this request with
    another one.

    There is an open feature request that you had said 'should be
    available;, so apparently if it got done by someone else you'd be
    willing to merge the code.

    The feature request in questions was adding 'custom field' support:

    https://sourceforge.net/p/postfixadmin/feature-requests/49/

    If you would be willing to provide some more detail on how precisely to
    get this SQL Groups feature done in such a way that the feature for a
    way to add custom fields was worked into it, that would make sense to a
    programmer, I have some hours available to me with the SOGo company that
    I might be able to leverage to get this done, if it doesn't take too long.

    Remember, ianap, just a beggar (and user/lover of wonderful free/open
    source software like postfixadmin)...

    :)

    Thx Christian,

    Charles


    [feature-requests:#114]
    http://sourceforge.net/p/postfixadmin/feature-requests/114/ SQL 'User
    Groups'

    Status: open
    Created: Fri Feb 28, 2014 03:34 PM UTC by Charles
    Last Updated: Fri Feb 28, 2014 05:29 PM UTC
    Owner: nobody

    Hello all,

    I'd really like to see if this is possible to accomplish without too
    much work involved.

    I need the ability to create a 'group' in postfixadmin, and add 'users'
    (email accounts) to them. Users should be able to be members of multiple
    groups. It would be nice if there was support for nested groups, but
    that would just be a bonus...

    Why I need this...

    We are in the process of implementing the SOGo Groupware server. It is
    really, really slick, but there is one management shortcoming that is
    really going to make my life much more difficult than it should be.

    Their system works perfectly with postfixadmin DB for everything, but,
    for managing ACLs, they only support Groups using Active Directory
    integration (ie, Active Directory Security Groups). They don't have any
    way to create their own SQL based groups.

    They did say, however, that if postfixadmin supported SQL based groups
    (ie, creating a 'Group', and adding email accounts to it), then it would
    be a simple matter to constructy an SQL Query that would allow their ACL
    system to support SQL based groups for managing ACLs.

    So, the question is, is this a really complex request, or relatively simple?

    Thx...

    Charles

     

    Related

    Feature Requests: #114