0

I am designing a multi-tenant database schema and specifically stuck on the Authentication (AuthN) and Authorization (AuthZ) alongside with App subscription. I think I should pretty good after that. My preliminary schema. I haven't found many resources on this even though I believe what I have below is very stereotypical/common.

Note: I am leaving out fields like created_at, updated_at, and tables like EMAIL_VERIFICATON_CODE, PASSWORD_RESET_TOKEN for brevity.

Business Rules:

  • A TENANT represents the highest level grouping. Represents workspace/business unit/project.
  • A USER can have be part of many tenants with a different ROLE at each. (May be an OWNER at one and just a USER on another.
  • OWNER role has permissions to do anything. It is not effected by APP_PERMISSIONS
  • USER role's permission are effected by APP_PERMISSION
  • A TEAM is specific within each TENANT
  • An APP represents a SaaS subscription through Stripe (Ex: inventory app, human resource app).
  • An APP has many APP_PERMISSIONs associated with it. (Ex: create product)
  • A TEAM and USER can have many APP_PERMISSION's.
  • A TEAM is just an easy way to group APP_PERMISSONs to associate with many USER
  • A USER's cumulative permissions are ones directly associated with each USER and TEAM's its on for that specific TENANT.
  • Either OWNER or USERs with invite permission will be able to invite other USERs to the TENANT.

ISSUES Not Resolved

  • How should APP be related to TENANT or USER? How to setup that USER that has the role of OWNER of a TENANT is responsible for the APP subscription. What happens when there are multiple OWNER's in the same tenant?
  • What happens if a USER only has role of USER with one tenant through invite. Then gets kicked out of that tenant? Standalone USER account without any association to a TENANT?
  • In order to solve the above problems, maybe I need to add another ROLE type? ADMIN is not effected by APP_PERMISSION and can do anything. The difference between ADMIN is that OWNER role is just who is responsible for app subscription.
  • I don't want to force all USERs to be an owner of a TENANT
  • Not sure if better way to model APP_PERMISSION and APP_PERMISSION_ACTION, don't feel to good about it. May be action should just be free text and get rid of lookup table APP_PERMISSION_ACTION.
  • Because only USER role is affected by APP_PERMISSION, should I actually model as supertype subtype? But then again, what happens if a USER with role USER has APP_PERMISSIONs then get promoted to OWNER? vice versa? Shouldn't lose the APP_PERMISSION data it had.

USER

  • id UUID PK
  • fullName TEXT NOT NULL
  • email TEXT NOT NULL
  • email_verified BOOLEAN
  • password TEXT NOT NULL

SESSION

  • id TEXT PK
  • user_id UUID NOT NULL(references USER.id)
  • expires_at TIMESTAMPTZ NOT NULL

TENANT

  • id UUID PK
  • name TEXT NOT NULL UNIQUE

ROLE

  • id SMALLSERIAL PK
  • name TEXT NOT NULL UNIQUE Values will be OWNER and USER

TENANT_MEMBERSHIP

  • user_id UUID CPK (references USER.id)
  • role_id SMALLINT CPK(references ROLE.id)
  • tenant_id UUID CPK (references TENANT.id)
  • app_permission_id UUID CPK (references APP_PERMISSION.id)

USER_TEAM_MEMBERSHIP

  • user_id UUID CPK (references USER.id)
  • team_id UUID CPK (references TEAM.id)
  • app_permission_id UUID CPK (references APP_PERMISSION.id)

TEAM

  • id UUID PK
  • name TEXT NOT NULL UNIQUE
  • tenant_id UUID (references TENANT.id)

USER_INVITE

  • user_id UUID CPK (references USER.id)
  • tenant_id UUID CPK (references TENANT.id)
  • expires_at TIMESTAMPTZ NOT NULL

APP (Stripe Product data)

  • id TEXT PK
  • name TEXT NOT NULL UNIQUE
  • ...other stripe data

Values will be Inventory, Human Resources

APP_PERMISSION

  • id SERIAL PK
  • name TEXT NOT NULL UNIQUE
  • app_id TEXT (references APP.id)
  • resource TEXT NOT NULL
  • app_permission_action_id INTEGER (references APP_PERMISSION_ACTION.id)

APP_PERMISSION_ACTION

  • id SERIAL PK
  • name TEXT NOT NULL UNIQUE Values will be create, update, read, delete, invite, etc

APP_SUBSCRIPTION (Stripe Billing Subscription data)

  • id TEXT PK
  • app_id TEXT (references APP.id)
  • ...other stripe data
  • How to relate this?

STRIPE_CUSTOMER (Stripe Customer data)

  • id TEXT PK
  • user_id UUID NOT NULL(references USER.id)
  • ...other stripe data

All entities specific to tenant

TENANT_SPECIFIC_ENTIY

  • id UUID CPK
  • ..other entity
  • tenant_id UUID CPK (references TENANT.id)

1 Answer 1

0

Looking through this, there seems to be some convolution around permissions and roles. Specifically in roles-based authentication, we have users, roles and permissions. Users are given roles, realized through users_roles and roles are affirmatively granted permission through roles_permissions relations.

In your work, the user_roles metaphor is given by TENANT_MEMBERSHIP, but what links ROLE to APP_PERMISSION?

Your first question:

How should APP be related to TENANT or USER? How to setup that USER that has the role of OWNER of a TENANT is responsible for the APP subscription. What happens when there are multiple OWNER's in the same tenant?

In my opinion, the APP/TENANT question boils down to one choice: Do all TENANTs have access to all apps?

  • If yes, then you only need a ROLE_APP_PERMISSION table, and the couplet of (ROLE_ID, APP_PERMISSION_ID) would represent a role bearing a specific permission

  • If no, then will need the a 3-way relation TENANT_ROLE_APP_PERMISSION and the triplet (TENANT_ID, ROLE_ID, APP_PERMISSION_ID) appears to be the correct relation.

Next Question

What happens if a USER only has role of USER with one tenant through invite. Then gets kicked out of that tenant? Standalone USER account without any association to a TENANT?

Similarly, what if a user has all of their ROLES revoked? Well, then they would have no permission, because PERMISSION are given to ROLES, and ROLES are given to USERS.

So, if a user is given ROLES by way of membership in a TENANT, then a USER without a TENANT has no ROLE and thus no PERMISSION.

Question 3:

In order to solve the above problems, maybe I need to add another ROLE type? ADMIN is not effected by APP_PERMISSION and can do anything. The difference between ADMIN is that OWNER role is just who is responsible for app subscription.

No, Do not create an ADMIN role that is "not affected by app permissions". Instead, create an admin role that has all relevant permissions. If you need to distingush between an OWNER AND ADMIN, grant the ADMIN ROLE a greater PERMISSION.

I don't want to force all USERs to be an owner of a TENANT

You should not have to. Rather:

  • A user can have 0 or more TENANTS
  • A user can have 0 or more ROLES

Not sure if better way to model APP_PERMISSION and APP_PERMISSION_ACTION, don't feel to good about it. May be action should just be free text and get rid of lookup table APP_PERMISSION_ACTION.

I am unclear why the APP_PERMISSION_ACTION table has a foreign key relation on itself. Perhaps you can offer more details on the design and intent here.

Because only USER role is affected by APP_PERMISSION, should I actually model as supertype subtype? But then again, what happens if a USER with role USER has APP_PERMISSIONs then get promoted to OWNER? vice versa? Shouldn't lose the APP_PERMISSION data it had.

IMO, you should instead maintain a clear boundary between USERS, ROLES, and PERMISSION and not convolute them with the typing noted above. If a user has both a ROLE and additional permissions, something is awry; Instead of giving a user additional permission, you should give the user additional roles.

That speaks the the idea that how we have divined ROLES may need to be rethought. Perhaps each APP should have 2 roles, APP/OWNER and APP/USER, and the user is granted either or none.

Either way, your application code should ONLY be looking at PERMISSIONS when determining access and control, and NOT making decisions like IF (USER IS ADMIN) ... but prefer IF (USER HAS PERMISSION_XYZ). The PERMISSION should be given to the ROLE and the USER should be granted ROLES

For more on RBAC, see https://en.wikipedia.org/wiki/Role-based_access_control

For more on Discresionary Access Control, see: https://en.wikipedia.org/wiki/Discretionary_access_control

3
  • So I wasn’t going with traditional nomenclature of RBAC. The function of roles is generally to group permissions. But I instead did this with teams. I also got inspiration from AWS permission system. They allow directly assigning a permission to a user, although, they recommend assigning to a role first. Just wanted to handle edge case in someone needed to add perm to user. Permission action was more about bout creating a lookup table for integrity, CRUD operations. But then again, I got thrown a thorn with”invite” an action user
    – CodingBrah
    Mar 26 at 8:10
  • oooh - I missed teams, though that seems to further muddy the definition of roles. moreso than permissions. Mar 26 at 14:07
  • In divining this model, perhaps it is best to start from the other end of the system: Think of oneself as the application developer developing a CRUD endpoint: - How will I express granular permission to a particular resource? How will I then provide (ie migrate SQL) existing roles and/or users with this permission? How will I pair a ROLE to a particular action and do so in a consistent way across the application? Should the permission be expressed in terms of ROLE A can perform ACTION B, or rather than USER A can perform ACTION B? I would opt for the latter as this keeps perm semantics clear Mar 26 at 14:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.