From: Dirk Jagdmann <jagdmann@gmail.com>
To: tv@fuzzy.cz <tv@fuzzy.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: storing access rights in a postgres database
Date: Tue, 10 Oct 2006 23:57:26 +0200
Message-ID: <5d0f60990610101457w36b0a3f5r9e4110c7194fd14c@mail.gmail.com> (raw)
In-Reply-To: <1160487514.452ba25a936ea@mail.fuzzy.cz>
References: <1160487514.452ba25a936ea@mail.fuzzy.cz>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jagdmann@gmail.com, tv@fuzzy.cz
Subject: Re: storing access rights in a postgres database
In-Reply-To: <5d0f60990610101457w36b0a3f5r9e4110c7194fd14c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox