grant permissions to set variable?

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