[SQL] storing access rights in a postgres database

[tv]

Hi,

we are developping a web application in which we need to manage access to
several types of objects, the most important objects are 'company', 'projects',
'subproject', 'module' (and several others but that's not important for now).
In general these objects constitute a tree, as for example each company owns
several projects and each project belongs to exactly one company. So it's
relatively easy to store these objects and relations between them as it's a
simple 1:M relationship.

The funny part begins with the access rights on these objects - we want to store
them in a database in such a way to get:

(a) good performance - there will be several hundreds of users and objects etc.

(b) easy administering - all of that will be administered by humans, so it
should be as easy as possible

There'll be two basic types of questions:

(1) Does the user X have an access to the object Y? (i.e. Does the user have
access to the 'project X'?)

(2) To which objects at the level X can the user Y access? (i.e. 'To which
projects does the user have an access?')

The (b) in general means some kind of 'inheritance' is used, that is each node
in the tree inherits the access right from the node above him in case there's
no access right set directly on it. So the first step when deciding 'Does the
user X have an access to 'project Y?' would be to determine whether there's an
access right right on the project, and if not then the same question ('Does he
hava an access?' would be asked for the node above project (a 'firm' for
example).

I came up with a table

CREATE TABLE rights (
   user_id   INT NOT NULL,
   allowed   BOOLEAN NOT NULL,
   firm_id   INT,
   project_idINT,
   subproject_id INT,
   module_id INT
);

Where all the columns are references to the related tables (not important here).
The table is filled from 'left to right' that is if a column is NULL then all
the columns to right from it are NULL as well, thus each row has a meaning of a
path in the tree. For example

INSERT INTO rights(allowed,user_id,firm_id,project_id,subproject_id,module_id)
VALUES ('t',1,4,33,12,24);
INSERT INTO rights(allowed,user_id,firm_id,project_id,subproject_id,module_id)
VALUES ('t',1,4,NULL,NULL,NULL);

are valid rows, while

INSERT INTO rights(allowed,user_id,firm_id,project_id,subproject_id,module_id)
VALUES ('t',1,4,NULL,34,NULL);

is not valid as there's a 'gap' between '4' and '34'.

The question 'Does the user X have an access to object Y?' is then realized by
an SQL query (let the object be a project with id 3, belonging to firm with id
4, and let the user have id 1):

SELECT allowed FROM rights WHERE user_id = 1 AND (
 (firm_id = 4 AND project_id = 3 AND subproject_id IS NULL)
  OR (firm_id = 4 AND project_id IS NULL)
)
ORDER BY firm_id, project_id, subproject_id, module_id, allowed DESC LIMIT 1;

where the 'ORDER BY' clause sorts the results the rows so the most specific are
'at the top' and then choose 't' in prior to 'f'.

The problem is with the second type of queries (all objects the user has access
rights to) as all the ways to find that using SQL are very slow. For example to
get a list of all such projects for user with id 1 we use this:

SELECT id, (
SELECT allowed FROM rights WHERE user_id = 1 AND (
 (firm_id = projects.firm_id AND project_id = projects.id AND
subproject_id IS NULL)
  OR (firm_id = projects.firm_id AND project_id IS NULL)
)
ORDER BY firm_id, project_id, subproject_id, module_id, allowed DESC LIMIT 1
) as allowed
FROM projects;

The problem is in the 'LIMIT 1' clause - that's the reason I can't write that as
a join.

Does someone else has an idea how to solve this? If needed I can send more
complex examples and some testing data, explain plans, etc.

I've been thinking about some 'intermediate table' with results of the
subselect, updated by a set of triggers, but maybe there's some better
solution.

thanks for all your advices
Tomas

PS: We're not granting right directly to users of course - we are using roles,
but it's not necessary here. Just imagine role_id instead of user_id in all the
text.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] i have table

[Andrew Sullivan]

On Mon, Oct 09, 2006 at 11:01:17AM -0500, Aaron Bono wrote:
> On 10/5/06, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
> >you want to insert &c., you put some rules there.
 
> 
> If you do this you need to make the view updateable or
> inserts/updates/deletes will break.

That's what that "some rules" part means.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
When my information changes, I alter my conclusions.  What do you do sir?
--attr. John Maynard Keynes

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] Temp tables and functions

[mark.dingee]

Everyone,

I've written a function (language = sql) which uses a temporary table to 
simplify the process; however, when I go to load the function I get:

/var/lib/pgsql$cat thm.sql | psql test
ERROR:  relation "lost_bus" does not exist

where "lost_bus" is the name of my temp table.  The function is just for a 
report that is run monthly, so I can create a permanent table if necessary, but 
I'd rather not. 

Thanks in advance
Mark

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] optimal insert

[Aaron Bono]

On 10/8/06, Dirk Jagdmann <[EMAIL PROTECTED]> wrote:
Hello experts,I have a database that contains three tables:create table a (id serial primary key,
... -- some more fields not relevant for my question);create table b (id serial primary key,... -- some more fields not relevant for my question);create table a_b (a int not null references a,
b int not null references b);Tables a and b have already been filled with lots of rows. Now myapplication needs to insert the relationship of a to b into table a_band is currently doing it with inserts like the following:
insert into a_b(a,b) values(1,100);insert into a_b(a,b) values(1,200);insert into a_b(a,b) values(1,54);insert into a_b(a,b) values(1,4577);So for a batch of inserts the value of a stays the same, while for by
arbitrary values are inserted. Now I have wondered if PostreSQL offersa smarter way to insert those values? A solution can contains usage ofsome plpgsql code.
 
It depends on your logic.  If you can write a query that selects out the a and b records, there is a smart way:
 
insert into a_b(a, b)
select 1, b.id from b where b.id in (100, 200, 54, 4577);
 
This is not really smart because you already have the id values - the select may diminish your performance rather than help.  But if the select is "smarter" then you don't even need to know what the 
b.id values are:
 
insert into a_b(a, b)
select 1, b.id from b where b.somecolumn = 'somevalue';
 
Hope this helps. 
==   Aaron Bono   Aranya Software Technologies, Inc.
   http://www.aranya.com   http://codeelixir.com== 

Re: [SQL] optimal insert

[George Pavlov]

And don't forget that \COPY and especially COPY are usually much faster
(and, IMHO, easier to compose/maintain) than gobs of INSERTs.


> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Bono
> Sent: Tuesday, October 10, 2006 1:46 PM
> To: Dirk Jagdmann
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] optimal insert
> 
> On 10/8/06, Dirk Jagdmann <[EMAIL PROTECTED]> wrote: 
> 
>   Hello experts,
>   
>   I have a database that contains three tables:
>   
>   create table a (
>   id serial primary key, 
>   ... -- some more fields not relevant for my question
>   );
>   create table b (
>   id serial primary key,
>   ... -- some more fields not relevant for my question
>   );
>   create table a_b (
>   a int not null references a, 
>   b int not null references b
>   );
>   
>   Tables a and b have already been filled with lots of 
> rows. Now my
>   application needs to insert the relationship of a to b 
> into table a_b
>   and is currently doing it with inserts like the following: 
>   
>   insert into a_b(a,b) values(1,100);
>   insert into a_b(a,b) values(1,200);
>   insert into a_b(a,b) values(1,54);
>   insert into a_b(a,b) values(1,4577);
>   
>   So for a batch of inserts the value of a stays the 
> same, while for by 
>   arbitrary values are inserted. Now I have wondered if 
> PostreSQL offers
>   a smarter way to insert those values? A solution can 
> contains usage of
>   some plpgsql code.
> 
>  
> It depends on your logic.  If you can write a query that 
> selects out the a and b records, there is a smart way:
>  
> insert into a_b(a, b)
> select 1, b.id from b where b.id in (100, 200, 54, 4577);
>  
> This is not really smart because you already have the id 
> values - the select may diminish your performance rather than 
> help.  But if the select is "smarter" then you don't even 
> need to know what the b.id   values are:
>  
> insert into a_b(a, b)
> select 1, b.id from b where b.somecolumn = 'somevalue';
>  
> Hope this helps.
>  
> 
>   
> ==
>  Aaron Bono
>  Aranya Software Technologies, Inc. 
>  http://www.aranya.com
>  http://codeelixir.com
>   
> == 
> 
> 

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] optimal insert

[Dirk Jagdmann]

Hello Aaron,

thank you for your suggestion. I will have to think if something
similar would be of any benefit for my data.

--
---> Dirk Jagdmann
> http://cubic.org/~doj
-> http://llg.cubic.org

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] optimal insert

[Dirk Jagdmann]

Hello George,


And don't forget that \COPY and especially COPY are usually much faster
(and, IMHO, easier to compose/maintain) than gobs of INSERTs.


I did not forget, but my application uses embedded SQL (with the epcg
preprocessor) and I don't think it can handle COPYs :(

--
---> Dirk Jagdmann
> http://cubic.org/~doj
-> http://llg.cubic.org

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] storing access rights in a postgres database

[Dirk Jagdmann]

Hello tv,

I think in your case the PostgreSQL array column type really fits
well. I would just add an array of type integer (or whatever your
primary key in your role table is) to your company, project, module,
... tables. Then you can easy check if a role has access to the
project row by checking if the roles primary key is contained in the
role array. And you can easily select over the entire project table
and matching any values in the role array.

Here are some (untested) SQL statements to clarify my suggestion:

create table role (
 id serial primary key,
 name text );
create table project (
 id serial primary key,
 name text,
 roles int[] );
create table company (
 id serial primary key,
 name text,
 roles int[] );
insert into role values(1,'you');
insert into role values(2,'me');
insert into project values(1,'a',{1,2});
insert into project values(2,'b',{2});
-- check if I can access a project
select id from project where name='a' and 2=ANY(roles); -- 2 is 'my' role id
-- get all companies I have access to
select id,name from company where 2=ANY(roles);

I think the foreign key constraints can not be enforced with the
standard foreign key triggers/functions so you would have to write
your own plpgsql triggers if this is a mandatory requirement.

--
---> Dirk Jagdmann
> http://cubic.org/~doj
-> http://llg.cubic.org

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Temp tables and functions

[Jim C. Nasby]

On Tue, Oct 10, 2006 at 03:21:36PM -0400, [EMAIL PROTECTED] wrote:
> Everyone,
> 
> I've written a function (language = sql) which uses a temporary table to 
> simplify the process; however, when I go to load the function I get:
> 
> /var/lib/pgsql$cat thm.sql | psql test
> ERROR:  relation "lost_bus" does not exist
> 
> where "lost_bus" is the name of my temp table.  The function is just for a 
> report that is run monthly, so I can create a permanent table if necessary, 
> but I'd rather not. 

Create the temp table in your script that creates the function. You
don't need to populate it or anything, you just need it to exist
(prefferably with the correct definition).
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly