Re: Different views with same name for different users - Mailing list pgsql-general

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
> user E)
>
> for users A, B, C
> CREATE VIEW PERS as SELECT * FROM TOTALDATABASE
>
> so in my application I can do alll the SELECTS on PERS ... which looks
> different for every user.
>
> Which is the most elegant way to do this?
> (is there any way at all????)
>
> Tnx
>
> Harald
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>