Re: Loggingt psql meta-commands

Scott Mead <scottm@openscg.com> writes:

> On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim <olegyusim@gmail.com> wrote:
>
>     Thanks John, I realized that and confirmed in my logs. What I'm trying to determine now, can I only log some
SELECTstatements, 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@hogranch.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
thehere 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
ofthe 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@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800