Обсуждение: Logging statement having any threat?

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

On 4/20/24 07:02, Lok P wrote:
> 
> Now when we reach out to the infrastructure team , they are saying these 
> variables(pg_cluster_log_statement,pg_instance_log_statement) were 
> 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?

I should have added to previous post, if you have access to the database 
the security wall has already been breached.

> 
> Regards
> Lok

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/21/24 02:35, Lok P wrote:
> On Sat, Apr 20, 2024 at 10:02 PM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
> 
>     Have you tried?:
> 
>     https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
<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
<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.
> 
> 
>   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 

log_statement = 'mod'

create role pwd_test with password 'test';
CREATE ROLE

tail -f /var/log/postgresql/postgresql-16-main.log

<...>
2024-04-21 09:04:17.746 PDT [9664] postgres@test LOG:  statement: create 
role pwd_test with password 'test';

> 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 

Aah, the joys of managed services where you have to check even more 
layers when building out your security.  Logging itself is not the 
issue, who has access to the logs is. The more access points the more 
difficult that gets. Dealing with this is going to require a system wide 
review by all parties and coming up with an agreed upon access policy 
that balances security with the need to monitor what is happening in the 
database. Otherwise troubleshooting issues will be a long drawn out 
process which in itself could end up being a security issue.


> 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
<https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.Concepts.PostgreSQL.html>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com