Users and User Groups

Data Model

• Users
• User Groups
• User Group Membership

Users

The user table stores authentication information of the users. It is also used to automate database roles management in PostgreSQL. When a user is created in the user table, the following operations are triggered:

• A new user user_<id> is created in the pg_roles table.
• The selected role is granted to the new user user_<id> in the pg_roles table.
• The default Public user group user_group_0 is granted to the new user user_<id> in the pg_roles table.

Table: user

Attribute	Type	Description
id	Integer	Unique identifier of the user, used as a primary key.
email	Text	E-mail of the user, must be unique.
password	Text	Encrypted password of the user.
role	Text	Role of the user as defined in roles & permissions page (standard, advanced, admin).
flag_active	Boolean	Boolean value to indicate if the user is active or inactive. Inactive users cannot login to ListOf (WIP).
created_date	Timestamp	Record created date.
updated_date	Timestamp	Record last updated date.
created_by_id	Integer	Foreign key of the user table, to indicate which user created the record.
updated_by_id	Integer	Foreign key of the user table, to indicate which user updated the record.

User Groups

User groups define a set of users who can access a common scope of lists. User groups are used to manage row level security on the data, which means users can only see the lists which belong to their user groups. When a user group is created in the user_group table, a new user group user_group_<id> is created in the pg_roles table.

Table: user_group

Attribute	Type	Description
id	Integer	Unique identifier of the user, used as a primary key.
name	Text	Name of the user group, must be unique.
created_date	Timestamp	Record created date.
updated_date	Timestamp	Record last updated date.
created_by_id	Integer	Foreign key of the user table, to indicate which user created the record.
updated_by_id	Integer	Foreign key of the user table, to indicate which user updated the record.

User Group Membership

The table user_group_membership is used to manage the membership of users to user groups. When a user is linked to a user group in the user_group_membership table, the user group user_group_<id> is automatically granted to the user user_<id> in the pg_roles table.

Table: user_group_user

Attribute	Type	Description
id	Integer	Unique identifier of the user, used as a primary key.
created_date	Timestamp	Record created date.
created_by_id	Integer	Foreign key of the user table, to indicate which user created the record.
user_group_id	Integer	Foreign key of the user_group table, to indicate to which user group the user belongs to.
user_id	Integer	Foreign key of the user table, to indicate to which user is assigned to the user group.