Re: Loggingt psql meta-commands - Mailing list pgsql-general

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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.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.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>
>         <mailto:david.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>>
>                  <mailto:david.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>
>                  <mailto:david.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>>>> wrote:
>
>                       On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim
>                  <olegyusim@gmail.com <mailto:olegyusim@gmail.com>
>         <mailto:olegyusim@gmail.com <mailto:olegyusim@gmail.com>>
>                       <mailto:olegyusim@gmail.com
>         <mailto:olegyusim@gmail.com>
>                  <mailto:olegyusim@gmail.com
>         <mailto:olegyusim@gmail.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.com>>
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com