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.

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)
);