On 4/21/24 02:35, Lok P wrote:
On Sat, Apr 20, 2024 at 10:02 PM Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx
