Loggingt psql meta-commands

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

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

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

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

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:

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:

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

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
http://www.openscg.com

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

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

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

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

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:

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

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

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

1)
http://www.postgresql.org/docs/9.4/interactive/catalog-pg-db-role-setting.html

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.

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

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

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

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