Re: User permission

"C G"  writes:
> I have a user_info table that has trigger which creates a user and
> switches session authorization to the new user, but it doesn't seem to
> work as I expect.

> mydb1=> select * from user_info ;
> usename | password
> ---------+----------
> user1   | ***
> (1 row)
> Shouldn't be able to do that!

I think what is happening is that when control exits from the SECURITY
DEFINER function, the value of current_user is reset to what it was when
the function was entered.  So even though session_user says user1,
you are still effectively admin here.

I'm not sure there is any clean solution that would make this do what
you expect, partly because it's not real clear what you expect (consider
nested SECURITY DEFINER functions, error recovery, etc).  I'd be
inclined to put a band-aid on it by forbidding SET SESSION AUTHORIZATION
inside functions.

Can anyone propose a defensible and implementable behavior that would
allow CG to do what he wants?

			regards, tom lane