Re: Loggingt psql meta-commands

Adrian,

You seemed to be familiar with the STIG world, so how about V-ID from
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.

Thanks,

Oleg

On Thu, Dec 10, 2015 at 4:03 PM, Adrian Klaver <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>> 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>>> 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>>>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>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>