postgresql schema for access control list

The SQL code you provided creates several tables to implement a basic user, group, and permission system. Let's break down each table and its purpose:

1. public.user table:

   • This table stores information about users.
   • Columns:
     • id: A serial primary key that uniquely identifies each user.
     • name: A varchar field to store the name of the user (up to 200 characters).
     • created_at: A timestamp indicating the creation date of the user, with a default value of the current timestamp when the record is created.
     • updated_at: A timestamp indicating the last update date of the user, with a default value of the current timestamp when the record is created or updated.
     • is_active: A boolean field that indicates whether the user is active (True) or inactive (False), with a default value of True.

2. public.group table:

   • This table stores information about groups.
   • Columns:
     • id: A serial primary key that uniquely identifies each group.
     • name: A varchar field to store the name of the group (up to 200 characters).
     • created_at: A timestamp indicating the creation date of the group, with a default value of the current timestamp when the record is created.
     • updated_at: A timestamp indicating the last update date of the group, with a default value of the current timestamp when the record is created or updated.

3. public.permission table:

   • This table stores information about permissions.
   • Columns:
     • id: A serial primary key that uniquely identifies each permission.
     • name: A varchar field to store the name of the permission (up to 200 characters).
     • created_at: A timestamp indicating the creation date of the permission, with a default value of the current timestamp when the record is created.
     • updated_at: A timestamp indicating the last update date of the permission, with a default value of the current timestamp when the record is created or updated.

4. public.usergroups table:

   • This table represents the many-to-many relationship between users and groups, indicating which users belong to which groups.
   • Columns:
     • user_id: A foreign key referencing the id column in the public.user table, representing the user who is a member of the group.
     • group_id: A foreign key referencing the id column in the public.group table, representing the group to which the user belongs.
     • created_at: A timestamp indicating the creation date of the relationship record, with a default value of the current timestamp when the record is created.
     • updated_at: A timestamp indicating the last update date of the relationship record, with a default value of the current timestamp when the record is created or updated.
     • The primary key is a composite key formed by the user_id and group_id columns, ensuring that a user can only belong to a group once.

5. public.userpermissions table:

   • This table represents the many-to-many relationship between users and permissions, indicating which permissions are assigned to which users.
   • Columns:
     • user_id: A foreign key referencing the id column in the public.user table, representing the user to whom the permission is assigned.
     • permission_id: A foreign key referencing the id column in the public.permission table, representing the permission assigned to the user.
     • created_at: A timestamp indicating the creation date of the relationship record, with a default value of the current timestamp when the record is created.
     • updated_at: A timestamp indicating the last update date of the relationship record, with a default value of the current timestamp when the record is created or updated.
     • The primary key is a composite key formed by the user_id and permission_id columns, ensuring that a user can have a specific permission only once.

6. public.grouppermissions table:

   • This table represents the many-to-many relationship between groups and permissions, indicating which permissions are assigned to which groups.
   • Columns:
     • group_id: A foreign key referencing the id column in the public.group table, representing the group to which the permission is assigned.
     • permission_id: A foreign key referencing the id column in the public.permission table, representing the permission assigned to the group.
     • created_at: A timestamp indicating the creation date of the relationship record, with a default value of the current timestamp when the record is created.
     • updated_at: A timestamp indicating the last update date of the relationship record, with a default value of the current timestamp when the record is created or updated.
     • The primary key is a composite key formed by the group_id and permission_id columns, ensuring that a group can have a specific permission only once.

With these tables, you can now create and manage users, groups, and permissions for your system effectively. The use of many-to-many relationship tables (public.usergroups, public.userpermissions, and public.grouppermissions) allows you to assign multiple users to groups and multiple permissions to users and groups easily.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

create table public.user(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
is_active BOOLEAN NOT NULL default True
);

create table public.group(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);

create table public.permission(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);

create table public.usergroups(
user_id int references public.user(id),
group_id int references public.group(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(user_id, group_id)
);

create table public.userpermissions(
user_id int references public.user(id),
permission_id int references public.permission(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(user_id, permission_id)
);

create table public.grouppermissions(
group_id int references public.user(id),
permission_id int references public.permission(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(group_id, permission_id)
);