What would a "properly designed" schema for roles and permissions look like? 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.
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..