From: | Tim Smith <randomdev4+postgres(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Help with ltree queries |
Date: | 2014-12-05 11:43:41 |
Message-ID: | CA+HuS5HaMBLM5Q0oJcOUX=t6jswTeoh1BKa0n7sheVL7FFsLDQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-12-05 15:22:04 | Re: Fwd: Fwd: Problem with pg_dump and decimal mark |
Previous Message | Albe Laurenz | 2014-12-05 11:14:57 | Re: (never executed) in the execution plan |