Thread: Loggingt psql meta-commands

Loggingt psql meta-commands

From
oleg yusim
Date:
Greetings!

I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my question:

Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, \z, etc?

Thanks,

Oleg

Re: Loggingt psql meta-commands

From
Adrian Klaver
Date:
On 12/10/2015 09:58 AM, oleg yusim wrote:
> Greetings!
>
> I'm new to PostgreSQL, working on it from the point of view of Cyber
> Security assessment. In regards to the here is my question:
>
> Is it a way to enable logging for psql prompt meta-commands, such as
> \du, \dp, \z, etc?

aklaver@killi:~> psql -d test -E
psql (9.4.4)
Type "help" for help.

aklaver@test=> \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
   r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
   r.rolconnlimit, r.rolvaliduntil,
   ARRAY(SELECT b.rolname
         FROM pg_catalog.pg_auth_members m
         JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
         WHERE m.member = r.oid) as memberof
, r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************


postgresql.conf

log_statement = 'all'


aklaver-2015-12-10 10:12:45.177 PST-0LOG:  statement: SELECT r.rolname,
r.rolsuper, r.rolinherit,
           r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
           r.rolconnlimit, r.rolvaliduntil,
           ARRAY(SELECT b.rolname
                 FROM pg_catalog.pg_auth_members m
                 JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
                 WHERE m.member = r.oid) as memberof
         , r.rolreplication
         FROM pg_catalog.pg_roles r
         ORDER BY 1;

>
> Thanks,
>
> Oleg


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Loggingt psql meta-commands

From
Andreas Kretschmer
Date:
oleg yusim <olegyusim@gmail.com> wrote:

> Greetings!
>
> I'm new to PostgreSQL, working on it from the point of view of Cyber Security
> assessment. In regards to the here is my question:
>
> Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp,
> \z, etc?

start psql with -E


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


Re: Loggingt psql meta-commands

From
John R Pierce
Date:
On 12/10/2015 9:58 AM, oleg yusim wrote:
> I'm new to PostgreSQL, working on it from the point of view of Cyber
> Security assessment. In regards to the here is my question:
>
> Is it a way to enable logging for psql prompt meta-commands, such as
> \du, \dp, \z, etc?

what the other two gentlemen are trying to say is the metacommands are
shortcuts for more complex SQL queries of the pg_catalog schema, so to
log them, you would need to log all queries and filter for accesses to
the pg_catalog....



--
john r pierce, recycling bits in santa cruz



Re: Loggingt psql meta-commands

From
oleg yusim
Date:
Andreas, Andrian,

Thank you very much for both pieces of information. It was very helpful. Now, let me ask you one more question on the same topic. Is it more granular way to control logging PosgreSQL provides, or I pretty much reduced to choosing between mod and all? 

The reason I'm asking is because with 'all" volume of daily logging becomes truly ginormous. And for my purposes, I really do not need all the SELECT statements to be logged. Only those, which are responsible for explicit querying of role/privileges/roles (so, \du, \dp, \z, in essence).

Thanks,

Oleg

On Thu, Dec 10, 2015 at 12:14 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
oleg yusim <olegyusim@gmail.com> wrote:

> Greetings!
>
> I'm new to PostgreSQL, working on it from the point of view of Cyber Security
> assessment. In regards to the here is my question:
>
> Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp,
> \z, etc?

start psql with -E


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Loggingt psql meta-commands

From
oleg yusim
Date:
Thanks John, I realized that and confirmed in my logs. What I'm trying to determine now, can I only log some SELECT statements, or I should log all of them or none of them.

Oleg

On Thu, Dec 10, 2015 at 1:40 PM, John R Pierce <pierce@hogranch.com> wrote:
On 12/10/2015 9:58 AM, oleg yusim wrote:
I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my question:

Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, \z, etc?

what the other two gentlemen are trying to say is the metacommands are shortcuts for more complex SQL queries of the pg_catalog schema, so to log them, you would need to log all queries and filter for accesses to the pg_catalog....



--
john r pierce, recycling bits in santa cruz




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Loggingt psql meta-commands

From
Pavel Stehule
Date:


2015-12-10 20:49 GMT+01:00 oleg yusim <olegyusim@gmail.com>:
Andreas, Andrian,

Thank you very much for both pieces of information. It was very helpful. Now, let me ask you one more question on the same topic. Is it more granular way to control logging PosgreSQL provides, or I pretty much reduced to choosing between mod and all? 

The reason I'm asking is because with 'all" volume of daily logging becomes truly ginormous. And for my purposes, I really do not need all the SELECT statements to be logged. Only those, which are responsible for explicit querying of role/privileges/roles (so, \du, \dp, \z, in essence).

There is nothing similar.

But if you can recompile psql - you can hack psql - it should be small simple patch (less than 100 lines).

Regards

Pavel
 

Thanks,

Oleg

On Thu, Dec 10, 2015 at 12:14 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
oleg yusim <olegyusim@gmail.com> wrote:

> Greetings!
>
> I'm new to PostgreSQL, working on it from the point of view of Cyber Security
> assessment. In regards to the here is my question:
>
> Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp,
> \z, etc?

start psql with -E


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Loggingt psql meta-commands

From
Scott Mead
Date:
On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim <olegyusim@gmail.com> wrote:
Thanks John, I realized that and confirmed in my logs. What I'm trying to determine now, can I only log some SELECT statements, or I should log all of them or none of them.

You can configure this to multiple levels:

 Global, per-user, per-database

ALTER USER postgres SET log_min_duration_statement=0;
ALTER DATABASE xyz SET log_min_duration_statement=0;

  That being said, you would want to make sure that the user issuing the largest volume of queries is not set with this, otherwise, you could potential flood your logs with every single query issued.  This has a tendency to cause performance problems.  The other item of note is that, once logged in, the user could change that value with a similar ALTER statement.


--Scott
PostgreSQL database experts

Oleg

On Thu, Dec 10, 2015 at 1:40 PM, John R Pierce <pierce@hogranch.com> wrote:
On 12/10/2015 9:58 AM, oleg yusim wrote:
I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my question:

Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, \z, etc?

what the other two gentlemen are trying to say is the metacommands are shortcuts for more complex SQL queries of the pg_catalog schema, so to log them, you would need to log all queries and filter for accesses to the pg_catalog....



--
john r pierce, recycling bits in santa cruz




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Loggingt psql meta-commands

From
Adrian Klaver
Date:
On 12/10/2015 11:49 AM, oleg yusim wrote:
> Andreas, Andrian,
>
> Thank you very much for both pieces of information. It was very helpful.
> Now, let me ask you one more question on the same topic. Is it more
> granular way to control logging PosgreSQL provides, or I pretty much
> reduced to choosing between mod and all?

Yes, overall your choices are none, ddl, mod, and all.

>
> The reason I'm asking is because with 'all" volume of daily logging
> becomes truly ginormous. And for my purposes, I really do not need all
> the SELECT statements to be logged. Only those, which are responsible
> for explicit querying of role/privileges/roles (so, \du, \dp, \z, in
> essence).

Take a look here:

http://www.postgresql.org/docs/current/static/logfile-maintenance.html

at bottom are links to pgBadger and Nagios plugin check_postgres. I have
used neither, so cannot comment on their useability. Still something
like them could be used to harvest log entries into a more condensed
file and then allow you to get rid of the old Postgres log files sooner.

>
> Thanks,
>
> Oleg
>
> On Thu, Dec 10, 2015 at 12:14 PM, Andreas Kretschmer
> <akretschmer@spamfence.net <mailto:akretschmer@spamfence.net>> wrote:
>
>     oleg yusim <olegyusim@gmail.com <mailto:olegyusim@gmail.com>> wrote:
>
>     > Greetings!
>     >
>     > I'm new to PostgreSQL, working on it from the point of view of Cyber Security
>     > assessment. In regards to the here is my question:
>     >
>     > Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp,
>     > \z, etc?
>
>     start psql with -E
>
>
>     Andreas
>     --
>     Really, I'm not out to destroy Microsoft. That will just be a completely
>     unintentional side effect.                              (Linus Torvalds)
>     "If I was god, I would recompile penguin with --enable-fly."   (unknown)
>     Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Loggingt psql meta-commands

From
Jerry Sievers
Date:
Scott Mead <scottm@openscg.com> writes:

> On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim <olegyusim@gmail.com> wrote:
>
>     Thanks John, I realized that and confirmed in my logs. What I'm trying to determine now, can I only log some
SELECTstatements, or I should log all of them or none 
>     of them.
>
> You can configure this to multiple levels:
>
>  Global, per-user, per-database
>
> ALTER USER postgres SET log_min_duration_statement=0;
> ALTER DATABASE xyz SET log_min_duration_statement=0;
>
>   That being said, you would want to make sure that the user
> issuing the largest volume of queries is not set with this, otherwise,
> you could potential flood your logs with every single query
> issued.  This has a tendency to cause performance
> problems. 

> The other item of note is that, once logged in, the
> user could change that value with a similar ALTER statement.

No, not really.  Unprivileged users can't frob those settings.


> select name, context from pg_settings where name ~ '^log.*statement$' order by 1;
            name            |  context
----------------------------+-----------
 log_min_duration_statement | superuser
 log_min_error_statement    | superuser
 log_statement              | superuser
(3 rows)

> select version();
                                           version
----------------------------------------------------------------------------------------------
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)

>


> create role foo;
CREATE ROLE
> set role foo;
SET
> set log_statement to 'none';
ERROR:  permission denied to set parameter "log_statement"
> set log_min_duration_statement to -1;
ERROR:  permission denied to set parameter "log_min_duration_statement"
>

>
> --Scott
> PostgreSQL database experts
> http://www.openscg.com
>
>     Oleg
>
>     On Thu, Dec 10, 2015 at 1:40 PM, John R Pierce <pierce@hogranch.com> wrote:
>
>         On 12/10/2015 9:58 AM, oleg yusim wrote:
>
>             I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to
thehere is my question: 
>
>             Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, \z, etc?
>
>         what the other two gentlemen are trying to say is the metacommands are shortcuts for more complex SQL queries
ofthe pg_catalog schema, so to log them, you 
>         would need to log all queries and filter for accesses to the pg_catalog....
>
>         --
>         john r pierce, recycling bits in santa cruz
>
>         --
>         Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


Re: Loggingt psql meta-commands

From
oleg yusim
Date:
Hmm... let me see if I got it right. I can set log_statement to mod overall, and then modify it as ALTER USER postgres SET log_statement=all; for postgres only? Also... how do we control who can run meta commands?

Thanks,

Oleg

On Thu, Dec 10, 2015 at 2:16 PM, Jerry Sievers <gsievers19@comcast.net> wrote:
Scott Mead <scottm@openscg.com> writes:

> On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim <olegyusim@gmail.com> wrote:
>
>     Thanks John, I realized that and confirmed in my logs. What I'm trying to determine now, can I only log some SELECT statements, or I should log all of them or none
>     of them.
>
> You can configure this to multiple levels:
>
>  Global, per-user, per-database
>
> ALTER USER postgres SET log_min_duration_statement=0;
> ALTER DATABASE xyz SET log_min_duration_statement=0;
>
>   That being said, you would want to make sure that the user
> issuing the largest volume of queries is not set with this, otherwise,
> you could potential flood your logs with every single query
> issued.  This has a tendency to cause performance
> problems. 

> The other item of note is that, once logged in, the
> user could change that value with a similar ALTER statement.

No, not really.  Unprivileged users can't frob those settings.


> select name, context from pg_settings where name ~ '^log.*statement$' order by 1;
            name            |  context
----------------------------+-----------
 log_min_duration_statement | superuser
 log_min_error_statement    | superuser
 log_statement              | superuser
(3 rows)

> select version();
                                           version
----------------------------------------------------------------------------------------------
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)

>


> create role foo;
CREATE ROLE
> set role foo;
SET
> set log_statement to 'none';
ERROR:  permission denied to set parameter "log_statement"
> set log_min_duration_statement to -1;
ERROR:  permission denied to set parameter "log_min_duration_statement"
>

>
> --Scott
> PostgreSQL database experts
> http://www.openscg.com
>
>     Oleg
>
>     On Thu, Dec 10, 2015 at 1:40 PM, John R Pierce <pierce@hogranch.com> wrote:
>
>         On 12/10/2015 9:58 AM, oleg yusim wrote:
>
>             I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my question:
>
>             Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, \z, etc?
>
>         what the other two gentlemen are trying to say is the metacommands are shortcuts for more complex SQL queries of the pg_catalog schema, so to log them, you
>         would need to log all queries and filter for accesses to the pg_catalog....
>
>         --
>         john r pierce, recycling bits in santa cruz
>
>         --
>         Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

Re: Loggingt psql meta-commands

From
"David G. Johnston"
Date:
On Thu, Dec 10, 2015 at 1:20 PM, oleg yusim <olegyusim@gmail.com> wrote:
Also... how do we control who can run meta commands?

​You cannot do so directly but you can control permissions to the underlying schema that the meta-command queries touch so that attempting to run the meta-command fails.

This is not as simple as it may seem; visibility of the schema is considerably more permissive than visibility of data.

David J.​
 

Re: Loggingt psql meta-commands

From
oleg yusim
Date:
Hi David,

Can you, please, give me example?

Thanks,

Oleg

On Thu, Dec 10, 2015 at 2:25 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Dec 10, 2015 at 1:20 PM, oleg yusim <olegyusim@gmail.com> wrote:
Also... how do we control who can run meta commands?

​You cannot do so directly but you can control permissions to the underlying schema that the meta-command queries touch so that attempting to run the meta-command fails.

This is not as simple as it may seem; visibility of the schema is considerably more permissive than visibility of data.

David J.​
 


Re: Loggingt psql meta-commands

From
"David G. Johnston"
Date:
On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim <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.​
 

Re: Loggingt psql meta-commands

From
oleg yusim
Date:
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

Thanks,

Oleg

On Thu, Dec 10, 2015 at 2:50 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim <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.​
 

Re: Loggingt psql meta-commands

From
"David G. Johnston"
Date:
On Thu, Dec 10, 2015 at 1:56 PM, oleg yusim <olegyusim@gmail.com> 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


​Can you maybe bottom (or inline) post like the rest of us, please?


2) Again, not easy if it is indeed possible without source-code hacking​.  psql itself will not limit the user's ability to execute the command.

David J.

Re: Loggingt psql meta-commands

From
Adrian Klaver
Date:
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>> wrote:
>
>     On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim <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


Re: Loggingt psql meta-commands

From
oleg yusim
Date:
Adrian,

What I hope to achieve is to meet this requirement from Database SRG:

Review DBMS documentation to verify that audit records can be produced when privileges/permissions/role memberships are retrieved.

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.

Thanks,

Oleg



On Thu, Dec 10, 2015 at 3:29 PM, Adrian Klaver <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>> wrote:

    On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim <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

Re: Loggingt psql meta-commands

From
John R Pierce
Date:
On 12/10/2015 12:56 PM, oleg yusim wrote:
> 2) Way to ensure that only superuser can run meta commands, such as
> \du, \dp, \z

Those metacommands only exist in the psql command line tool, so to only
restrict access to them, you'd need to modify that tool. however, that
wouldn't stop users from directly querying pg_catalog.

Regular users HAVE to be able to read the pg_catalog metadata, as they
are used by many wrapper libraries such as ORM's to get table
definitions and such.   The SQL standard information_schema is
implemented as views of pg_catalog.




--
john r pierce, recycling bits in santa cruz



Re: Loggingt psql meta-commands

From
Adrian Klaver
Date:
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


Re: Loggingt psql meta-commands

From
oleg yusim
Date:
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.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>> 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

Re: Loggingt psql meta-commands

From
Tom Lane
Date:
oleg yusim <olegyusim@gmail.com> writes:
> What I hope to achieve is to meet this requirement from Database SRG:
> *Review DBMS documentation to verify that audit records can be produced
> when privileges/permissions/role memberships are retrieved.*

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

As multiple people have noted, it's a serious error to imagine that your
requirement is "log \du etc".  Those are just handy macros for queries on
the system catalogs, which could also be done in other ways.  What you
seem to need is server-side logging of queries that access specific system
catalog columns.  There's no out-of-the-box facility for that right now,
short of log_statement = all which you've already rejected.

It'd be possible to write a C-code extension that did something like
that, and some work in that direction has already gone on; the pg_audit
extension that didn't quite get into 9.5 might come close to your
requirements.

            regards, tom lane


Re: Loggingt psql meta-commands

From
Adrian Klaver
Date:
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


Re: Loggingt psql meta-commands

From
John R Pierce
Date:
On 12/10/2015 2:03 PM, Adrian Klaver wrote:
>
> So some aspect of this:
>
> https://www.stigviewer.com/stig/database_security_requirements_guide/

thats a rather insane bunch of requirements.   Reads like a wish list by
academic security researchers.

for instance
https://www.stigviewer.com/stig/database_security_requirements_guide/2015-06-23/finding/V-58123

??!?   The database server has no clue about the difference between an
"application that it supports" and a user directly querying.  The PSQL
shell, or dbadmin, is an 'application that it supports'.

at this point, speaking purely as a interested outsider (I am in no way
representing hte PG Development Group), I'd guess PostgreSQL probably
doesn't meet 2/3rds of those 'findings'.   I truly wonder if any
standard RDBMS supports all or even most of them?!?



--
john r pierce, recycling bits in santa cruz



Re: Loggingt psql meta-commands

From
oleg yusim
Date:
John, 

I can answer that - Oracle and MS SQL do, or at least there were able to convince DISA that they do (STIGs for them are present here: http://iase.disa.mil/stigs/Pages/a-z.aspx). That actually benefits those products greatly - from the point of view of security they, once hardened, meet Federal security requirements and such can be used in multiple products other DBs can't (for that very reason).

Thanks,

Oleg 

On Thu, Dec 10, 2015 at 4:52 PM, John R Pierce <pierce@hogranch.com> wrote:
On 12/10/2015 2:03 PM, Adrian Klaver wrote:

So some aspect of this:

https://www.stigviewer.com/stig/database_security_requirements_guide/

thats a rather insane bunch of requirements.   Reads like a wish list by academic security researchers.

for instance
https://www.stigviewer.com/stig/database_security_requirements_guide/2015-06-23/finding/V-58123

??!?   The database server has no clue about the difference between an "application that it supports" and a user directly querying.  The PSQL shell, or dbadmin, is an 'application that it supports'.

at this point, speaking purely as a interested outsider (I am in no way representing hte PG Development Group), I'd guess PostgreSQL probably doesn't meet 2/3rds of those 'findings'.   I truly wonder if any standard RDBMS supports all or even most of them?!?




--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Loggingt psql meta-commands

From
oleg yusim
Date:
Thanks Tom, I get what you are saying and that seems to be final at this stage. I will write pg_audit down, though.

Oleg

On Thu, Dec 10, 2015 at 4:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
oleg yusim <olegyusim@gmail.com> writes:
> What I hope to achieve is to meet this requirement from Database SRG:
> *Review DBMS documentation to verify that audit records can be produced
> when privileges/permissions/role memberships are retrieved.*

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

As multiple people have noted, it's a serious error to imagine that your
requirement is "log \du etc".  Those are just handy macros for queries on
the system catalogs, which could also be done in other ways.  What you
seem to need is server-side logging of queries that access specific system
catalog columns.  There's no out-of-the-box facility for that right now,
short of log_statement = all which you've already rejected.

It'd be possible to write a C-code extension that did something like
that, and some work in that direction has already gone on; the pg_audit
extension that didn't quite get into 9.5 might come close to your
requirements.

                        regards, tom lane

Re: Loggingt psql meta-commands

From
oleg yusim
Date:
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.klaver@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.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

Re: Loggingt psql meta-commands

From
Andrew Sullivan
Date:
On Thu, Dec 10, 2015 at 02:52:38PM -0800, John R Pierce wrote:
> thats a rather insane bunch of requirements.   Reads like a wish list by
> academic security researchers.

Well, I don't know.  Might be a wish list by insurance adjusters who
want to minimise liability.  At least in the United States of
Liability, I think the day is rapidly approaching where people will
need insurance against database breaches.  Therefore,

> https://www.stigviewer.com/stig/database_security_requirements_guide/2015-06-23/finding/V-58123
>
> ??!?   The database server has no clue about the difference between an
> "application that it supports" and a user directly querying.  The PSQL
> shell, or dbadmin, is an 'application that it supports'.

while I agree that there's no way for the RDBMS to tell when it's an
attacker mimicing an application's normal connection and query
pattern, I think there are some things you could do here that would
catch this.  For instance,

    • use strong authentication mechanisms for your clients.  Kerberos
      seems like a good alternative, but TLS (SSL) certificates might
      do.  Log connections and the connection origin.  If you get
      connections for a given user from the wrong place, you know you
      have a problem.

    • isolate your users, so that your application (or better, each
      instance of your application) has an associated user.  Your
      humans are not allowed to log in with this username.  Then,
      queries issued by non-application usernames are your candidate
      queries.

Again, this will not defend against, "Attacker got into my system and
subverted the application user."  You need to have other lines of
defence for that.  But depending on your auditor, this might be enough
to satisfy the condition.

Also, of course, there is the application_name (string) parameter.  In
principle, you ought to be able to filter on this.  Again, won't help
you if your application login is somehow compromised.

I agree that all of this depends on logging everything and filtering,
however.

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca


Re: Loggingt psql meta-commands

From
Adrian Klaver
Date:
On 12/10/2015 02:58 PM, oleg yusim wrote:
> John,
>
> I can answer that - Oracle and MS SQL do, or at least there were able to
> convince DISA that they do (STIGs for them are present here:
> http://iase.disa.mil/stigs/Pages/a-z.aspx). That actually benefits those
> products greatly - from the point of view of security they, once
> hardened, meet Federal security requirements and such can be used in
> multiple products other DBs can't (for that very reason).


Caveats apply.

Re: Loggingt psql meta-commands

From
Adrian Klaver
Date:
On 12/10/2015 03:02 PM, oleg yusim wrote:
> 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?

Well just the ones I mentioned previously and found here:

http://www.postgresql.org/docs/9.4/interactive/logfile-maintenance.html

>
> Oleg
>



--
Adrian Klaver
adrian.klaver@aklaver.com