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

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>> 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>>> 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>>>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>

--
Adrian Klaver
adrian.klaver@aklaver.com