Re: Row-based authorization

On Dec 5, 9:52 am, thiago.si...@kdemail.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...@jabber.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 ;