Letting Non-superusers Control Logging

I’ve decided that my quest to port one of my systems from 7.3 -> 8.1 is turning into a case study on why I shouldn’t upgrade. It’s not just the [http://archives.postgresql.org/pgsql-performance/2006-01/msg00265.php query trouble], it’s the little quirks that that trip you up as well. Todays topic? Controlling Logging For Non-Superusers… So the problem I face is very simple. One of the apps that hits the 7.3 database is an xml-rpc service that runs on about a half-dozen servers, doing a couple thousand transactions every minute. When something goes awry, it’s hard to debug, especially on 7.3. So one thing we do is include a debugging flag on each server that allows it to turn on sql logging by issuing the proper SET command. Unfortunatly I looked in my 8.1 log where we’re doing some testing and ran across this:

2006-01-30 14:03:03 EST : 11267 : 10.25.10.54 : ERROR:  permission denied to set parameter "log_statement"

2006-01-30 14:03:03 EST : 11267 : 10.25.10.54 : STATEMENT: SET log_statement = on

Ugh. Apparently this behavior was changed somewhere after 7.3. I say somewhere becuase I didn’t see any mention of it in the release notes. (Bonus points for anyone who posts a link in the comments) Luckily it’s not a complete catastrophe. Basically you need to write a function that allows non-superusers to turn the logging on and off. In my case, I don’t need to duplicate the full functionality of log_statement, just the basic idea of turning logging on or off, so I whipped out this little function:

CREATE OR REPLACE FUNCTION log_statement(boolean) RETURNS text AS $$

DECLARE

    param     text;

    retval    text;

    setsql    text;

    devnull   text;

BEGIN

    IF $1 THEN

        param := 'all';

        retval := 'on';

    ELSE

       param := 'none';

       retval := 'off';

    END IF;



    setsql := 'SELECT set_config(\'log_statement\',\'' || param || '\',true)';

    RAISE DEBUG '%',setsql; 

    EXECUTE setsql INTO devnull;



    RETURN retval;

END

$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

You can could probably shorten the code down a little, but it will work like this and should be pretty simple to read. If you really used this functionality alot, you might want to do some type of overloaded version of set_config itself and make it security definer so you could control all of the log_* parameters but that’s a bit beyond what I needed. The only downside here is that we still need to touch application code to implement the change, but I guess you can’t have everything.