Re: grant permissions to set variable?

[Vivek Khera <vivek@xxxxxxxxx>]

On Mar 14, 2007, at 11:36 AM, Richard Huxton wrote:

> Vivek Khera wrote:
> > I want to do some debugging on an app, and I'd like to set on a  
> > per-connection basis "set log_min_duration_statement = 10;"   
> > Obviously since I'm not super user I get permission denied.
> > Is there some GRANT I can grant to the user in question to allow  
> > this?  I don't really want to do it globally or for all  
> > connections by that user, both of which are easy to do
>
> Could you handle it with a security=definer function?

Good call.  However, the following complains about the $ in $1.  My  
guess is that the SET command doesn't like anything but an integer to  
be there.  If I make it a string, the function gets defined, but at  
runtime it complains that it is not an integer.  If I try to cast the  
string to '$1'::integer the function definition again fails with  
syntax error.

CREATE OR REPLACE FUNCTION setlogtime(integer) RETURNS void AS $$
  SET log_min_duration_statement = $1;
  SHOW log_min_duration_statement;
$$ LANGUAGE SQL SECURITY DEFINER;


I tried variants '$1' and '$1'::integer as noted above.

How can I write this function?

Attachment: smime.p7s
Description: S/MIME cryptographic signature