log_statement GUC parameter

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