Re: Loggingt psql meta-commands

Adrian,

I used all those settings you suggested already, and I will suggest to use
logrotate or syslog on top of it, so no more than day worth of log would be
kept in the system. Still, I view it as a big drawback. Do you know of any
third party tools (log monitoring/analyze software, you mentioned),
PostgreSQL integrates nicely with?

Oleg

On Thu, Dec 10, 2015 at 4:45 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 12/10/2015 02:13 PM, oleg yusim wrote:
>
>> Adrian,
>>
>> You seemed to be familiar with the STIG world, so how about V-ID from
>>
>
> No, I am just familiar with how search engines work:)
>
> Database SRG? I'm looking into STIG ID: SRG-APP-000091-DB-000066 right
>> now. Now, I do not really think it is a tall order, since the
>> requirement speaks about explicit calls for privilege/permission/role
>> membership information. Internal checks, which are going on all the time
>> do not count.
>>
>
> http://securityrules.info/about/xivik-hagym-bupih-dityx/SV-42701r3_rule
>
> Hope there is another rule that keeps someone from writing code that
> masquerades as internal checks.
>
> At any rate it seems the immediate solution is going to lie in some sort
> of log monitoring/analyze software that pulls out queries of interest and
> adds them to another file. You can limit the Postgres logs that are kept
> around using the settings here:
>
> http://www.postgresql.org/docs/9.4/interactive/runtime-config-logging.html
>
> Some of which are:
>
> log_rotation_age (integer)
>
> When logging_collector is enabled, this parameter determines the
> maximum lifetime of an individual log file. After this many minutes have
> elapsed, a new log file will be created. Set to zero to disable time-based
> creation of new log files. This parameter can only be set in the
> postgresql.conf file or on the server command line.
>
> log_rotation_size (integer)
>
> When logging_collector is enabled, this parameter determines the
> maximum size of an individual log file. After this many kilobytes have been
> emitted into a log file, a new log file will be created. Set to zero to
> disable size-based creation of new log files. This parameter can only be
> set in the postgresql.conf file or on the server command line.
>
> log_truncate_on_rotation (boolean)
>
> When logging_collector is enabled, this parameter will cause
> PostgreSQL to truncate (overwrite), rather than append to, any existing log
> file of the same name. However, truncation will occur only when a new file
> is being opened due to time-based rotation, not during server startup or
> size-based rotation. When off, pre-existing files will be appended to in
> all cases. For example, using this setting in combination with a
> log_filename like postgresql-%H.log would result in generating twenty-four
> hourly log files and then cyclically overwriting them. This parameter can
> only be set in the postgresql.conf file or on the server command line.
>
> Example: To keep 7 days of logs, one log file per day named
> server_log.Mon, server_log.Tue, etc, and automatically overwrite last
> week's log with this week's log, set log_filename to server_log.%a,
> log_truncate_on_rotation to on, and log_rotation_age to 1440.
>
> Example: To keep 24 hours of logs, one log file per hour, but also
> rotate sooner if the log file size exceeds 1GB, set log_filename to
> server_log.%H%M, log_truncate_on_rotation to on, log_rotation_age to 60,
> and log_rotation_size to 1000000. Including %M in log_filename allows any
> size-driven rotations that might occur to select a file name different from
> the hour's initial file name.
>
>
>
>> Thanks,
>>
>> Oleg
>>
>> On Thu, Dec 10, 2015 at 4:03 PM, Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> On 12/10/2015 01:36 PM, oleg yusim wrote:
>>
>> Adrian,
>>
>> What I hope to achieve is to meet this requirement from Database
>> SRG:
>>
>>
>> So some aspect of this:
>>
>> https://www.stigviewer.com/stig/database_security_requirements_guide/
>>
>> Can you be more specific?
>>
>>
>> /Review DBMS documentation to verify that audit records can be
>> produced
>> when privileges/permissions/role memberships are retrieved./
>>
>>
>> That is a tall order, that is an almost constant process.
>>
>> /
>> /
>> To do that I would need to enable logging of such commands as
>> \du, \dp,
>> \z. At the same time, I do not want to get 20 GB of logs on the
>> daily
>> basis, by setting log_statement = 'all'. So, I'm trying to find
>> a way in
>> between.
>>
>>
>> Any way you look at this is going to require pulling in and
>> analyzing a great deal of information. That is why I asked for the
>> specific requirement, to help determine exactly what is being
>> required?
>>
>>
>> Thanks,
>>
>> Oleg
>>
>>
>>
>> On Thu, Dec 10, 2015 at 3:29 PM, Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> wrote:
>>
>> On 12/10/2015 12:56 PM, oleg yusim wrote:
>>
>> So what I want to accomplish is logging queries for
>> roles/privileges
>> with minimal increasing volume of logs along the way.
>> The idea I got
>> from responses in this thread so far is:
>>
>> 1) Set log_statement on postgresql.conf to 'mod'
>> 2) Raise log_statement to 'all' but only for postgres
>> superuser
>>
>> What seems to be open questions to me with this model:
>>
>> 1) Way to check what log_statement set to on per user
>> basis
>> (what table
>> should I query?)
>> 2) Way to ensure that only superuser can run meta
>> commands, such
>> as \du,
>> \dp, \z
>>
>>
>> Maybe if you tell us what you hope to achieve, monitoring
>> or access
>> denial and to what purpose, it might be possible to come up
>> with a
>> more complete answer.
>>
>>
>> Thanks,
>>
>> Oleg
>>
>> On Thu, Dec 10, 2015 at 2:50 PM, David G. Johnston
>> <david(dot)g(dot)johnston(at)gmail(dot)com
>> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>
>> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com
>> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>>
>> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com
>> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>
>> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com
>> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>>>> wrote:
>>
>> On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim
>> <olegyusim(at)gmail(dot)com <mailto:olegyusim(at)gmail(dot)com>
>> <mailto:olegyusim(at)gmail(dot)com <mailto:olegyusim(at)gmail(dot)com>>
>> <mailto:olegyusim(at)gmail(dot)com
>> <mailto:olegyusim(at)gmail(dot)com>
>> <mailto:olegyusim(at)gmail(dot)com
>> <mailto:olegyusim(at)gmail(dot)com>>>>wrote:
>>
>> Hi David,
>>
>> Can you, please, give me example?
>>
>>
>> ​Not readily...maybe others can. Putting forth
>> specific
>> examples of
>> what you want to accomplish may help.
>>
>> David J.​
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>