Re: Loggingt psql meta-commands

Scott Mead <scottm(at)openscg(dot)com> writes:

> On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim <olegyusim(at)gmail(dot)com> 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(at)hogranch(dot)com> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800