From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Paul McGarry *EXTERN*" <paul(at)paulmcgarry(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: log_min_duration_statement modification for non superusers? |
Date: | 2012-06-28 07:55:48 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C20812787B@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Paul McGarry wrote:
> I have a situation where I'd like to lower the
> log_min_duration_statement for a particular connection
> .
> The DB is used for several reporting type queries which may reasonably
> take several seconds so the log_min_duration_statement in the DB is
> set accordingly.
>
> However there are critical paths in the app where queries are more
> interactive and we'd like to log any statements using a much lower
> limit.
>
> A superuser could, for example:
> set log_min_duration_statement = 500;
> when connecting to the DB but normal users can't.
>
> Is there a trick that would allow us to enable non-super users to
> lower the log_min_duration_statement value?
>
> The only thing I have though of is connecting as a Super User and then
> using "SET ROLE" to change to the non-priviledged user afterwards but
> really we don't want our Super User authentication credentials on the
> application server.
You could write a SECURITY DEFINER function thusly:
CREATE OR REPLACE FUNCTION set_log_min_duration(integer) RETURNS void
LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER
SET search_path TO pg_catalog,pg_temp AS
$$BEGIN
EXECUTE 'SET log_min_duration_statement = ' || $1::text;
END$$;
REVOKE EXECUTE ON FUNCTION set_log_min_duration(integer) FROM PUBLIC;
Then you can grant EXECUTE privileges to the users you want to
be able to change the setting.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Raghavendra | 2012-06-28 08:38:20 | Re: Customising pg directories |
Previous Message | Ben Carbery | 2012-06-28 07:50:32 | Customising pg directories |