Hmm... let me see if I got it right. I can set log_statement to mod overall, and then modify it as ALTER USER postgres SET log_statement=all; for postgres only? Also... how do we control who can run meta commands?
Thanks,
Oleg
On Thu, Dec 10, 2015 at 2:16 PM, Jerry Sievers <gsievers19@xxxxxxxxxxx> wrote:
Scott Mead <scottm@xxxxxxxxxxx> writes:
> On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim <olegyusim@xxxxxxxxx> wrote:
>
> Thanks John, I realized that and confirmed in my logs. What I'm trying to determine now, can I only log some SELECT statements, or I should log all of them or none
> of them.
>
> You can configure this to multiple levels:
>
> Global, per-user, per-database
>
> ALTER USER postgres SET log_min_duration_statement=0;
> ALTER DATABASE xyz SET log_min_duration_statement=0;
>
> That being said, you would want to make sure that the user
> issuing the largest volume of queries is not set with this, otherwise,
> you could potential flood your logs with every single query
> issued. This has a tendency to cause performance
> problems.
> The other item of note is that, once logged in, the
> user could change that value with a similar ALTER statement.
No, not really. Unprivileged users can't frob those settings.
> select name, context from pg_settings where name ~ '^log.*statement$' order by 1;
name | context
----------------------------+-----------
log_min_duration_statement | superuser
log_min_error_statement | superuser
log_statement | superuser
(3 rows)
> select version();
version
----------------------------------------------------------------------------------------------
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)
>
> create role foo;
CREATE ROLE
> set role foo;
SET
> set log_statement to 'none';
ERROR: permission denied to set parameter "log_statement"
> set log_min_duration_statement to -1;
ERROR: permission denied to set parameter "log_min_duration_statement"
-->
>
> --Scott
> PostgreSQL database experts
> http://www.openscg.com
>
> Oleg
>
> On Thu, Dec 10, 2015 at 1:40 PM, John R Pierce <pierce@xxxxxxxxxxxx> wrote:
>
> On 12/10/2015 9:58 AM, oleg yusim wrote:
>
> I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my question:
>
> Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, \z, etc?
>
> what the other two gentlemen are trying to say is the metacommands are shortcuts for more complex SQL queries of the pg_catalog schema, so to log them, you
> would need to log all queries and filter for accesses to the pg_catalog....
>
> --
> john r pierce, recycling bits in santa cruz
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@xxxxxxxxxxx
p: 312.241.7800
