Postgresql – What would a “properly designed” schema for roles and permissions look like

database-designpostgresqlschema

I'm creating an application that has a similar roles and permissions system to Discord.

Basically

Users are members of groups
Groups have roles
Roles have permissions
And users can be associated with one role at a time.

I'm not sure how to design a system like this. Originally, I was thinking I'd have a roles table, and for each permission there would be a boolean column like: can_change_nickname.

That doesn't seem right though…

If you have any recommended reading, or can offer an answer with details on why it's done that way, I'd really appreciate that.

A USER is a member of 1 or more GROUPS
A GROUP can have 1 or more ROLES
A ROLE can have 1 or more PERMISSIONS
A USER can only get PERMISSION by being assigned a ROLE with those PERMISSIONS.
Every GROUP will share common permissions based on what features and content types the application has made available
Different ROLES can share the same PERMISSIONS.

So it looks like I'll need a table for both ROLES and PERMISSIONS. Now I just need to find what the PERMISSIONS table would look like.

Best Answer

Let's start with GROUPS. In general, I use plural for table names unless there is a collective noun that works. You may have a different preference.

CREATE TABLE GROUPS
( GROUP_ID ... NOT NULL PRIMARY KEY
, ... ) ;

Since a user can be a member of several groups (and I assume that a group can contain more than 1 user), we need a n-n relationship. This is usually implemented via a junction table:

CREATE TABLE USERS
( USER_ID ... NOT NULL PRIMARY KEY
, ... ) ;

CREATE TABLE USER_GROUPS
( USER_ID ... NOT NULL
      REFERENCES USERS (USER_ID)
, GROUP_ID ... NOT NULL
      REFERENCES GROUPS (GROUP_ID)
,   PRIMARY KEY (USER_ID, GROUP_ID)
);

Next we have PERMISSIONS:

CREATE TABLE PERMISSIONS
( PERMISSION_ID ... NOT NULL PRIMARY KEY
, ...
);

Since a permission can exists for several roles, we once again use a n-n relatyionship table:

CREATE TABLE ROLES
( ROLE_ID ... NOT NULL PRIMARY KEY
, ...);

CREATE TABLE ROLE_PERMISSIONS
( ROLE_ID ... NOT NULL
      REFERENCES ROLES (ROLE_ID)
, PERMISSION_ID ... NOT NULL   
      REFERENCES PERMISSIONS (PERMISSION_ID)
,   PRIMARY KEY (ROLE_ID, PERMISSION_ID)
);

Finally we can describe the relationship between groups and roles. If I got it right that is once again an n-n relationship:

CREATE TABLE GROUP_ROLES
( ROLE_ID ... NOT NULL
      REFERENCES ROLES (ROLE_ID)
, GROUP_ID ... NOT NULL   
      REFERENCES GROUPS (GROUP_ID)
,   PRIMARY KEY (ROLE_ID, GROUP_ID)
);

This is of course just a sketch. I invented attribute names blindly, if there is an attribute name that exists in reality, use that..

Related Solutions

Mysql – User roles and permissions

The simplest structure would be a PERMISSIONS table like this:

create table PERMISSIONS
( Site_ID int
, User_ID int
, Role char(1)
, primary key (Site_ID, User_ID, Role) -- scenario 1
-- OR:
, primary key (Site_ID, User_ID) -- scenario 2
)

Use values for Role such as:

A = Administrator
P = Publisher
E = Editor

How many rows you need depends on how your code interprets the data. You could have very simple code which interprets each permission strictly at face value. To do an administrative task, the user would need an 'A' permission. To do a publishing task, they would need a 'P' permission, etc. In this case an administrator would need three records per site (one each of A, P, E). Similarly publishers would need two records per site and editors only one. There is a risk in this approach that data consistency errors could creep in, like someone could be a publisher, but not an editor (by virtue of the 'E' record being missing).

Alternatively, you could make your code more complex, in which case each user only needs one permission record per site. In this scenario, administrative tasks require an 'A' permission, publishing requires an 'A' or a 'P' and editing requires 'A', 'P', or 'E'. The advantage of this approach is that there is less data to maintain and you won't have inconsistencies.

Note too that you do not need to have the Users.Parent column. If an administrator has an 'A' permission for a site, that automatically gives them control of all of the users for that site.

Sql-server – How to design a table with relationships to a group of users or individual users

you could use something like this:

create table users (
    userid int not null primary key
  )

create table groups (
    groupid int not null primary key
  )

create table permissions (
    permissionid int not null primary key
  )

create table users_groups (
    userid int not null
  , groupid int not null
  , constraint pk_users_groups primary key clustered (userid, groupid)
  , constraint fk_users_groups_userid foreign key references users(userid)
  , constraint fk_users_groups_groupid foreign key references groups(groupid)
  )

create table users_permissions (
    userid int not null
  , permissionid int not null
  , constraint pk_users_permissions primary key clustered (userid, permissionid)
  , constraint fk_users_permissions_userid foreign key references users(userid)
  , constraint fk_users_permissions_permissionid foreign key references permissions(permissionid)
  )

create table groups_permissions (
    groupid int not null
  , permissionid int not null
  , constraint pk_groups_permissions primary key clustered (groupid, permissionid)
  , constraint fk_groups_permissions_groupid foreign key references groups(groupid)
  , constraint fk_groups_permissions_permissionid foreign key references permissions(permissionid)
  )

and query it with something like this:

select p.permissionid --,p.other_permissions_columns
  from users_permissions up 
    inner join persmissions p on p.permissionid = up.permissionid
  where up.userid = @userid
union 
select p.permissionid --,p.other_permissions_columns
    from users_groups ug 
      inner join groups_permissions gp on gp.groupid = ug.groupid
        and ug.userid = @userid
      inner join persmissions p on p.permissionid = gp.permissionid

Best Answer

Related Solutions

Mysql – User roles and permissions

Sql-server – How to design a table with relationships to a group of users or individual users

Related Question