Thread: log_statement GUC parameter
Hi! Unfortunately, only a superuser can set log_statement='all'; Would it be possible to execute set session log_statement='all'; as an ordinary user? I am trying to execute it from login.sql, a part of login_hook extension which implements on-login triggers in PostgreSQL. I will create a procedure with security definer, owned by the role "postgres", and grant it to public. That should do the trick. However, it would be much nicer if PostgreSQL allowed me to set the parameter as a part of the normal session. The idea is to log all statements by the particular user, not by everybody. The user in question uses Weblogic 12.2.1.4 and creates a connection pool so I need to find out which statements are the longest running ones and make them perform. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
On Thu, Aug 12, 2021 at 04:19:18PM -0400, Mladen Gogala wrote: > Hi! > > Unfortunately, only a superuser can set log_statement='all'; Would it be > possible to execute set session log_statement='all'; as an ordinary user? I > am trying to execute it from login.sql, a part of login_hook extension which > implements on-login triggers in PostgreSQL. I will create a procedure with > security definer, owned by the role "postgres", and grant it to public. That > should do the trick. However, it would be much nicer if PostgreSQL allowed > me to set the parameter as a part of the normal session. > > The idea is to log all statements by the particular user, not by everybody. > The user in question uses Weblogic 12.2.1.4 and creates a connection pool > so I need to find out which statements are the longest running ones and make > them perform. I think you can write a SECURITY DEFINER function that calls SET, call that function at login. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
On 8/12/21 1:19 PM, Mladen Gogala wrote: > Hi! > > The idea is to log all statements by the particular user, not by > everybody. The user in question uses Weblogic 12.2.1.4 and creates a > connection pool so I need to find out which statements are the longest > running ones and make them perform. Would pg_stat_statements work for you?: https://www.postgresql.org/docs/current/pgstatstatements.html > > Regards > -- Adrian Klaver adrian.klaver@aklaver.com
Mladen Gogala <gogala.mladen@gmail.com> writes:
> Unfortunately, only a superuser can set log_statement='all'; Would it be
> possible to execute set session log_statement='all'; as an ordinary
> user? I am trying to execute it from login.sql, a part of login_hook
> extension which implements on-login triggers in PostgreSQL. I will
> create a procedure with security definer, owned by the role "postgres",
> and grant it to public. That should do the trick. However, it would be
> much nicer if PostgreSQL allowed me to set the parameter as a part of
> the normal session.
If an ordinary user could manipulate that parameter, he could equally
well hide his activities from the system log. Conversely, if the
postmaster log setup is such that not a lot of volume is expected,
then flooding it with extra traffic could create its own set of
problems. Thus, basically all parameters that affect what is logged
are superuser-only, and it'd be a hard sell to weaken that. The
SECURITY DEFINER function approach is your best bet for poking local
exceptions into that policy.
regards, tom lane
On Thu, Aug 12, 2021 at 04:30:12PM -0400, Tom Lane wrote: > Mladen Gogala <gogala.mladen@gmail.com> writes: > > Unfortunately, only a superuser can set log_statement='all'; Would it be > > possible to execute set session log_statement='all'; as an ordinary > > user? I am trying to execute it from login.sql, a part of login_hook > > extension which implements on-login triggers in PostgreSQL. I will > > create a procedure with security definer, owned by the role "postgres", > > and grant it to public. That should do the trick. However, it would be > > much nicer if PostgreSQL allowed me to set the parameter as a part of > > the normal session. > > If an ordinary user could manipulate that parameter, he could equally > well hide his activities from the system log. Conversely, if the > postmaster log setup is such that not a lot of volume is expected, > then flooding it with extra traffic could create its own set of > problems. Thus, basically all parameters that affect what is logged > are superuser-only, and it'd be a hard sell to weaken that. The > SECURITY DEFINER function approach is your best bet for poking local > exceptions into that policy. The nice thing about SECURITY DEFINER is that the super user controls what values it can be set to. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
[ dept. of second thoughts ] Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 8/12/21 1:19 PM, Mladen Gogala wrote: >> The idea is to log all statements by the particular user, not by >> everybody. > Would pg_stat_statements work for you?: > https://www.postgresql.org/docs/current/pgstatstatements.html Actually, for that specific requirement, there's an easier way: ALTER USER target_user SET log_statement = 'all'; While the target_user can't do that for himself, a superuser can. regards, tom lane
On Thu, Aug 12, 2021 at 04:37:16PM -0400, Tom Lane wrote: > [ dept. of second thoughts ] > > Adrian Klaver <adrian.klaver@aklaver.com> writes: > > On 8/12/21 1:19 PM, Mladen Gogala wrote: > >> The idea is to log all statements by the particular user, not by > >> everybody. > > > Would pg_stat_statements work for you?: > > https://www.postgresql.org/docs/current/pgstatstatements.html > > Actually, for that specific requirement, there's an easier way: > > ALTER USER target_user SET log_statement = 'all'; > > While the target_user can't do that for himself, a superuser > can. Ah, yes, I hadn't considered that ALTER USER bypasses those user checks. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Hi Bruce Yes, I can. I have already done so and it works. I wrote a procedure because of my Oracle background, but it doesn't really matter. This was just a suggestion for the session settable parameters. Regards On 8/12/21 4:23 PM, Bruce Momjian wrote: > I think you can write a SECURITY DEFINER function that calls SET, call > that function at login. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Thank you Tom! It turns out that the Oracle way of doing things (SECURITY DEFINER) was the wrong way here. Thanks a bunch. On 8/12/21 4:37 PM, Tom Lane wrote: > ctually, for that specific requirement, there's an easier way: > > ALTER USER target_user SET log_statement = 'all'; > > While the target_user can't do that for himself, a superuser > can. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com