SEO

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: tsilva@jabber.org

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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 ;

On Tue, Dec 05, 2006 at 12:52:15 -0200,
Thiago Silva <thiago.silva@kdemail.net> wrote:
>
> 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.

You can do this kind of thing using a view that joins a column in the
table to current_user. You would probably want some other table which
maps something in this extra column to authorized users (unless each
row is only going to be accessible to one user).

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/