Help with ltree queries

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general