Re: User permissions/Data separation.

Conor,

You're covering a few different areas here. Rather than go into depth
on each one, I'll just point out a few things that came to mind while
reading your post.


On May 20, 2007, at 23:17 , Conor McTernan wrote:

> I'm using Postgres for my web app, I users that interface with the
> database through the app. All records are classified with an industry
> and an occupation. Currently permissions are based around these two
> values, i.e. User John Doe can view all records with industries 1-10
> and occupations 5-50, user Jane Doe can view all records with ALL
> industries and occupations 1-20.
>
> For all the users I am maintaining 2 tables (user_can_see_industry and
> user_can_see_occupation), at the application level I am pulling these
> values out of the database and constructing my queries. I'm finding
> that this approach is not really the best way to manage the
> permissions, but at the same time I'm having trouble figuring out a
> better way.

To put this in more concrete terms, this is the schema I imagine you
have:

CREATE TABLE users
(
     user_id INTEGER PRIMARY KEY
     , user_name text NOT NULL UNIQUE
);

CREATE TABLE industries
(
     industry_id INTEGER PRIMARY KEY
     , industry text NOT NULL UNIQUE
);

CREATE TABLE user_can_see_industry
(
     user_id INTEGER NOT NULL
     , industry_id INTEGER NOT NULL
     , PRIMARY KEY (user_id, industry_id)
);

CREATE TABLE occupations
(
     occupation_id INTEGER PRIMARY KEY
     , occupation text NOT NULL UNIQUE
);

CREATE TABLE user_can_see_occupation
(
     user_id INTEGER NOT NULL
     , industry_id INTEGER NOT NULL
     , PRIMARY KEY (user_id, industry_id)
);

> Users belong to working groups, and while I'd love to implement a view
> managing the permissions for each group this does not really meet the
> business needs of the users (more senior users need to be able to see
> more records than junior users, group scope is constantly changing,
> the scope of each group is not clearly defined (this is probably my
> biggest problem, but that's a different story)).

If you can arrange your working groups into hierarchies, you might
want to look at using nested sets or adjacency lists and connect_by
(included in the tablefunc contrib module).

> I'm also noticing that with my queries constructed the way they are at
> the moment I'm hitting a bit of a performance bottleneck, I'm using a
> very long WHERE/OR statement in each query, which will occasionally
> slow it down.

Here's the crux of your issues, I believe. Without actually seeing
your schema or queries, it's hard to know exactly how to help you
solve them. The pgsql-performance list is a good place to start (and
remember to include EXPLAIN ANALYZE output of your queries :) ) Also,
what version of PostgreSQL are you running? More recent versions have
improvements in handling OR in the WHERE clause. You might also look
into using IN rather than OR. It may have different performance
characteristics.

> What would the benefits be implementing a VIEW for each user, would it
> improve query time at all? When updating their permissions I would
> obviously have to blow away the old view and create a new one, are
> there any negative aspects that approach.

Standard views won't really help from a performance standpoint, as
they're basically a way of naming a query: the result of the query
isn't saved. You might want to look into materialized views. A quick
google brought me to "Materialized Views in PostgreSQL"[1], which you
may find helpful.

> I've thought of using different schema's to limit access, but I'm
> having trouble getting my head around that approach. My understanding
> is that if User A adds a record to Schema A then User B using Schema B
> will not be able to see these records, if this is the case this will
> not fit my needs as users should be able to see other users records so
> long as they have the correct privileges.

Permissions on schemas is similar to permissions on other database
objects in PostgreSQL: it all depends on how you want to set them up.
Permissions in PostgreSQL are based on the role that makes the
connection to the database, so it probably only makes sense to use
schemas if you are setting up a new PostgreSQL role for each
application user. However, I don't think schemas are really going to
help you here: you'd have a lot of duplication of tables across the
schemas and a more complex way of managing the data.

Hopefully this gives you some more options in how you want to
approach this.

Michael Glaesemann
grzm seespotcode net

[1](http://www.jonathangardner.net/PostgreSQL/materialized_views/
matviews.html)