[Maria-discuss] postfixadmin and mariadb, want to add support for user 'groups'
Hello all, We have a postfixadmin+mariadb sql database we have been using for many, many years (well, only recently migrated from mysql to mariadb, and it was totally transparent, so thanks for that!). This system is rock solid, and we have no desire to change it. We are in the process of setting up SOGo groupware, amd have run into a stumbling block. Not a show stopper, but it definitely put a heavy burden on me, as the SysAdmin. SOGo can Active Directory just fine for testing Group Memberships for purposes of applying ACLs (for shared calendars, contacts, etc)... We intentionally do not use SSO - we enforce very strong email passwords, but allow much weaker AD computer passwords - so email AUTH happens with our SQL DB. The problem is, SOGo does not have support for using 'Groups' for ACLs when using SQL for the AUTH backend. It also does not (yet) have support for different AUTH and USER DB backends. So, as things stand now, I'm going to have to manually add every single individual userid to every single resource they will need to have access too, rather than just doing this with groups. So, my first questions is, has anyone every come up with a decent schema that provides for managing user groups in the same way as AD groups that scales reasonably well? I'm very dangerous when it comes to this stuff, so can't even attempt this myself, but I have some options available to help get this done, especially if I can get some help/advice with getting started. One thing I do know is the SOGo guy had to create a 'View' to use, so whatever we end up using will have to work inside a View (probably displaying my ignorance to the world with that comment - is pretty much everything in an SQL DB compatible with 'Views'?)... I'd dearly appreciate any help anyone is willing to give... Thanks, Charles
Hi, Tanstaafl! On Mar 09, Tanstaafl wrote:
So, as things stand now, I'm going to have to manually add every single individual userid to every single resource they will need to have access too, rather than just doing this with groups.
This is what SQL standard roles were designed to solve. We have implemented roles in 10.0. You create roles instead of groups, and grant these roles to users. One of the drawbacks - when a connection is established, one needs to do "SET ROLE" to be able to use role's privileges. But one can work around it using --init-connect. For example, with something like this: select concat("set role ", role_name) into @a from information_schema.applicable_roles where grantee = current_user limit 1; prepare s from @a; execute s; This will be fixed properly when we implement default roles (which is a non-standard feature), but it didn't make it into 10.0 version. Regards, Sergei
On 3/10/2014 9:42 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Tanstaafl!
On Mar 09, Tanstaafl wrote:
So, as things stand now, I'm going to have to manually add every single individual userid to every single resource they will need to have access too, rather than just doing this with groups.
This is what SQL standard roles were designed to solve. We have implemented roles in 10.0. You create roles instead of groups, and grant these roles to users.
Hi Sergei, thanks for the reply, but you are misunderstanding. I am *not* talking about *SQL* users - ie, users who will have direct access to and be interacting with the SQL database. I am talking about the EMAIL users that postfixadmin creates and stores in the sql db. This is what I meant when I said 'in the same way as Active Directory (Security) Groups work'... So, I need an SQL schema that allows for the creation of 'Groups', that the email 'accounts' can be made members of, that can then be queried to test if any given email user is a member of any given group. Hopefully that was a little clearer. Charles
Am 10.03.2014 15:34, schrieb Tanstaafl:
On 3/10/2014 9:42 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Tanstaafl!
On Mar 09, Tanstaafl wrote:
So, as things stand now, I'm going to have to manually add every single individual userid to every single resource they will need to have access too, rather than just doing this with groups.
This is what SQL standard roles were designed to solve. We have implemented roles in 10.0. You create roles instead of groups, and grant these roles to users.
Hi Sergei,
thanks for the reply, but you are misunderstanding.
I am *not* talking about *SQL* users - ie, users who will have direct access to and be interacting with the SQL database.
I am talking about the EMAIL users that postfixadmin creates and stores in the sql db.
This is what I meant when I said 'in the same way as Active Directory (Security) Groups work'...
So, I need an SQL schema that allows for the creation of 'Groups', that the email 'accounts' can be made members of, that can then be queried to test if any given email user is a member of any given group.
Hopefully that was a little clearer
that is hardly a mariadb topic you need to ask that on a postfixadmin list
On 3/10/2014 10:38 AM, Reindl Harald <h.reindl@thelounge.net> wrote:
So, I need an SQL schema that allows for the creation of 'Groups', that the email 'accounts' can be made members of, that can then be queried to test if any given email user is a member of any given group.
Hopefully that was a little clearer
that is hardly a mariadb topic you need to ask that on a postfixadmin list
Reindl, Please read my original email. It IS an SQL issue/question, and I happen to be using mariadb, so I thought I'd ask here.
On 3/9/2014 12:10 PM, Tanstaafl <tanstaafl@libertytrek.org> wrote:
The problem is, SOGo does not have support for using 'Groups' for ACLs when using SQL for the AUTH backend. It also does not (yet) have support for different AUTH and USER DB backends.
So, as things stand now, I'm going to have to manually add every single individual userid to every single resource they will need to have access too, rather than just doing this with groups.
So, my first questions is, has anyone every come up with a decent schema that provides for managing user groups in the same way as AD groups that scales reasonably well?
So, apparently no one has ever done or even attempted to do this using SQL? Bummer, was hoping for at least some pointers or ideas on how to structure the schema...
participants (3)
-
Reindl Harald -
Sergei Golubchik -
Tanstaafl