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