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 <gsiever...@comcast.net> wrote: > Scott Mead <sco...@openscg.com> writes: > > > On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim <olegyu...@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 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 <pie...@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 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@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: postgres.consult...@comcast.net > p: 312.241.7800 >
