current_user inside SECURITY DEFINER function?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

current_user inside SECURITY DEFINER function?

Richard Hayward
Is there any way to get the name of the current user inside a PL/pgSQL
function that is defined with security definer?

current_user gives the name of the user who created the function.

The reason I want this is that I intend to use functions to maintain
security, like this:

userA has only select permission on myTable.

The only way userA can insert to myTable is by providing parameters
for and executing myFunction.

myFunction was created by user postgres who does have insert
permission on myTable. However the code inside myFunction needs to do
different things, depending on who called it.

Is there any way of getting the user?

regards
Richard
 

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: current_user inside SECURITY DEFINER function?

Stephan Szabo
On Tue, 5 Jul 2005, Richard Hayward wrote:

> Is there any way to get the name of the current user inside a PL/pgSQL
> function that is defined with security definer?

IIRC, SESSION_USER should give the original user.

---------------------------(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
Reply | Threaded
Open this post in threaded view
|

Re: current_user inside SECURITY DEFINER function?

Adam Witney
In reply to this post by Richard Hayward

I think you want to use session_user instead

Adam


> Is there any way to get the name of the current user inside a PL/pgSQL
> function that is defined with security definer?
>
> current_user gives the name of the user who created the function.
>
> The reason I want this is that I intend to use functions to maintain
> security, like this:
>
> userA has only select permission on myTable.
>
> The only way userA can insert to myTable is by providing parameters
> for and executing myFunction.
>
> myFunction was created by user postgres who does have insert
> permission on myTable. However the code inside myFunction needs to do
> different things, depending on who called it.
>
> Is there any way of getting the user?
>
> regards
> Richard
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to [hidden email]


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: current_user inside SECURITY DEFINER function?

Stephen Frost
In reply to this post by Richard Hayward
* Richard Hayward ([hidden email]) wrote:
> Is there any way of getting the user?

You might try session_user.  8.1 will hopefully clean this up some.

        Thanks,

                Stephen

signature.asc (196 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: current_user inside SECURITY DEFINER function?

Peter Eisentraut-2
Stephen Frost wrote:
> * Richard Hayward ([hidden email]) wrote:
> > Is there any way of getting the user?
>
> You might try session_user.  8.1 will hopefully clean this up some.

Why would it?  This is SQL standard behavior that should not be changed.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: current_user inside SECURITY DEFINER function?

Richard Hayward
In reply to this post by Stephan Szabo
On Wed, 6 Jul 2005 06:33:58 -0700 (PDT), [hidden email]
(Stephan Szabo) wrote:


>SESSION_USER should give the original user.

Thanks all, that does what I want.

regards
Richard


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: current_user inside SECURITY DEFINER function?

Stephen Frost
In reply to this post by Peter Eisentraut-2
* Peter Eisentraut ([hidden email]) wrote:
> Stephen Frost wrote:
> > * Richard Hayward ([hidden email]) wrote:
> > > Is there any way of getting the user?
> >
> > You might try session_user.  8.1 will hopefully clean this up some.
>
> Why would it?  This is SQL standard behavior that should not be changed.

It'll match the SQL spec, I'm not sure it does now, that was more of my
point than anything else. :)

        Stephen

signature.asc (196 bytes) Download Attachment