Postgres: Many-to-many columns and multiple columns or columns

Question

I need help developing complex user permissions in a Postgres database. In my Rails application, each user will be able to access a unique set of functions. In other words, there are no predefined "roles" that determine what functions a user can receive.

In almost every controller / view, the application checks if the current user has access to various functions. Ideally, the application will provide ~ 100 different functions and will support 500k + users.

I am currently considering three different options (but welcome alternatives!) And would like to know which option offers the best performance. Thank you in advance for any help / suggestions.

Option 1: Many-to-Many Relationship

By building a many-to-many relationship between a table Userand a table, the Featureapplication can check whether the user has access to this function by querying the connection table.

For example, if there is an entry in the connection table that connects user1 and feature1, then user1 has access to function1.

Option 2: Multiple Columns

An application can represent each function as a boolean column in a table User. This avoids querying multiple tables to check permissions.

For example, if user1.has_feature1true, then user1 has access to function1.

Option 3: array column

(GIN-indexed?) User. , , , .

, user1.features.include? 'feature1' , 1 1.

Answer 1

" " . , .

?

• .
• . ludicris, . , .
• Array. , , . integrety, , , , .
  , , , 500k+. VS CASCADE.

---

class Feature
  has_many :user_features
  has_many :users, through: :user_features
end

class UserFeature
  belongs_to :user
  belongs_to :feature
end

class User
  has_many :user_features
  has_many :features, through: :user_features

  def has_feature?(name)
    features.exist?(name: name)
  end
end