0

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.

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Tnqu :) option number 2 seems like the right choice... After posting the question i had an idea that i could make each of the tables inherit the users table and store the users there, but soon realised that i would have to migrate the data about the user in case i want to change the role he belongs to

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.