Modelling users and groups - but with multiple types of groups

Question

I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them).

However, its seems I'm dealing with different kinds of groups: some that represent an organization (e.g. a company) and some that represent a role or differing permission levels within an organization (e.g. admin). The organizations represent tenants, or customers, in the business domain.

I currently see two possibilities: (a) create a separate table for organizations, or (b) add a boolean column to the group table called "is_organization" and use it to distinguish the two types.

Is there a best practice for modelling these type of system? Thank you.

Answer 1

Create a Company table. Create/Verify you have a Group table and a GroupCompany table. Those will need populated on addition of a company to the system. You may want to create a template of groups common for a new company. Users that are added to the system are added to a group. Each group is tied to a company by the forementioned GroupCompany table. You can add a GroupType field to categorize company groups across the whole system.

Adding a company to your system helps you scale it across many organizations. It should be standard on most systems you architect. When the requirement comes in that we have a new client but they want all their data reported separately, you are covered.

Good Luck

Answer 2

I would probably not model the users in PostgreSQL. If you do, you're limited to interfacing with apps you create. If you user another solution, like say LDAP, you'll be able to utilize those models on everything from phones to login systems to web apps.

That all said, there is a simpler way than doing what you ask. Because normally in these structures organizations can themselves have sub-organizations...

DROP TABLE role;
CREATE TABLE role (
  rid        serial PRIMARY KEY,
  parentrid  int    REFERENCES role,
  rn         text   CHECK ( rn NOT LIKE '%\.%' ),
  pw         text
);

INSERT INTO role (rn, pw, parentrid) VALUES
  ('stackexchange', DEFAULT, DEFAULT),
  ('jatwood', crypt('microsoftTyrant1', gen_salt('bf',10)), 1);

WITH RECURSIVE t(rid, dn, pw) AS (
  SELECT rid, rn, pw
  FROM role
  WHERE parentrid IS NULL
  UNION ALL
    SELECT role.rid, t.dn||'.'||role.rn, role.pw
    FROM t
    INNER JOIN role
      ON (t.rid = role.parentrid )
)
SELECT *
FROM t;

 rid |          dn           |                              pw                              
-----+-----------------------+--------------------------------------------------------------
   1 | stackexchange         | 
   2 | stackexchange.jatwood | $2a$10$ovH1Bg75Kishth32lwQxpuHNFJgwGDt8mBdj79.BG0Zp3FDb3CGj6
(2 rows)

This will return blowfish encrypted dn/pw table. From there, because we put jatwood under stackexchange he would log in by providing stackexchange.jatwood, (or you would create the login string).

Ok... That takes you to permissions. This has been covered a lot. After you resolve to a rid of matching dn, you need to work out how the permissions to work:

• Should they cascade/inherit from the groups.
• Should they only apply to the users.