Feedback-ID: i76984098:Fastmail
Message-ID: <b924d999-d63a-438f-95de-d08da4905bd1@aklaver.com>
Date: Sat, 20 Apr 2024 09:32:46 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Logging statement having any threat?
To: Lok P <loknath.73@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAKna9VZZuGwejSd+u9gQ7WobDYDcip+2Ua5e67sKA-Wgah=7Wg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAKna9VZZuGwejSd+u9gQ7WobDYDcip+2Ua5e67sKA-Wgah=7Wg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/b924d999-d63a-438f-95de-d08da4905bd1%40aklaver.com>
Precedence: bulk

On 4/20/24 07:02, Lok P wrote:
> Hello All,
> Its postgres version 15.4 and its RDS, in which our dev team gets the 
> infrastructure code from another third party team which provides us base 
> infrastructure code to build a postgres database, in which we will be 
> able to do change DB parameter values etc whatever is mentioned in the 
> file with possible values. But surprisingly we don't see log_statement 
> there. Below was our requirement,
> 
> For debugging and evaluating performance we were having 
> pg_stat_statements but it contains aggregated information about all the 
> query execution. But in case just want to debug any point in time issues 
> where the selected few queries were performing bad (may be because of 
> plan change), we were planning to have the auto_explain extension added 
> and set the log_min_duration to ~5 seconds, So that, all the queries 
> going above that time period(5 seconds) will be logged and provide 
> detailed information on the exact point of bottleneck. But we see the 
> log_statement parameter has been removed from the base infrastructure 
> script/terraform script given by the database team here, so that means 
> we will get it as default which is "NONE", which means no 
> statement(SELECT/DML/DDL etc) can be logged.

Have you tried?:

https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-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-ADMIN-SET

set_config ( setting_name text, new_value text, is_local boolean ) → text


> 
> Now when we reach out to the infrastructure team , they are saying these 
> 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.

> removed due to potential security threat. So I want to understand from 
> experts here , how this is really a security threat and if any option to 
> get this logging enabled (which will help us debug performance issues) 
> at same time addressing the threat too?
> 
> Regards
> Lok

-- 
Adrian Klaver
adrian.klaver@aklaver.com