Search Postgresql Archives

Re: log_min_duration_statement modification for non superusers?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux