agora inbox for [email protected]
help / color / mirror / Atom feedRe: Logging statement having any threat?
4+ messages / 4 participants
[nested] [flat]
* Re: Logging statement having any threat?
@ 2024-04-21 09:35 Lok P <[email protected]>
2024-04-21 16:17 ` Re: Logging statement having any threat? Adrian Klaver <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Lok P @ 2024-04-21 09:35 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sat, Apr 20, 2024 at 10:02 PM Adrian Klaver <[email protected]>
wrote:
>
> 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.
>
>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Logging statement having any threat?
2024-04-21 09:35 Re: Logging statement having any threat? Lok P <[email protected]>
@ 2024-04-21 16:17 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2024-04-21 16:17 UTC (permalink / raw)
To: Lok P <[email protected]>; +Cc: pgsql-general <[email protected]>
On 4/21/24 02:35, Lok P wrote:
> On Sat, Apr 20, 2024 at 10:02 PM Adrian Klaver
> <[email protected] <mailto:[email protected]>> 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
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* last_analyze/last_vacuum in pg_stat_all_tables (PG 17.10)
@ 2026-07-03 10:20 Thomas Michael Engelke <[email protected]>
2026-07-05 13:12 ` Re: last_analyze/last_vacuum in pg_stat_all_tables (PG 17.10) Laurenz Albe <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Thomas Michael Engelke @ 2026-07-03 10:20 UTC (permalink / raw)
To: [email protected] <[email protected]>
Good morning,
for monitoring purposes we use check_pgactivity
(https://github.com/OPMDG/check_pgactivity) to check a number of
different metrics. One of those checks the last analyze timestamp of
either kind, another the last vacuum timestamp of either kind, and
alerts when either of those becomes older than a certain age that can
be specified as a parameter to these checks.
This is the SQL that runs to find these values:
$PG_VERSION_91 => qq{
SELECT
pg_is_in_recovery()::int AS is_in_recovery,
current_database(),
schemaname,
a.relname,
CASE WHEN NOT pg_is_in_recovery() THEN
coalesce(max(
coalesce(extract(epoch FROM
current_timestamp -
greatest(last_${type},
last_auto${type})),
'infinity'::float)),
'NaN'::float)
ELSE 'NaN'::float END,
CASE WHEN NOT pg_is_in_recovery() THEN
coalesce(sum(${type}_count), 0)
ELSE 0 END
AS ${type}_count,
CASE WHEN NOT pg_is_in_recovery() THEN
coalesce(sum(auto${type}_count), 0)
ELSE 0 END
AS auto${type}_count,
CASE WHEN NOT pg_is_in_recovery() THEN
(
SELECT
md5(tup_inserted::text||tup_updated::text||tup_deleted::text||'$c_limit
$w_limit'
)
FROM pg_catalog.pg_stat_database
WHERE datname = current_database()
)
ELSE '' END
FROM pg_stat_all_tables a
JOIN pg_class b on a.relid = b.oid
WHERE schemaname NOT LIKE 'pg_temp_%'
AND schemaname NOT LIKE 'pg_toast_temp_%'
AND pg_relation_size(relid) >= $table_min_size
AND schemaname NOT LIKE 'information_schema'
AND (('${type}' = 'analyze' AND schemaname NOT LIKE
'pg_toast') -- TOAST tables aren't ANALYZEd
OR ('${type}' = 'vacuum'))
AND (('${type}' = 'analyze' AND a.relname NOT LIKE
'pg_statistic') -- pg_statistic never gets ANALYZEd
OR ('${type}' = 'vacuum'))
AND relkind <> 'p' -- partitioned table do not have
last_* information
GROUP BY schemaname, a.relname
}
{type} gets replaced by either analyze or vacuum.
What happens *sometimes* on servers is that the age of the oldest
maintenance increases to infinity, as the fields of last_analyze and
last_vacuum are emptied (NULL value).
However, looking into the monitoring system I can see that there must
have been values in these fields before. I can see the values coming
ouf of that check 2 hours ago, the values were not infinity.
Are there any circumstances under which these fields get emptied? From
reading the documentation any value should never be replaced by a NULL
value:
last_vacuum timestamp with time zone: Last time at which this table was
manually vacuumed (not counting VACUUM FULL)
last_autovacuum timestamp with time zone: Last time at which this table
was vacuumed by the autovacuum daemon
last_analyze timestamp with time zone: Last time at which this table
was manually analyzed
last_autoanalyze timestamp with time zone: Last time at which this
table was analyzed by the autovacuum daemon
I can probably work around that by using either vacuum against the
database or vacuumdb on the system, but I'd like to understand if there
are other factors that influence these fields, and maybe the monitoring
agent needs to change in that regard.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: last_analyze/last_vacuum in pg_stat_all_tables (PG 17.10)
2026-07-03 10:20 last_analyze/last_vacuum in pg_stat_all_tables (PG 17.10) Thomas Michael Engelke <[email protected]>
@ 2026-07-05 13:12 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2026-07-05 13:12 UTC (permalink / raw)
To: Thomas Michael Engelke <[email protected]>; [email protected] <[email protected]>
On Fri, 2026-07-03 at 10:20 +0000, Thomas Michael Engelke wrote:
> What happens *sometimes* on servers is that the age of the oldest
> maintenance increases to infinity, as the fields of last_analyze and
> last_vacuum are emptied (NULL value).
>
> However, looking into the monitoring system I can see that there must
> have been values in these fields before. I can see the values coming
> ouf of that check 2 hours ago, the values were not infinity.
>
> Are there any circumstances under which these fields get emptied?
A crash of the database server would do that.
Also, if somebody calls pg_stat_reset() or
pg_stat_reset_single_table_counters('table_name'), that would reset
these counters.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-07-05 13:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-21 09:35 Re: Logging statement having any threat? Lok P <[email protected]>
2024-04-21 16:17 ` Adrian Klaver <[email protected]>
2026-07-03 10:20 last_analyze/last_vacuum in pg_stat_all_tables (PG 17.10) Thomas Michael Engelke <[email protected]>
2026-07-05 13:12 ` Re: last_analyze/last_vacuum in pg_stat_all_tables (PG 17.10) Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox