Обсуждение: Data access and user id

Hi list,

I can't solve what should be a trivial problem:

I don't want users to have direct access to tables (no direct inserts,
updates & deletes), so I use views and functions for that.

My PB is I just can use triggers with SECURITY DEFINER to do that, thus
I can't update the field 'user_mod' with user's id because triggers are
always executed as their owner's id, not caller's id.

Jiff
--

On Sat, Nov 21, 2009 at 8:08 AM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote:

> My PB is I just can use triggers with SECURITY DEFINER to do that, thus
> I can't update the field 'user_mod' with user's id because triggers are
> always executed as their owner's id, not caller's id.

http://www.postgresql.org/docs/8.4/interactive/functions-info.html

Notice that there are different user operators to choose from.
current_user ( user ), session_user.  I believe that one of those will
give you what you want.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Richard Broersma a écrit :
> On Sat, Nov 21, 2009 at 8:08 AM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote:

oops, sorry for the delay: icedove biffer sometimes miss new emails :(

>> My PB is I just can use triggers with SECURITY DEFINER to do that, thus
>> I can't update the field 'user_mod' with user's id because triggers are
>> always executed as their owner's id, not caller's id.
>
> http://www.postgresql.org/docs/8.4/interactive/functions-info.html
>
> Notice that there are different user operators to choose from.
> current_user ( user ), session_user.  I believe that one of those will
> give you what you want.

Yep, but no: my purpose is to forbid any direct rights and pass through
functions & triggers (in this case, trigger *must* be SECURITY DEFINER
to bypass the lack of schema and table permissions.)

The problem is if I use either current_user or session_user in the INSERT
trigger, it returns the trigger's owner Id, not the caller one.

--
Make sure your code does nothing gracefully.

Richard Broersma a écrit :
> http://www.postgresql.org/docs/8.4/interactive/functions-info.html

Ok, you were right: I left triggers and only used functions SECURITY
DEFINED, and switch from current_user to session_user and it worked :D

Thanks Dick!
--
Th' MIND is the Pizza Palace of th' SOUL