Re: Row-based authorization

On Dec 5, 9:52 am, thiago(dot)si(dot)(dot)(dot)(at)kdemail(dot)net ("Thiago Silva") wrote:
> Hello all,
> I'm not much of a database professional, so my questions might sound silly ;)
>
> I was wondering if PostgreSQL authorization rules can be aplied on
> specific rows of a given table. I mean, AFAIK the GRANT statement
> cannot be used for such purpose.
>
> The other way I looked into implement such behavior was to use
> triggers (naive approach?), but, looking into the documentation, I see
> that those cannot be applied on SELECT statements.
>
> What I actually need is a flexible mechanism for figuring out if a
> given user can or cannot see/change/add/delete information on the DB,
> based on a dinamic set of strategies - per record. Think of
> filesystems, where each file has its own set of permission rules.
> Except that the permission rules could be more flexible and dinamic.
>
> So, if such mechanism is currently not possible to be used, is there
> any interest (or is it coherent, viable and desirable) to support such
> feature in PostgreSQL?
> If not, has anyone suggestions about this?
>
> Thanks,
>
> --
> Thiago Silva
> Blog:www.sourcecraft.info/blog
> Jabber: tsi(dot)(dot)(dot)(at)jabber(dot)org

I've seen that done by using views in conjunction with a user
permission table. Basic idea is to revoke direct SELECT permission on
the underlying table and to create a VIEW that filters the rows based
on column values that are associated with the currently logged-in user.
For example:

BEGIN ;

CREATE TABLE public.user_perms (
name varchar(32) NOT NULL
,department_id varchar(5) NOT NULL
) ;

CREATE TABLE public.employees (
employee_id integer NOT NULL
,name varchar(50) NOT NULL
,department_id varchar(5) NOT NULL
) ;

CREATE VIEW public.v_employees AS
SELECT *
FROM public.employees e
WHERE e.department_id IN (
SELECT up.department_id
FROM public.user_perms up
WHERE up.name=CURRENT_USER
) ;

INSERT INTO public.employees VALUES(1,'Frank Smith','A001') ;
INSERT INTO public.employees VALUES(2,'Louis Jones','A001') ;
INSERT INTO public.employees VALUES(3,'Martin Ramirez','B001') ;

-- ...dbuser-1 can "see" both departments, dbuser-2 can only see A001
INSERT INTO public.user_perms VALUES('dbuser-1','A001') ;
INSERT INTO public.user_perms VALUES('dbuser-1','B001') ;
INSERT INTO public.user_perms VALUES('dbuser-2','A001') ;

ROLLBACK ;