Hi,
I'm trying to use PostgreSQL ltree to make a basic RBAC system as it
seems a sensible thing to do because of the hierarchical parsing ltree
can do !
I currently have the tables below which I've simplified as follows :
/***************
****************
****************
create table app_users (user_id char(64) not null primary key);
create table app_roles (role_id char(64) not null primary key);
create table app_user_role_memberships(
user_id char(64) not null references app_users(user_id) on update
cascade on delete cascade,
role_id char(64) not null references app_roles(role_id) on update
cascade on delete cascade,
primary key (user_id,role_id)
);
create table app_permissions (
perm_id char(64) not null primary key,
perm_label ltree not null);
create table app_role_perm_memberships (
role_id char(64) not null references app_roles(role_id) on update
cascade on delete cascade,
perm_id char(64) not null references app_permissions(perm_id) on
update cascade on delete cascade,
primary key (role_id,perm_id)
);
****************
****************
****************/
The problem I have is whilst I've come up with the model above, I
can't seem to come up with a clean SQL query that doesn't look like
spaghetti !
Is my model correct ? Are there better ways to do this (e.g. ltree[]
instead of ltree) ?
How clean can you make a query to find out whether a given user has
the correct permission for a resource ?
Thanks all !
Tim