Postgresql – DB Design – Assigning users to multiple sites with different roles

First of all, what type of security model do you plan to implement? Role-based Access Control (RBAC) or Discretionary Access Control (DAC)?

RBAC in the Role-Based Access Control (RBAC) model, access to resources is based on the role assigned to a user. In this model, an administrator assigns a user to a role that has certain predetermined right and privileges. Because of the user's association with the role, the user can access certain resources and perform specific tasks. RBAC is also known as Non-Discretionary Access Control. The roles assigned to users are centrally administered.

DAC In the Discretionary Access Control (DAC) model, access to resources is based on user's identity. A user is granted permissions to a resource by being placed on an access control list (ACL) associated with resource. An entry on a resource's ACL is known as an Access Control Entry (ACE). When a user (or group) is the owner of an object in the DAC model, the user can grant permission to other users and groups. The DAC model is based on resource ownership.

see source

1) In RBAC: you need ElementType table to assign rights to role (users are assigned to role(s)). RBAC defines: "What can this role/user do". Administrator assigns rights for roles and permissions to roles, assigns users to role(s) to access resources. 2) In DAC: users and roles have rights to elements via access control list (ownership). DAC defines: "who has access to my data". User (owner) grants permissions to owned resource.

Any way I suggest this data model:

CREATE TABLE ElementType
(
    Id (PK)
    Name
    ...
)

CREATE TABLE ElementBase
(
    Id (PK)
    Type (FK to ElementType)
    ...
)

(one to one relationship)

CREATE TABLE Element_A
(
    Id (PK, FK to ElementBase)
    ...
)

CREATE TABLE Element_B
(
    Id (PK, FK to ElementBase)
    ...
)

1) RBAC (many-to many relationship)

CREATE TABLE ElementType_To_Role_Rights
(
    RightId (PK)
    RoleId  (FK to Role)
    ElementTypeId (FK to ElementType)
    ...
)

2) DAC (many-to many relationship)

CREATE TABLE ElementBase_To_Actor_Rights
(
    RightId (PK)
    ElementBaseId (FK to ElementBase)
    ActorId (FK to Actor)
    ...
)

CREATE TABLE Actor
(
    Id (PK)
    Name
)

CREATE TABLE User
(
    Id (PK, FK to Actor)
    Password
    ...
)

CREATE TABLE Role
(
    Id (PK, FK to Actor)
    ...
)

The standard way to model permissions for a system is through Role-Based Security. The typical model looks something like this:

The way it works is you have three tangible things: Users, Allowable Actions (the access that is being controlled) and Roles. Roles are groups of both users and allowable actions, therefore there are two intersection tables which record the people in each role and the actions that role permits. Some people include additional information in the ROLE MEMBERSHIP and PERMISSION intersection tables, like when the record was created and by whom. Some people keep that kind of audit log information in a separate table.

In this model every type of user is stored in your USER table. The rules that you have stated for cardinality of users (one owner, one assistant) are frankly very brittle if you ask me. I don't like to cast rules into my data model which are almost certainly going to change. It seems unreasonable to me that a business will use your system if it doesn't allow a manager to delegate their responsibilities for when they are off duty, on vacation or away from the business. Your cardinality rules should be enforced in application logic or with additional tables related to USER if you really feel they need to be in the data model somehow.