MIME-Version: 1.0
References: <CAKna9VZZuGwejSd+u9gQ7WobDYDcip+2Ua5e67sKA-Wgah=7Wg@mail.gmail.com>
 <b924d999-d63a-438f-95de-d08da4905bd1@aklaver.com>
In-Reply-To: <b924d999-d63a-438f-95de-d08da4905bd1@aklaver.com>
From: Lok P <loknath.73@gmail.com>
Date: Sun, 21 Apr 2024 15:05:56 +0530
Message-ID: <CAKna9VYJRd3fxjXDxFAsRFKxQBNMhOGaVTYvidppY4Misc7Lug@mail.gmail.com>
Subject: Re: Logging statement having any threat?
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f2ec7d0616980c5d"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VYJRd3fxjXDxFAsRFKxQBNMhOGaVTYvidppY4Misc7Lug%40mail.gmail.com>
Precedence: bulk

--000000000000f2ec7d0616980c5d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Apr 20, 2024 at 10:02=E2=80=AFPM Adrian Klaver <adrian.klaver@aklav=
er.com>
wrote:

>
> Have you tried?:
>
>
> https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTI=
ME-CONFIG-LOGGING-WHAT
>
> "
> log_statement (enum)
>
>    <...>
>
> The default is none. Only superusers and users with the appropriate SET
> privilege can change this setting.
> "
>
> Or
>
>
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-AD=
MIN-SET
>
> set_config ( setting_name text, new_value text, is_local boolean ) =E2=86=
=92 text
>
>
> >
> > Now when we reach out to the infrastructure team , they are saying thes=
e
> > variables(pg_cluster_log_statement,pg_instance_log_statement) were
>
> Where are those variables coming from? I can not find them in RDS or
> Terraform docs.
>
>
 Thank You Adrian.

Actually I was trying to understand if the auto_explain can only work and
help us see the slow sql statements in the log, only after we set the
"log_statement" parameter to non default values (like all, mod, ddl)?

And what is the exact threat with the logging these queries , and i think
,I got the point as you mentioned , having access to database  itself is
making someone to see the object details, however do you agree that in case
of RDS logs are available through different mediums like cloud watch, data
dog agent etc , so that may pose additional threats as because , may be
some person doesn't have access to database directly but still having
permission to see the logs, so the appropriate access control need to put
in place?

And additionally I was trying to execute the "SELECT
set_config('log_statement', 'all', true);" but it says "*permission denied
to set parameter "log_statement*".".So might be it needs a higher
privileged user to run it.

To answer your question on the variable those we have on the
terraform module, the terraform module is customized by the database infra
team so that might be why we are seeing those there which may not be
exactly the same as its showing in RDS docs for postgres.

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess=
.Concepts.PostgreSQL.html

--000000000000f2ec7d0616980c5d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Apr 20, 2024 at 10:02=E2=80=AFPM =
Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klave=
r@aklaver.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex"><br>
Have you tried?:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/runtime-config-logging.h=
tml#RUNTIME-CONFIG-LOGGING-WHAT" rel=3D"noreferrer" target=3D"_blank">https=
://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONF=
IG-LOGGING-WHAT</a><br>
<br>
&quot;<br>
log_statement (enum)<br>
<br>
=C2=A0 =C2=A0&lt;...&gt;<br>
<br>
The default is none. Only superusers and users with the appropriate SET <br=
>
privilege can change this setting.<br>
&quot;<br>
<br>
Or<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/functions-admin.html#FUN=
CTIONS-ADMIN-SET" rel=3D"noreferrer" target=3D"_blank">https://www.postgres=
ql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET</a><br>
<br>
set_config ( setting_name text, new_value text, is_local boolean ) =E2=86=
=92 text<br>
<br>
<br>
&gt; <br>
&gt; Now when we reach out to the infrastructure team , they are saying the=
se <br>
&gt; variables(pg_cluster_log_statement,pg_instance_log_statement) were <br=
>
<br>
Where are those variables coming from? I can not find them in RDS or <br>
Terraform docs.<br><br></blockquote><div><br></div><div>=C2=A0Thank You Adr=
ian.=C2=A0</div><div><br></div><div>Actually I was trying to understand=C2=
=A0if the auto_explain can only work and help us see the slow sql statement=
s in the log, only after we set the &quot;log_statement&quot; parameter to =
non default values (like all, mod, ddl)?=C2=A0</div><div><br></div><div>And=
 what is the exact threat with the logging these queries , and i think ,I g=
ot the point as you mentioned , having access to database=C2=A0 itself is m=
aking someone to see the object details, however do you agree that in case =
of RDS logs are available through different mediums like cloud watch, data =
dog agent etc , so that may pose additional threats as because , may be som=
e person doesn&#39;t=C2=A0have access to database directly but still having=
 permission to see the logs, so the appropriate access control need to put =
in place?<br></div><div><br></div><div>And additionally=C2=A0I was trying t=
o execute the &quot;SELECT set_config(&#39;log_statement&#39;, &#39;all&#39=
;, true);&quot; but it says &quot;<i>permission denied to set parameter &qu=
ot;log_statement</i>&quot;.&quot;.So might be it needs a higher privileged =
user to run it.</div><div><br></div><div>To answer your question on the var=
iable those we have on the terraform=C2=A0module, the terraform module is c=
ustomized=C2=A0by the database infra team so that might be why we are seein=
g those there which may not be exactly the same as its showing in RDS docs =
for postgres.</div><div><br></div><div><a href=3D"https://docs.aws.amazon.c=
om/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.Concepts.PostgreSQL.html=
">https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_LogAcce=
ss.Concepts.PostgreSQL.html</a><br></div><div><br></div></div></div>

--000000000000f2ec7d0616980c5d--