Thread: grant permissions to set variable?

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

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?

--
   Richard Huxton
   Archonet Ltd

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?

Vivek Khera <vivek@khera.org> writes:
> CREATE OR REPLACE FUNCTION setlogtime(integer) RETURNS void AS $$
>    SET log_min_duration_statement = $1;
>    SHOW log_min_duration_statement;
> $$ LANGUAGE SQL SECURITY DEFINER;

> How can I write this function?

Use a plpgsql EXECUTE command.  In general, utility statements don't
cope with parameters, because that's a planner/executor facility and
utility statements don't go through that.  So you've got to substitute
the value you want into the text of the command that's submitted.

            regards, tom lane