Re: Logging statement having any threat?

[Adrian Klaver]

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


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
 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Logging statement having any threat?

[Lok P]

On Sat, Apr 20, 2024 at 10:02 PM Adrian Klaver 
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

Re: Logging statement having any threat?

[Adrian Klaver]

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.kla...@aklaver.com

Re: Logging statement having any threat?

[Adrian Klaver]

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.kla...@aklaver.com

Re: Logging the query executed on the server

[Steve Baldwin]

On Sun, Jul 24, 2022 at 4:29 PM Igor Korot  wrote:

>
> 2 things:
> 1. How do I turn this off? ;-)
>

When you change the setting via 'set', that change is only for the current
session. You can revert it with 'set {some param} to default;' or just
terminate the session. If you want to make the change permanent, you need
to set it in the config file.

2. The log does show the query but it shows it with the placeholders.
> Is there a way to see the actual query?
>

Not sure what you mean here. The query shown in the log should be what is
actually executed by the server. If you are using placeholders, there are
probably 3 different log entries - one for the parse step, one for the bind
step and one for the execute step. If you are asking what are the bind
variable values, they are shown in the bind step. For example:

2022-07-24 07:00:00
UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:LOG:
duration: 0.072 ms bind :
select public.currency_on_mismatch() as on_mismatch,
set_config('search_path', $1, true),
set_config('application.user_id', $2, true),
set_config('application.app_client', $3, true),
set_config('application.api_client_id', $4 , true),
set_config('application.source', $5 , true),
set_config('application.request_id', $6 , true),
set_config('application.in_test_context', $7, true),
set_config('lock_timeout', $8, true),
txid_current()
2022-07-24 07:00:00
UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:DETAIL:
parameters: $1 = 'public', $2 = 'Admin/Support', $3 = 'Admin/Support', $4 =
'ce34e2bc-2c65-4fc1-9b95-878aef19a348', $5 = '?', $6 =
'4432dbb2-ab1c-4bd8-a413-ff5c704209a6', $7 = 'f', $8 = '10s'

If this doesn't help, maybe post what you're seeing in the log.

Cheers,

Steve

Re: Logging the query executed on the server

[Igor Korot]

Hi, Steve,

On Sun, Jul 24, 2022 at 12:51 AM Steve Baldwin  wrote:
>
> Hi Igor,
>
> Before you issue your query, try something like this:
>
> (from psql, but hopefully you get the idea)
>
> b2bcreditonline=# set log_min_duration_statement to 0;
> SET
> b2bcreditonline=# set log_statement to 'all';
> SET
>
> Ref: https://www.postgresql.org/docs/current/sql-set.html, 
> https://www.postgresql.org/docs/current/config-setting.html
>
> Then submit your query and it will be set to the server log. You can get the 
> name of the current logfile with:
>
> b2bcreditonline=# select pg_current_logfile();
>  pg_current_logfile
> 
>  /log/pg.csv

2 things:
1. How do I turn this off? ;-)
2. The log does show the query but it shows it with the placeholders.
Is there a way to see the actual query?

Thank you.


>
> HTH,
>
> Steve
>
> On Sun, Jul 24, 2022 at 3:26 PM Igor Korot  wrote:
>>
>> Hi,
>> Is it possible to log the query that will be executed
>> on the server?
>>
>> I'm writing an application that connects to the server
>> through ODBC and libpq.
>> For some reason ODBC interface is failing - it desn't
>> return any rows
>>
>> So I'm thinking if I have a proof that the query I am
>> actually executing is the same as the one I run through
>> the psql - I will know where to look.
>>
>> I am actually binding some parameters and trying to
>> execute the query.
>>
>> Thank you.
>>
>>

Re: Logging the query executed on the server

[Steve Baldwin]

Hi Igor,

Before you issue your query, try something like this:

(from psql, but hopefully you get the idea)

b2bcreditonline=# set log_min_duration_statement to 0;
SET
b2bcreditonline=# set log_statement to 'all';
SET

Ref: https://www.postgresql.org/docs/current/sql-set.html,
https://www.postgresql.org/docs/current/config-setting.html

Then submit your query and it will be set to the server log. You can get
the name of the current logfile with:

b2bcreditonline=# select pg_current_logfile();
 pg_current_logfile

 /log/pg.csv

HTH,

Steve

On Sun, Jul 24, 2022 at 3:26 PM Igor Korot  wrote:

> Hi,
> Is it possible to log the query that will be executed
> on the server?
>
> I'm writing an application that connects to the server
> through ODBC and libpq.
> For some reason ODBC interface is failing - it desn't
> return any rows
>
> So I'm thinking if I have a proof that the query I am
> actually executing is the same as the one I run through
> the psql - I will know where to look.
>
> I am actually binding some parameters and trying to
> execute the query.
>
> Thank you.
>
>
>

Re: Logging [RESOLVED]

[Peter J. Holzer]

On 2019-12-04 16:03:24 -0800, Rich Shepard wrote:
> On Wed, 4 Dec 2019, Stephen Eilert wrote:
> > Usually, this is done by logrotate or a similar mechanism in your system.
> > You’ll likely find that other logs in your system follow a similar
> > pattern, not just Postgresql.
> 
> I just checked /etc/logrotate.d/postgres and it was set at daily with rotate
> 7. I changed rotate to 4 but the dates are 3-4 days apart, not sequential.

Your log files are extremely small. At only a few hundred bytes every 3
or 4 days it is very likely that nothing is logged on most days. If your
log file is empty, logrotate won't rotate it if the option "notifempty"
is set (which is probably the case).

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Logging

[Rich Shepard]

On Wed, 4 Dec 2019, Adrian Klaver wrote:

Take a look at the logging section of postgresql.conf to see if Postgres is 
handing off to the system and logrotate


Adrian,

That conf file is in several places (different flavors). I'll check them
all.

Thanks,

Rich

Re: Logging [RESOLVED]

[Rich Shepard]

On Wed, 4 Dec 2019, Stephen Eilert wrote:


Usually, this is done by logrotate or a similar mechanism in your system.
You’ll likely find that other logs in your system follow a similar
pattern, not just Postgresql.


Stephen,

Other logs, controlled by logrotate, rotate daily for a maximum of 4
backups.

I just checked /etc/logrotate.d/postgres and it was set at daily with rotate
7. I changed rotate to 4 but the dates are 3-4 days apart, not sequential.

Thanks,

Rich

Re: Logging

[Adrian Klaver]

On 12/4/19 3:20 PM, Rich Shepard wrote:

Running Slackware-14.2/x86_64 and postgresql-11.5.

In /var/log/ are these files:

-rw-r- 1 postgres wheel   0 Nov 23 04:40 postgresql-11
-rw-r- 1 postgres wheel 723 Nov 23 04:40 postgresql-11.1
-rw-r- 1 postgres wheel 324 Nov 20 04:40 postgresql-11.2.gz
-rw-r- 1 postgres wheel 320 Nov 17 04:40 postgresql-11.3.gz
-rw-r- 1 postgres wheel 322 Nov 14 04:40 postgresql-11.4.gz
-rw-r- 1 postgres wheel 321 Nov 10 04:40 postgresql-11.5.gz
-rw-r- 1 postgres wheel 325 Nov  6 04:40 postgresql-11.6.gz
-rw-r- 1 postgres wheel 337 Oct 23 04:40 postgresql-11.7.gz

I assume that they're an automatic backup that runs every 3-4 days. What's
backed up and where is this controlled?


Looks like logrotate.
Take a look at the logging section of postgresql.conf to see if Postgres 
is handing off to the system and logrotate




I ask because I have a cron job that does a pg_dumpall each night at 11:30
pm. (It's a small installation for my business use so the files are not
excessive and I keep them for only short periods.)

Regards,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Logging

[Stephen Eilert]

Usually, this is done by logrotate or a similar mechanism in your system. 
You’ll likely find that other logs in your system follow a similar pattern, not 
just Postgresql.

— Stephen
On Dec 4, 2019, 3:21 PM -0800, Rich Shepard , wrote:
> Running Slackware-14.2/x86_64 and postgresql-11.5.
>
> In /var/log/ are these files:
>
> -rw-r- 1 postgres wheel 0 Nov 23 04:40 postgresql-11
> -rw-r- 1 postgres wheel 723 Nov 23 04:40 postgresql-11.1
> -rw-r- 1 postgres wheel 324 Nov 20 04:40 postgresql-11.2.gz
> -rw-r- 1 postgres wheel 320 Nov 17 04:40 postgresql-11.3.gz
> -rw-r- 1 postgres wheel 322 Nov 14 04:40 postgresql-11.4.gz
> -rw-r- 1 postgres wheel 321 Nov 10 04:40 postgresql-11.5.gz
> -rw-r- 1 postgres wheel 325 Nov 6 04:40 postgresql-11.6.gz
> -rw-r- 1 postgres wheel 337 Oct 23 04:40 postgresql-11.7.gz
>
> I assume that they're an automatic backup that runs every 3-4 days. What's
> backed up and where is this controlled?
>
> I ask because I have a cron job that does a pg_dumpall each night at 11:30
> pm. (It's a small installation for my business use so the files are not
> excessive and I keep them for only short periods.)
>
> Regards,
>
> Rich
>
>

Re: logging proxy

[Bruce Momjian]

On Fri, Nov 1, 2019 at 01:58:10AM +0300, Олег Самойлов
wrote:
> Does anyone know PostgresQL proxy which can log queries with username,
> ip and affected rows for security reason. PostgresQL itself can log
> almost all, except affected rows.

You can use the server logs to get the non-row information, then use
streaming replication with logical decoding to get the rows for each
transaction started by the user.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: logging "raise" to file

[wambacher]

Thank Georg,

regards
walter

> the red part writes your "raise notice" to your log
>
> psql (+your connection string) -f /path/to/file.sql *>
> /path/to/log/xxx.log 2>&1*
>
> -- 
My projects:

Admin Boundaries of the World 
Missing Boundaries

Emergency Map 
Postal Code Map (Germany only) 
Fools (QA for zipcodes in Germany) 
Postcode Boundaries of Germany 
OSM Software Watchlist

Re: logging "raise" to file

[Georg H.]

Hi,

Am 28.07.2019 um 12:32 schrieb wambac...@posteo.de:


Hi,

is there a way to log output from "raise ..." to a local file? \o file 
does not work (for me).


regards
walter



the red part writes your "raise notice" to your log

psql (+your connection string) -f /path/to/file.sql *> 
/path/to/log/xxx.log 2>&1*


see

https://dba.stackexchange.com/questions/107199/how-to-log-custom-messages-from-inside-a-postgresql-transaction

regards

Georg

Re: logging "raise" to file

[Guillaume Lelarge]

Hi,

Le dim. 28 juil. 2019 à 12:32,  a écrit :

> Hi,
>
> is there a way to log output from "raise ..." to a local file? \o file
> does not work (for me).
>

No, RAISE messages can only go to log files. You would need to call a
function that could write to a file (though without calling RAISE).


-- 
Guillaume.

Re: Logging

[Andrew Bartley]

Thanks All,

It seems that the related DETAIL message appears if the query is
successful.  On error the DETAIL log line is missing...   This makes
debugging difficult, considering the insert is coming from Dreamfactory via
a rest POST.

I am finding it very difficult navigating my way through the brave new
world of SAS and Blackbox type applications  Maybe 32 years as a
database programmer is just too long.

Thanks

Andrew




On Wed, 13 Jun 2018 at 14:12 Adrian Klaver 
wrote:

> On 06/12/2018 08:25 PM, David G. Johnston wrote:
> > On Tuesday, June 12, 2018, Andrew Bartley  > > wrote:
> >
> >
> >
> > On Wed, 13 Jun 2018 at 12:43 Laurenz Albe  > > wrote:
> >
> >
> > log_min_duration_statement = 0
> >
> > [...]
> >
> >
> > log_min_duration_statement -1
> >
> >
> > You've disabled statement logging altogether.  The zero value you were
> > directed to use is what causes everything to be logged.
>
> Actually no:
>
>
> https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN
>
> log_min_duration_statement (integer)
>
> "... Setting this to zero prints all statement durations. Minus-one (the
> default) disables logging statement durations.  ..."
>
> "
>
> So -1 only affects logging statements relative to duration.
>
> If you have log_statements set then you will still get statements logged
> if you have log_min_duration_statement = -1 :
>
> Note
>
> When using this option together with log_statement, the text of
> statements that are logged because of log_statement will not be repeated
> in the duration log message.
> "
>
> This is how I have my logging setup, log_min_duration_statement = -1
> and log_statements = 'mod' and I see statements in the logs.
>
> >
> > David J.
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Logging

[Adrian Klaver]

On 06/12/2018 08:25 PM, David G. Johnston wrote:
On Tuesday, June 12, 2018, Andrew Bartley > wrote:




On Wed, 13 Jun 2018 at 12:43 Laurenz Albe mailto:laurenz.a...@cybertec.at>> wrote:


log_min_duration_statement = 0

[...]


log_min_duration_statement -1


You've disabled statement logging altogether.  The zero value you were 
directed to use is what causes everything to be logged.


Actually no:

https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

log_min_duration_statement (integer)

"... Setting this to zero prints all statement durations. Minus-one (the 
default) disables logging statement durations.  ..."


"

So -1 only affects logging statements relative to duration.

If you have log_statements set then you will still get statements logged 
if you have log_min_duration_statement = -1 :


Note

When using this option together with log_statement, the text of 
statements that are logged because of log_statement will not be repeated 
in the duration log message.

"

This is how I have my logging setup, log_min_duration_statement = -1
and log_statements = 'mod' and I see statements in the logs.



David J.





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Logging

[Andrew Bartley]

Ok, thanks.   will try

On Wed, 13 Jun 2018 at 13:25 David G. Johnston 
wrote:

> On Tuesday, June 12, 2018, Andrew Bartley  wrote:
>
>> On Wed, 13 Jun 2018 at 12:43 Laurenz Albe 
>> wrote:
>>
>
>>> log_min_duration_statement = 0
>>>
>> [...]
>
>>
>> log_min_duration_statement -1
>>
>
> You've disabled statement logging altogether.  The zero value you were
> directed to use is what causes everything to be logged.
>
> David J.
>
>
>

Re: Logging

[David G. Johnston]

On Tuesday, June 12, 2018, Andrew Bartley  wrote:

>
>
> On Wed, 13 Jun 2018 at 12:43 Laurenz Albe 
> wrote:
>
>>
>> log_min_duration_statement = 0
>>
> [...]

>
> log_min_duration_statement -1
>

You've disabled statement logging altogether.  The zero value you were
directed to use is what causes everything to be logged.

David J.

Re: Logging

[Andrew Bartley]

On Wed, 13 Jun 2018 at 12:43 Laurenz Albe  wrote:

> Andrew Bartley wrote:
> > Can someone please tell me how to log the values being inserted in this
> example..
> >
> > 2018-06-12 22:31:49 UTC-5b2049d4.434ac-STATEMENT:  insert into
> "api_consumers" ("consumer_id", "create_datetime") values ($1, $2).
> >
> > I have tried many different logging options and combinations.
>
> That should automatically be logged as a DETAIL message.
>
> log_min_duration_statement = 0
> log_min_error_statement = log or better
> log_min_messages = log or better
>
> That should do the trick.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com

Thanks for the reply Laurenz,


Current settings

"PostgreSQL 9.6.7, compiled by Visual C++ build 1800, 64-bit"
Azure managed instance


log_min_duration_statement -1
log_min_error_statement DEBUG2
log_min_messages DEBUG2
log_statement ALL
log_error_verbosity VERBOSE

Still no joy.

Thanks Andrew

Re: Logging

[Laurenz Albe]

Andrew Bartley wrote:
> Can someone please tell me how to log the values being inserted in this 
> example..
> 
> 2018-06-12 22:31:49 UTC-5b2049d4.434ac-STATEMENT:  insert into 
> "api_consumers" ("consumer_id", "create_datetime") values ($1, $2).
> 
> I have tried many different logging options and combinations. 

That should automatically be logged as a DETAIL message.

log_min_duration_statement = 0
log_min_error_statement = log or better
log_min_messages = log or better

That should do the trick.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com