Re: Loggingt psql meta-commands

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(at)comcast(dot)net>
wrote:

> 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
>