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