Different views with same name for different users

On Wed, Jan 16, 2002 at 02:21:39PM +0100, Harald Massa wrote:

i've got a table of around 10.000 records.

Users A, B, C are allowed to see all the records

user D only some
user E only some others

Which is the most elegant way to do this?
(is there any way at all????)

You cannot create different views with the same name in one PostgreSQL
database. Hence the first answer is: there is no way.

Nevertheless that's not the right answer. You can create only one view:

CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria)

Within criteria you can check the user (`current_user') and return
`true' or `false' depending on the record at hand and the current
user.

--
Holger Krug
hkrug@rationalizer.com

Correct me if i am wrong which is normally the case :-))

But you could create the views as you have said then grant user access
permissions on them. I.e. get the priveldged user to create all the views
and then use the GRANT command to allow users to access them. I.e.

GRANT ALL ON PERS TO user D;

Hope this helps

Darren

Darren Ferguson

On Wed, 16 Jan 2002, Harald Massa wrote:

On Wed, Jan 16, 2002 at 09:59:42AM -0500, Darren Ferguson wrote:

But you could create the views as you have said then grant user access
permissions on them. I.e. get the priveldged user to create all the views
and then use the GRANT command to allow users to access them. I.e.

You forgot: He aimed to create all the different views with one and
the same name. This is impossible in PostgreSQL.

--
Holger Krug
hkrug@rationalizer.com

Harald,

Possible method to implement security that I think matches your requirements
follows, please feel free to comment:-

(
Notes:
------
I have only implemented the 'select' view the model could be easily
expanded to cope with different security permissions.
I have added the notion of user groups for convenience.
You could probably do something similar hooking into the pg_tables,
there are would be pros and cons if you did that.
You would probably want to modify the keys / foreign keys before using
the model (i.e. I've set them all as varchar(50).
)

hih

steve boyle

-- create tables
create table users (
user_id varchar(50),
primary key (user_id)
);

create table items (
item_id varchar(50),
primary key (item_id)
);

create table groups (
group_id varchar(50),
primary key (group_id)
);

create table user_groups (
fk_user_id varchar(50),
fk_group_id varchar(50),
primary key (fk_user_id,fk_group_id)
);

create table item_group_permissions (
fk_group_id varchar(50),
fk_item_id varchar(50),
canselect bool default true,
canupdate bool default false,
candelete bool default false,
primary key (fk_group_id,fk_item_id)
);

-- add foreign key constraints
alter table user_groups add constraint fk_groups_user_groups_group_id
foreign key (fk_group_id)
references groups(group_id)
on update cascade
not deferrable
initially immediate;

alter table user_groups add constraint fk_users_user_groups_user_id
foreign key (fk_user_id)
references users(user_id)
on update cascade
not deferrable
initially immediate;

alter table item_group_permissions add constraint
fk_groups_item_group_permissions_group_id
foreign key (fk_group_id)
references groups(group_id)
on update cascade
not deferrable
initially immediate;

alter table item_group_permissions add constraint
fk_items_item_group_permissions_item_id
foreign key (fk_item_id)
references items(item_id)
on update cascade
not deferrable
initially immediate;

-- insert demo data

insert into items (item_id) values ('item 1');
insert into items (item_id) values ('item 2');
insert into items (item_id) values ('item 3');

insert into users (user_id) values ('user 1');
insert into users (user_id) values ('user 2');
insert into users (user_id) values ('user 3');
insert into users (user_id) values ('user 4');

insert into groups (group_id) values ('group 1');
insert into groups (group_id) values ('group 2');
insert into groups (group_id) values ('group 3');

insert into user_groups(fk_user_id, fk_group_id) values ('user 1', 'group
1');
insert into user_groups(fk_user_id, fk_group_id) values ('user 2', 'group
2');
insert into user_groups(fk_user_id, fk_group_id) values ('user 3', 'group
3');
insert into user_groups(fk_user_id, fk_group_id) values ('user 4', 'group
1');
insert into user_groups(fk_user_id, fk_group_id) values ('user 4', 'group
2');

insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 1', 'item 1', 'T', 'T', 'T');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 2', 'item 2', 'T', 'T', 'T');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 3', 'item 3', 'T', 'T', 'T');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 2', 'item 1', 'T', 'F', 'F');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 3', 'item 1', 'T', 'F', 'F');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 2', 'item 3', 'T', 'F', 'F');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 3', 'item 2', 'T', 'F', 'F');

-- create permission api views

create view usr_select_items as
select distinct items.item_id, item_group_permissions.canselect,
users.user_id
from users inner join
items
inner join
groups
inner join item_group_permissions on
groups.group_id = item_group_permissions.fk_group_id
inner join user_groups
on groups.group_id = user_groups.fk_group_id
on items.item_id = item_group_permissions.fk_item_id
on users.user_id = user_groups.fk_user_id
where
item_group_permissions.canselect='T' and
users.user_id::text=getpgusername()::text
order by items.item_id;

-- Other api permission views could follow

----- Original Message -----
From: "Harald Massa" <HaraldMassa@ghum.de>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, January 16, 2002 1:21 PM
Subject: [GENERAL] Different views with same name for different users

Hello,

i've got a table of around 10.000 records.

Users A, B, C are allowed to see all the records

user D only some
user E only some others

To take logic away from the application to the database,
I would like to have a view as

for user D:
CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria for

viewable

user D)

for user E:
CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria for

viewable

Holger Krug wrote:

On Wed, Jan 16, 2002 at 02:21:39PM +0100, Harald Massa wrote:

i've got a table of around 10.000 records.

Users A, B, C are allowed to see all the records

user D only some
user E only some others

Which is the most elegant way to do this?
(is there any way at all????)

You cannot create different views with the same name in one PostgreSQL
database. Hence the first answer is: there is no way.

Nevertheless that's not the right answer. You can create only one view:

CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria)

Within criteria you can check the user (`current_user') and return
`true' or `false' depending on the record at hand and the current
user.

how about

create view pers as
select ... from totaldb where userid='D' and (d criteria)
union
select ... from totaldb where userid in 'A, B, C' and (abc criteria)
union
select ... from totaldb where userid = 'E' and (e criteria);

You would have to match the columns, perhaps put "not available" where
appropriate. Of course there are spiffier ways to group permissions
instead of hardcoding ids.

elein

--
--------------------------------------------------------
elein@nextbus.com
(510)420-3120
www.nextbus.com
spinning to infinity, hallelujah
--------------------------------------------------------

I found (thank you, Holger Krug) a way to do the access-problem

create view pers as select * from totaldatabase
where case currentuser="userA" then beraterid in (1256,2523,2521,623,124)
else beraterid in (9123, 12312,12313) end

(or more case statements.)

But NOW there are 2 drawbacks

1.) INSERT and UPDATE became MUCH MORE complicated. I have defined rules (do
instead insert ....), but now partial inserts (only SOME columns of the
orignal table) do not work.

2.) there is some bad performance penality. If I do a
explain select * from totaldatabase
where case currentuser="userA" then beraterid in (1256,2523,2521,623,124)
else beraterid in (9123, 12312,12313) end
allways the sequential scan is used - also that only 2% of the rows are
visible. The Planner seems not to realise, that currentuser="userA" DOES NOT
change with each row.

I think I'll ask my question in another way again..

Thank you all,

HArald