CODEWITHSUNDEEP
databasepostgresqlinheritancedatabase-designpermissions
imposibru
imposibru

Reputation: 11

User permissions database design using PostgreSQL's inheritance

I have a task of creating a database using PostgreSQL. I have to implement user permissions through inheritance. I don't have a clear vision on how to do that. Let's say i have 3 user roles: administrator, moderator and registered user. What i was thinking is to have a users table which would hold standard data about the user himself (first name, last name, email, password...). Table users wold have a FK to a table with the group that a user belongs to (moderator, administrator or registered user) so i know which table to query to get the permissions.

Since administrator and moderator have the permissions that a registered user has i thought that administrator and moderator tables would inherit permissions of the registered user and add their own permissions specific to them. The tables would be there just for the permissions they would not have a relationship with any other tables.

Then later on i would query the user_group table to see which group user belongs to and then query the right table to get the permissions (which would be of type boolean).

But with this approach i don't see a way to restrict a certain permission for a specific user (for example posting comments). I would have to restrict the permission for the whole group.

Any suggestions on the best way to do this would be highly appreciated.

Upvotes: 0

Views: 809

Answers (1)

Alexandru Marina
Alexandru Marina

Reputation: 906

A typical ACL based security schema sounds very much like you described. A User can have one or more Roles, a Role can have permissions (a role seems the same as your group).

The answer to your question pretty much depends on the level of granularity that you need (in a project i have worked on we had the need an extra level to the classical example described above). As i see your example, you have two options:

  1. Assign the permissions directly to users. Doesn't seem the case since you also need the roles (admin, moderator) etc. And if a role has access to posting comments usually you do not want to restrict it to a few users (if you want to do this to more users than maybe its the case for another role)

  2. If you need this though, maybe an exception mechanism can work. A user has a role, BUT there can be a separate table to store the exceptions, the permissions not allowed for him even though it has a certain role

Upvotes: 1

Related Questions