[GENERAL] Fwd: Data Packaging/Data Unpacking

[oleg yusim]

Appologies, for posting it again, but I didn't get any responses so far.
Looks like I posted it too late in the evening and it went not noticed.

Oleg

-- Forwarded message --
From: oleg yusim <olegyu...@gmail.com>
Date: Tue, Jan 12, 2016 at 10:00 PM
Subject: Data Packaging/Data Unpacking
To: PostgreSQL General <pgsql-general@postgresql.org>


Greetings,

I have matching couple of security requirements, speaking about preserving
data confidentiality and integrity in PostgreSQL DB during packaging for
transmission / unpacking from transmission.

Important: let's assume data at rest is encrypted using EFS and data at
transit is encrypted using ciphers, provided by OpenSSL.

So, with that in mind, please, help me to understand movement and location
of the data between the moment when it is pulled from file system and
encrypted as network package going through the SSL tunnel.

And reversing it - between the moment network package arrived through the
SSL tunnel is decrypted and the moment its content is placed into the file
system.



For those interested, here are requirements themselves, quoted:

1) The DBMS must maintain the confidentiality and integrity of information
during preparation for transmission.

Information can be either unintentionally or maliciously disclosed or
modified during preparation for transmission, including, for example,
during aggregation, at protocol transformation points, and during
packing/unpacking. These unauthorized disclosures or modifications
compromise the confidentiality or integrity of the information.

Use of this requirement will be limited to situations where the data owner
has a strict requirement for ensuring data integrity and confidentiality is
maintained at every step of the data transfer and handling process.

When transmitting data, the DBMS, associated applications, and
infrastructure must leverage transmission protection mechanisms.

2) The DBMS must maintain the confidentiality and integrity of information
during reception.

Information can be either unintentionally or maliciously disclosed or
modified during reception, including, for example, during aggregation, at
protocol transformation points, and during packing/unpacking. These
unauthorized disclosures or modifications compromise the confidentiality or
integrity of the information.

This requirement applies only to those applications that are either
distributed or can allow access to data non-locally. Use of this
requirement will be limited to situations where the data owner has a strict
requirement for ensuring data integrity and confidentiality is maintained
at every step of the data transfer and handling process.

When receiving data, the DBMS, associated applications, and infrastructure
must leverage protection mechanisms.


Thanks,

Oleg

Re: [GENERAL] Data Packaging/Data Unpacking

[oleg yusim]

Kevin,

Thank you for your reply. I understand what you are saying, but I guess I
need a bit deeper understanding for my assessment. Let's dive a bit here:

Say, I got network package. The package was decrypted by OpenSSL. Where
this data are, physically, at this moment?

Opposite situation: we are about to send results of SELECT statement over
the OpenSSL tunnel. Where are the data, physically, between the moment DB
engine returned results for my query and moment the package was encrypted
by OpenSSL?

Thanks,

Oleg

On Wed, Jan 13, 2016 at 2:46 PM, Kevin Grittner <kgri...@gmail.com> wrote:

> On Tue, Jan 12, 2016 at 10:00 PM, oleg yusim <olegyu...@gmail.com> wrote:
>
> > Important: let's assume data at rest is encrypted using EFS and data at
> > transit is encrypted using ciphers, provided by OpenSSL.
> >
> > So, with that in mind, please, help me to understand movement and
> location
> > of the data between the moment when it is pulled from file system and
> > encrypted as network package going through the SSL tunnel.
> >
> > And reversing it - between the moment network package arrived through the
> > SSL tunnel is decrypted and the moment its content is placed into the
> file
> > system.
>
> At all times the data is present only in files owned by the OS user
> which runs the database server or in RAM allocated to processes run
> by that user.  Files and RAM are freed without overwrite; we count
> on the OS to not gratuitously show the old values to processes
> making new allocations.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [GENERAL] Data Packaging/Data Unpacking

[oleg yusim]

OK, Kevin, David,

Thanks you very much for explanation. Now who is the owner of this process?
My understanding is, data then located physically in RAM, in the memory
stack assigned by OS to this process. Now the question is who owns the
process?

Thanks,

Oleg

On Wed, Jan 13, 2016 at 3:29 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Jan 13, 2016 at 2:19 PM, Kevin Grittner <kgri...@gmail.com> wrote:
>
>> On Wed, Jan 13, 2016 at 2:57 PM, oleg yusim <olegyu...@gmail.com> wrote:
>>
>> > Say, I got network package. The package was decrypted by OpenSSL. Where
>> this
>> > data are, physically, at this moment?
>>
>> Process-local memory for the PostgreSQL backend process associated
>> with the database connection.
>>
>> > Opposite situation: we are about to send results of SELECT statement
>> over
>> > the OpenSSL tunnel. Where are the data, physically, between the moment
>> DB
>> > engine returned results for my query and moment the package was
>> encrypted by
>> > OpenSSL?
>>
>> Process-local memory for the PostgreSQL backend process associated
>> with the database connection.
>>
>
> ​In other words (?) while OpenSSL may be a separate project the method of
> its use involves loading all relevant code into the process space​
>
> ​of PostgreSQL - specifically the process that was forked when the SSL
> client connection was established.
>
> David J.​
>

Re: [GENERAL] Data Packaging/Data Unpacking

[oleg yusim]

David,

Answer "postgres" would suffice. I have fairly decent idea of what happens
below that (you are right system utility - Memory Manager is what
marshaling data back and forth to RAM, abstracting absolute addresses from
application along the way, and once in RAM, security kernel of the system
protects data, terminating any process which tries to obtain data from
another process - provided data were not shared to begin with).

Thanks a lot for your consultation guys.

Oleg

On Wed, Jan 13, 2016 at 3:49 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Jan 13, 2016 at 2:37 PM, oleg yusim <olegyu...@gmail.com> wrote:
>
>> OK, Kevin, David,
>>
>> Thanks you very much for explanation. Now who is the owner of this
>> process? My understanding is, data then located physically in RAM, in the
>> memory stack assigned by OS to this process. Now the question is who owns
>> the process?
>>
>>
> Who owns the PostgreSQL backend user process?​
>
> ​Typically the ​"postgres" user.
> Who owns the "process" responsible for marshalling data back and forth
> from RAM?  There isn't really a concept of "process" when dealing with
> system resources.  The kernel is responsible for management of those
> resources.  Processes are a user-space concern.
>
> There may be some here concerned with the deep workings of the various O/S
> that PostgreSQL runs on but ultimately the answer, from PostgreSQL's
> perspective, is that we use system APIs to request and use resources and
> expect the underlying system to handle those securely.  If you want to know
> how that layer works you should ask there since it seems out-of-scope for
> this forum.
>
> David J.
>
>

Re: [GENERAL] Data Packaging/Data Unpacking

[oleg yusim]

Kevin,

It is understood. But doesn't change things from my perspective. It would
anyway be user, with sole access, nobody can elevate privileges to and only
root can su to. The name, in that case, is irrelevant, but answer postgres
gave me idea of the protection level here.

Thanks,

Oleg

On Wed, Jan 13, 2016 at 4:04 PM, Kevin Grittner <kgri...@gmail.com> wrote:

> On Wed, Jan 13, 2016 at 3:54 PM, oleg yusim <olegyu...@gmail.com> wrote:
>
> > Answer "postgres" would suffice.
>
> But the user would not always be "postgres".  To be accurate, it is
> the user which owns the files for the "cluster" (database instance)
> and which runs the database service.  If a machine contains
> multiple clusters it is (IMO) best practice, for both security and
> operational reasons, to use a separate OS user for each cluster.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

[GENERAL] Data Packaging/Data Unpacking

[oleg yusim]

Greetings,

I have matching couple of security requirements, speaking about preserving
data confidentiality and integrity in PostgreSQL DB during packaging for
transmission / unpacking from transmission.

Important: let's assume data at rest is encrypted using EFS and data at
transit is encrypted using ciphers, provided by OpenSSL.

So, with that in mind, please, help me to understand movement and location
of the data between the moment when it is pulled from file system and
encrypted as network package going through the SSL tunnel.

And reversing it - between the moment network package arrived through the
SSL tunnel is decrypted and the moment its content is placed into the file
system.



For those interested, here are requirements themselves, quoted:

1) The DBMS must maintain the confidentiality and integrity of information
during preparation for transmission.

Information can be either unintentionally or maliciously disclosed or
modified during preparation for transmission, including, for example,
during aggregation, at protocol transformation points, and during
packing/unpacking. These unauthorized disclosures or modifications
compromise the confidentiality or integrity of the information.

Use of this requirement will be limited to situations where the data owner
has a strict requirement for ensuring data integrity and confidentiality is
maintained at every step of the data transfer and handling process.

When transmitting data, the DBMS, associated applications, and
infrastructure must leverage transmission protection mechanisms.

2) The DBMS must maintain the confidentiality and integrity of information
during reception.

Information can be either unintentionally or maliciously disclosed or
modified during reception, including, for example, during aggregation, at
protocol transformation points, and during packing/unpacking. These
unauthorized disclosures or modifications compromise the confidentiality or
integrity of the information.

This requirement applies only to those applications that are either
distributed or can allow access to data non-locally. Use of this
requirement will be limited to situations where the data owner has a strict
requirement for ensuring data integrity and confidentiality is maintained
at every step of the data transfer and handling process.

When receiving data, the DBMS, associated applications, and infrastructure
must leverage protection mechanisms.


Thanks,

Oleg

Re: [GENERAL] Failing to known state

[oleg yusim]

Hi Adrian,

Thank you very much for that link. It confirms what JD and John said, plus
explains couple other moments to me.

Thanks,

Oleg

On Tue, Jan 5, 2016 at 7:04 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 01/05/2016 04:12 PM, oleg yusim wrote:
> > Hi Adrian,
> >
> > I meant a scenario, when user is trying to connect to database (doesn't
> > matter what interface) and database fails at this moment. If all
> > authentication/authorization/validation functions are written to return
> > false in case of abnormal termination, we are fine. If not, we can
> > potentially encounter the situation when database fails into state where
> > user is given greater privileges than he/she should or even
> > authenticated, when he/she shouldn't.
>
> Might want to take a look at:
>
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/postmaster/postmaster.c;h=41dbdf4bf9eeb54ae0a774ab21fc1c1362aa55f9;hb=d25c7d70ff46d1b2f2400f29d100190efe84d70d
>
> /*
>  * CleanupBackend -- cleanup after terminated backend.
>  *
>  * Remove all local state associated with backend.
>  *
>  * If you change this, see also CleanupBackgroundWorker.
>  */
> static void
> CleanupBackend
>
>
> /*
>  * HandleChildCrash -- cleanup after failed backend, bgwriter,
> checkpointer,
>  * walwriter, autovacuum, or background worker.
>  *
>  * The objectives here are to clean up our local state about the child
>  * process, and to signal all other remaining children to quickdie.
>  */
> static void
> HandleChildCrash(in
>
> etc
>
> Just do a find on crash.
>
>
> >
> > Thanks,
> >
> > Oleg
> >
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Failing to known state

[oleg yusim]

Hi Joe,

Exactly how I marked it :)

Thanks,

Oleg

On Tue, Jan 5, 2016 at 6:50 PM, Joe Conway <m...@joeconway.com> wrote:

> On 01/05/2016 04:32 PM, John R Pierce wrote:
> > On 1/5/2016 4:12 PM, oleg yusim wrote:
> >> I meant a scenario, when user is trying to connect to database
> >> (doesn't matter what interface) and database fails at this moment. If
> >> all authentication/authorization/validation functions are written to
> >> return false in case of abnormal termination, we are fine. If not, we
> >> can potentially encounter the situation when database fails into state
> >> where user is given greater privileges than he/she should or even
> >> authenticated, when he/she shouldn't.
> >
> > if the postgres server processes terminate for any reason, there's
> > nothing to connect to.  the client application will get a error like
> > 'connection refused' back from the connection attempt, or if it was
> > already connected and the server aborts, the next query will return an
> > error like CONNECTION_BAD.  there's no possible privilege elevation.
>
> +1
>
> I think you can call this one "Applicable -- Inherently Meets"
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

Re: [GENERAL] Failing to known state

[oleg yusim]

John,

Thanks, what you are saying makes sense. I agree, it would cause all user
to go through authentication/authorization loop all over and terminate all
running transactions too.

Thanks,

Oleg

On Tue, Jan 5, 2016 at 6:32 PM, John R Pierce <pie...@hogranch.com> wrote:

> On 1/5/2016 4:12 PM, oleg yusim wrote:
>
> I meant a scenario, when user is trying to connect to database (doesn't
> matter what interface) and database fails at this moment. If all
> authentication/authorization/validation functions are written to return
> false in case of abnormal termination, we are fine. If not, we can
> potentially encounter the situation when database fails into state where
> user is given greater privileges than he/she should or even authenticated,
> when he/she shouldn't.
>
>
>
>
>
> if the postgres server processes terminate for any reason, there's nothing
> to connect to.  the client application will get a error like
> 'connection refused' back from the connection attempt, or if it was already
> connected and the server aborts, the next query will return an error like
> CONNECTION_BAD.  there's no possible privilege elevation.
>
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>

Re: [GENERAL] Failing to known state

[oleg yusim]

Thanks JD.

Let me confirm I got you right. So, by exception you mean the
authentication/authorization/validation functions would return false in
case of DB failure?

Thanks,

Oleg


On Tue, Jan 5, 2016 at 5:33 PM, Joshua D. Drake <j...@commandprompt.com>
wrote:

> On 01/05/2016 03:21 PM, oleg yusim wrote:
>
>> Thanks JD.
>>
>>  From what I read about WAL (you have been referring to this:
>> http://www.postgresql.org/docs/current/static/wal-internals.html
>> pg_xlog, right?) it allows us to know what happened, but does it
>> warranty known secure state? I mean, I do not think it would help with
>> this:
>>
>> "In general, security mechanisms should be designed so that a failure
>> will follow the same execution path as disallowing the operation. For
>> example, application security methods, such as isAuthorized(),
>> isAuthenticated(), and validate(), should all return false if there is
>> an exception during processing. If security controls can throw
>> exceptions, they must be very clear about exactly what that condition
>> means. "
>>
>
> You are correct, that isn't the pg_xlog but yes, PostgreSQL will throw an
> exception in those types of cases.
>
> Sincerely,
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>

Re: [GENERAL] Failing to known state

[oleg yusim]

Hi Adrian,

I meant a scenario, when user is trying to connect to database (doesn't
matter what interface) and database fails at this moment. If all
authentication/authorization/validation functions are written to return
false in case of abnormal termination, we are fine. If not, we can
potentially encounter the situation when database fails into state where
user is given greater privileges than he/she should or even authenticated,
when he/she shouldn't.

Thanks,

Oleg

On Tue, Jan 5, 2016 at 5:34 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 01/05/2016 03:21 PM, oleg yusim wrote:
>
>> Thanks JD.
>>
>>  From what I read about WAL (you have been referring to this:
>> http://www.postgresql.org/docs/current/static/wal-internals.html
>> pg_xlog, right?) it allows us to know what happened, but does it
>> warranty known secure state? I mean, I do not think it would help with
>> this:
>>
>> "In general, security mechanisms should be designed so that a failure
>> will follow the same execution path as disallowing the operation. For
>> example, application security methods, such as isAuthorized(),
>> isAuthenticated(), and validate(), should all return false if there is
>> an exception during processing. If security controls can throw
>> exceptions, they must be very clear about exactly what that condition
>> means. "
>>
>
> Not sure what you are talking about above. the application as in the
> client application connecting to the database or the database application
> itself?
>
>
>> Right?
>>
>> Thanks,
>>
>> Oleg
>>
>>
>> On Tue, Jan 5, 2016 at 5:14 PM, Joshua D. Drake <j...@commandprompt.com
>> <mailto:j...@commandprompt.com>> wrote:
>>
>> On 01/05/2016 03:09 PM, oleg yusim wrote:
>>
>>
>>
>> The question here, what is PostreSQL 9.4.5 (hosted on Linux box)
>> behavior? Does it fail to known/secure state in these 3 cases? I
>> tried
>> to find the description of the way PostgreSQL fails in this
>> regard, but
>> didn't find much.
>>
>>
>> Based on what you pasted, PostgreSQL does fail to a known state.
>> That is the whole point of the xlog.
>>
>> Sincerely,
>>
>> JD
>>
>>
>> Thanks,
>>
>> Oleg
>>
>>
>>
>> --
>> Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
>> 
>> PostgreSQL Centered full stack support, consulting and development.
>> Announcing "I'm offended" is basically telling the world you can't
>> control your own emotions, so everyone else should do it for you.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

[GENERAL] Failing to known state

[oleg yusim]

Greetings,

One more security requirement I'm battling with:



The DBMS must fail to a secure state if system initialization fails,
shutdown fails, or aborts fail.

Failure to a known state can address safety or security in accordance with
the mission/business needs of the organization.

Failure to a known secure state helps prevent a loss of confidentiality,
integrity, or availability in the event of a failure of the information
system or a component of the system.

Failure to a known safe state helps prevent systems from failing to a state
that may cause loss of data or unauthorized access to system resources.
Systems that fail suddenly and with no incorporated failure state planning
may leave the hosting system available but with a reduced security
protection capability. Preserving information system state data also
facilitates system restart and return to the operational mode of the
organization with less disruption of mission/business processes.

Databases must fail to a known consistent state. Transactions must be
successfully completed or rolled back.

In general, security mechanisms should be designed so that a failure will
follow the same execution path as disallowing the operation. For example,
application security methods, such as isAuthorized(), isAuthenticated(),
and validate(), should all return false if there is an exception during
processing. If security controls can throw exceptions, they must be very
clear about exactly what that condition means.

Abort refers to stopping a program or function before it has finished
naturally. The term abort refers to both requested and unexpected
terminations.



The question here, what is PostreSQL 9.4.5 (hosted on Linux box) behavior?
Does it fail to known/secure state in these 3 cases? I tried to find the
description of the way PostgreSQL fails in this regard, but didn't find
much.

Thanks,

Oleg

Re: [GENERAL] Failing to known state

[oleg yusim]

Thanks JD.

>From what I read about WAL (you have been referring to this:
http://www.postgresql.org/docs/current/static/wal-internals.html pg_xlog,
right?) it allows us to know what happened, but does it warranty known
secure state? I mean, I do not think it would help with this:

"In general, security mechanisms should be designed so that a failure will
follow the same execution path as disallowing the operation. For example,
application security methods, such as isAuthorized(), isAuthenticated(),
and validate(), should all return false if there is an exception during
processing. If security controls can throw exceptions, they must be very
clear about exactly what that condition means. "

Right?

Thanks,

Oleg



On Tue, Jan 5, 2016 at 5:14 PM, Joshua D. Drake <j...@commandprompt.com>
wrote:

> On 01/05/2016 03:09 PM, oleg yusim wrote:
>
>
>>
>> The question here, what is PostreSQL 9.4.5 (hosted on Linux box)
>> behavior? Does it fail to known/secure state in these 3 cases? I tried
>> to find the description of the way PostgreSQL fails in this regard, but
>> didn't find much.
>>
>>
> Based on what you pasted, PostgreSQL does fail to a known state. That is
> the whole point of the xlog.
>
> Sincerely,
>
> JD
>
>
> Thanks,
>>
>> Oleg
>>
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>

Re: [GENERAL] Shared system resources

[oleg yusim]

HI George,

Thanks, this information clears the situation. Now, question to you and
David.

May we run into situation, when attacker dumps memory and analyses it for
valuable content, instead of reserving it for own process, where it would
be zeroed? My understanding, it is a possibility. Does kernel have any
safeguard against it?

Thanks,

Oleg

On Wed, Dec 23, 2015 at 2:13 AM, George Neuner  wrote:

> On Tue, 22 Dec 2015 23:21:27 +, David Wilson 
> wrote:
>
> >On Linux the memory pages of an exiting process aren't sanitized at
> >exit, however it is impossible(?) for userspace to reallocate them
> >without the kernel first zeroing their contents.
>
> Not impossible, but it requires a non-standard kernel.
>
> Since 2.6.33, mmap() accepts the flag MAP_UNINITIALIZED which allows
> pages to be mapped without being cleared.  The flag has no effect
> unless the kernel was built with CONFIG_MMAP_ALLOW_UNINITIALIZED.
>
>
> No mainstream distro enables this.  AFAIK, there is NO distro at all
> that enables it ... it's too big a security risk for a general purpose
> system.  It's intended to support embedded systems where the set of
> programs is known.
>
> George
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Shared system resources

[oleg yusim]

Jim,

Help me out with this statement:

"There's tons of raw data stored in the shared memory segment, and some of
that can be copied to process local memory at any time. If they OS doesn't
secure that adequately there's certainly nothing that Postgres or any other
database can do about it."

To my knowledge, many databases are using what called TDE to encrypt data
at rest and protect data from being accessed by attacker on host this way.
Here is the reference to quick guide on it:
https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/

Now, when you are saying " tons of raw data stored in the shared memory
segment, and some of that can be copied to process local memory at any time"
what kind of memory you are referring too? Is it that files, which
generally end up being protected with TDE, or is it a buffer memory, which
get's used by database processes, but doesn't belong to database permanent
storage?

Can you give me more details here, so I would understand the actual mapping
and scale of the issue?

Thanks,

Oleg

On Wed, Dec 23, 2015 at 9:55 AM, Jim Nasby <jim.na...@bluetreble.com> wrote:

> On 12/23/15 7:55 AM, oleg yusim wrote:
>
>> Sure David. For simplicity of modeling here, let's assume raw database
>> data was encrypted and the only possibility for attacker to get
>> something from raw data is to go and dig into sessions leftovers. Now,
>> with that has been said, do you happen to know what information actually
>> gets stored during the session into memory, reserved by session process?
>> I'm trying to determine, basically, does it even worth a talk - maybe
>> there is nothing at all valuable.
>>
>
> There's tons of raw data stored in the shared memory segment, and some of
> that can be copied to process local memory at any time. If they OS doesn't
> secure that adequately there's certainly nothing that Postgres or any other
> database can do about it.
>
> As David said, by the time you're concerned about someone getting access
> to raw memory it's already way too late.
>
> As for memory pages being zero'd after they are returned to the OS, that's
> entirely up to the OS. The only thing you could do on the Postgres side is
> to compile with memory debugging enabled, which will over-write any memory
> that's freed with a magic value. That's done to help hunt down memory
> access bugs, but would have the obvious side effect of obliterating any
> data that was in the page.
>
> Uh, only thing is, I don't know if this is done if we're going to be
> returning the memory to the OS.
>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Re: [GENERAL] Shared system resources

[oleg yusim]

Sure David. For simplicity of modeling here, let's assume raw database data
was encrypted and the only possibility for attacker to get something from
raw data is to go and dig into sessions leftovers. Now, with that has been
said, do you happen to know what information actually gets stored during
the session into memory, reserved by session process? I'm trying to
determine, basically, does it even worth a talk - maybe there is nothing at
all valuable.

Thanks,

Oleg

On Wed, Dec 23, 2015 at 7:41 AM, David Wilson <dw...@hmmz.org> wrote:

> On Wed, Dec 23, 2015 at 07:07:31AM -0600, oleg yusim wrote:
>
> > May we run into situation, when attacker dumps memory and analyses it
> > for valuable content, instead of reserving it for own process, where
> > it would be zeroed? My understanding, it is a possibility. Does kernel
> > have any safeguard against it?
>
> Sure it might be possible, but they would not have much useful
> information about which old processes the pages belonged to, and
> besides, they could most likely simply dump memory of a connected client
> in this case, or indeed just examine the filesystem or cache to get at
> the raw PG database files.
>
> Once someone has this level of access to the system it's not really
> useful to model threats much further.
>
> One minor correction from my first mail: MAP_UNINITIALIZED is indeed
> accessible to non-root, but as George mentions only when a non-default
> kernel parameter has been enabled.
>
>
> David
>

Re: [GENERAL] Shared system resources

[oleg yusim]

John,

To my knowledge, TDE is employed not only by Microsoft, but by Oracle too.
I recall it also has a mechanism of protecting keys. Here are references:

https://msdn.microsoft.com/en-us/library/bb934049.aspx
http://www.oracle.com/technetwork/database/options/advanced-security/index-099011.html

Thank you very much for that piece:

"In PostgreSQL 'shared memory' has a quite specific meaning, its referring
to the pool of buffer memory (ram) shared by all postgres server
processes.   this is primarily used as the buffer cache. In a properly
secured operating system, ONLY the postgres server processes have access to
this shared memory segment"

It helped me to understand terminology used by other reponders better.

Thanks,

Oleg

On Wed, Dec 23, 2015 at 10:48 AM, John R Pierce <pie...@hogranch.com> wrote:

> On 12/23/2015 8:16 AM, oleg yusim wrote:
>
>>
>> To my knowledge, many databases are using what called TDE to encrypt data
>> at rest and protect data from being accessed by attacker on host this way.
>> Here is the reference to quick guide on it:
>> https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/
>>
>
> that article is talking about a specific feature of Microsoft SQL Server
> Enterprise Edition, which upon a quick skim sounds to me to be smoke and
> mirrors 'security-by-checklist' protection.   If the encryption keys are
> stored on the system, then anyone with access to the raw data can decrypt
> it, no matter how much smoke and mirrors you wave around to obfuscate this
> fact.
>
> In PostgreSQL 'shared memory' has a quite specific meaning, its referring
> to the pool of buffer memory (ram) shared by all postgres server
> processes.   this is primarily used as the buffer cache. In a properly
> secured operating system, ONLY the postgres server processes have access to
> this shared memory segment, but the details of OS level memory management
> are outide postgres's scope, since its portable and designed to be able to
> run on most any OS that provides basic memory management, multiple
> processes, and a reliable/robust file system, with tcp/ip socket support.
>
>
>
> --
> 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: [GENERAL] Shared system resources

[oleg yusim]

Thank you very much George, that is exactly the piece of information I was
missing.

Oleg

On Wed, Dec 23, 2015 at 10:55 AM, George Neuner <gneun...@comcast.net>
wrote:

> Hi Oleg,
>
> On Wed, 23 Dec 2015 07:07:31 -0600, oleg yusim <olegyu...@gmail.com>
> wrote:
>
> >May we run into situation, when attacker dumps memory and analyses it for
> >valuable content, instead of reserving it for own process, where it would
> >be zeroed? My understanding, it is a possibility. Does kernel have any
> >safeguard against it?
>
> With recent kernels, by default there is no way for a userspace
> process (even root) to dump memory.  Older kernels by default
> permitted a root process unrestricted access to /dev/mem and
> /dev/kmem, however in general that isn't needed and has long been
> disabled by the mahor distros.  [see CONFIG_STRICT_DEVMEM].  IIRC, the
> default setting was changed in 2011.
>
> With sufficient privileges, a debugger-like process can attach and
> examine the memory of a running - or just terminated - process, but it
> won't have access to discarded (unmapped) memory.
>
> The MAP_UNINITIALIZED trick, even if it works, is not a predictable
> attack vector.  There is no way to ask for any *particular* VMM page -
> mmap() just gives you a set of pages sufficient to cover the requested
> address range ... you don't know what process those pages previously
> belonged to.  Obviously there is a known algorithm for satisfying the
> page requests, but the set of free pages includes both code and data
> and depends on the history of system activity.  There's no guarantee
> to get anything useful.
>
> I'm not sure any of this really answers your question.
> George
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

Thanks Michael, you are right, that is a very good alternative solution.

Oleg

On Tue, Dec 22, 2015 at 6:27 AM, Michael Paquier <michael.paqu...@gmail.com>
wrote:

> On Tue, Dec 22, 2015 at 1:42 AM, Stephen Frost <sfr...@snowman.net> wrote:
> > Oleg,
> >
> > * oleg yusim (olegyu...@gmail.com) wrote:
> >> tcp_keepalives_idle = 900
> >> tcp_keepalives_interval=0
> >> tcp_keepalives_count=0
> >>
> >> Doesn't terminate connection to database in 15 minutes of inactivity of
> >> psql prompt. So, it looks like that would work only for case if network
> >> connection is broken and session left hanging. For psql prompt case
> looks
> >> like pg_terminate_backend() would be the only solution.
> >
> > Those settings aren't for controlling idle timeout of a connection.
> >
> > pg_terminate_backend() will work and could be run out of a cronjob.
>
> Or a background worker if you are using PG >= 9.3:
> https://github.com/michaelpq/pg_plugins/tree/master/kill_idle
> This has the advantage to not have the cronjob error out should the
> server be stopped. That's less error handling to take care of at
> frontend level.
> --
> Michael
>

[GENERAL] Shared system resources

[oleg yusim]

Greetings,

I'm looking at the following security control right now:

*The DBMS must prevent unauthorized and unintended information transfer via
shared system resources.*

The purpose of this control is to prevent information, including encrypted
representations of information, produced by the actions of a prior
user/role (or the actions of a process acting on behalf of a prior
user/role) from being available to any current user/role (or current
process) that obtains access to a shared system resource (e.g., registers,
main memory, secondary storage) after the resource has been released back
to the information system. Control of information in shared resources is
also referred to as object reuse.

>From previous discussions, I understood that session in PostgreSQL is
closely associated with process, and it is essentially new process for
every user connection. In regards to that, my question would be:

When user disconnects, process is terminated and all resources are
released, does memory, session/process stored information at gets
"sanitized" or just released as is?

Thanks,

Oleg

Re: [GENERAL] Shared system resources

[oleg yusim]

Jim,

Yes, you are right. Generally the security control here is encryption of
data at rest (TDE), but PostgreSQL doesn't support it, to my knowledge. I
know about that vulnerability, but here I posed the question on different
one. I agree it is smaller one, compare to the absence of TDE, but I would
like to find out if this gates are opened too or not.

Thanks,

Oleg

On Tue, Dec 22, 2015 at 8:48 PM, Jim Nasby <jim.na...@bluetreble.com> wrote:

> On 12/22/15 6:03 PM, oleg yusim wrote:
>
>> Absolutely. But we are not talking about that type of data leakage here.
>> We are talking about potential situation when user, who doesn't have
>> access to database, but has (or gained) access to the Linux box DB is
>> installed one and gets his hands on data, database processes stored in
>> memory (memory would be a common resource here).
>>
>
> Of far larger concern at that point is unauthorized access to the database
> files.
>
> Basically, if someone gains access to the OS user that Postgres is running
> as, or to root, it's game-over.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Re: [GENERAL] Shared system resources

[oleg yusim]

John,

Absolutely. But we are not talking about that type of data leakage here. We
are talking about potential situation when user, who doesn't have access to
database, but has (or gained) access to the Linux box DB is installed one
and gets his hands on data, database processes stored in memory (memory
would be a common resource here).

Thanks,

Oleg

On Tue, Dec 22, 2015 at 5:28 PM, John R Pierce <pie...@hogranch.com> wrote:

> On 12/22/2015 2:52 PM, oleg yusim wrote:
>
>
> *The DBMS must prevent unauthorized and unintended information transfer
> via shared system resources.*
>
>
>
> you realize the database *itself* is a shared system resource and of
> and by itself has no idea about unauthorized/unintended information
> transfer, eg, any user with the proper credentials to connect to the
> database can query any tables those credentials are allowed to?
>
> --
> john r pierce, recycling bits in santa cruz
>
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

Melvin,

I promised to let you know results of my experiment, so here is goes:


tcp_keepalives_idle = 900
tcp_keepalives_interval=0
tcp_keepalives_count=0

Doesn't terminate connection to database in 15 minutes of inactivity of
psql prompt. So, it looks like that would work only for case if network
connection is broken and session left hanging. For psql prompt case looks
like pg_terminate_backend() would be the only solution.

Thanks,

Oleg



On Sun, Dec 20, 2015 at 11:33 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:

> Actually, I'm not an expert on the tcp_keepalives, but I  believe the 
> tcp_keepalives_count
> should be 1, otherwise it will take 45 minutes minutes to timeout. Then
> again, I could be wrong.
>
> On Sun, Dec 20, 2015 at 12:28 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
>> oleg yusim <olegyu...@gmail.com> writes:
>> > Got it, thanks... Now, is it any protection in place currently against
>> > replacing Session ID (my understanding, it is kept in memory, belonging
>> to
>> > the session process) or against guessing Session ID (i.e. is Session ID
>> > generated using FIPS 140-2 compliant algorithms, or anything of that
>> sort)?
>>
>> I don't think Postgres even has any concept that matches what you seem
>> to think a Session ID is.
>>
>> If you're looking for communication security/integrity checking, that's
>> something we leave to other software such as SSL.
>>
>> regards, tom lane
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

Melvin, Stephen,

Thanks for your responses, guys. I think we can finally put this topic to
the bed with that - I have satisfactory answer. For those who would be
interested and would dig into this topic later on, here is fairly detailed
explanation on how to use pg_terminate_backend in this case, coupled with
usage of pg_stat_activity and cron (it also has code too):
http://stackoverflow.com/questions/12391174/how-to-close-idle-connections-in-postgresql-automatically

Thanks everybody,

Oleg

On Mon, Dec 21, 2015 at 10:51 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:

> Pursuant to Stehen's suggestion, I've attached a scripts that you can
> execeute from a cron. I wrote it when I was working for a previous company
> that used to have users that opened connections
> and transaction that did nothing for a long time.
> Just adjust the max_time for your liking. You can also add OR
> current_query = '' to kill stagnant connections.
>
> On Mon, Dec 21, 2015 at 11:42 AM, Stephen Frost <sfr...@snowman.net>
> wrote:
>
>> Oleg,
>>
>> * oleg yusim (olegyu...@gmail.com) wrote:
>> > tcp_keepalives_idle = 900
>> > tcp_keepalives_interval=0
>> > tcp_keepalives_count=0
>> >
>> > Doesn't terminate connection to database in 15 minutes of inactivity of
>> > psql prompt. So, it looks like that would work only for case if network
>> > connection is broken and session left hanging. For psql prompt case
>> looks
>> > like pg_terminate_backend() would be the only solution.
>>
>> Those settings aren't for controlling idle timeout of a connection.
>>
>> pg_terminate_backend() will work and could be run out of a cronjob.
>>
>> Thanks!
>>
>> Stephen
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

[GENERAL] Session Identifiers

[oleg yusim]

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

>From the security standpoint we have to assure that database invalidates
session identifiers upon user logout or other session termination (timeout
counts too).

Does PostgreSQL perform this type of actions? If so, where are those
Session IDs are stored, so I can verify it?

Thanks,

Oleg

Re: [GENERAL] Session Identifiers

[oleg yusim]

Hi Melvin,

Thank you very much, that logging option really helps (I need to give
instructions, people, who are not very code literate should be capable of
executing). And, point taken about exact version and enviornment -
PostgreSQL 9.4.5, Linux box.

Thanks,

Oleg

On Sun, Dec 20, 2015 at 10:19 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:

> PostgreSQL does not "store" the session_id per se in any system
> catalogs/tables, however, you can configure the log_line_prefix in
> postgresql.conf to record it for each connection. It will then be stored in
> the postgresql log file.
> Please not that in the future, it is always helpful to provide the exact
> version of PostgreSQL and the O/S you are working with.
>
> On Sun, Dec 20, 2015 at 11:08 AM, Pavel Stehule <pavel.steh...@gmail.com>
> wrote:
>
>> Hi
>>
>> 2015-12-20 16:16 GMT+01:00 oleg yusim <olegyu...@gmail.com>:
>>
>>> 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 questions:
>>>
>>> From the security standpoint we have to assure that database invalidates
>>> session identifiers upon user logout or other session termination (timeout
>>> counts too).
>>>
>>> Does PostgreSQL perform this type of actions? If so, where are those
>>> Session IDs are stored, so I can verify it?
>>>
>>
>> Postgres is based on processes - for any session is created new process
>> when user is logged and this process is destroyed when user does logout.
>> Almost all data are in process memory only, but shared data related to
>> sessions are stored in shared memory - in array of PGPROC structures.
>> Postgres invalidates these data immediately when process is destroyed.
>> Search PGPROC in our code. Look to postmaster.c, where these operations are
>> described.
>>
>> What I know, there are not any other session data - so when process is
>> destroyed, then all is destroyed by o.s.
>>
>> Can be totally different if you use some connection pooler like pgpool or
>> pgbouncer - these applications can reuse Postgres server sessions for more
>> user sessions.
>>
>> Regards
>>
>> Pavel
>>
>>
>>>
>>> Thanks,
>>>
>>> Oleg
>>>
>>
>>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

Got it, thanks... Now, is it any protection in place currently against
replacing Session ID (my understanding, it is kept in memory, belonging to
the session process) or against guessing Session ID (i.e. is Session ID
generated using FIPS 140-2 compliant algorithms, or anything of that sort)?

Oleg

On Sun, Dec 20, 2015 at 11:02 AM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-12-20 17:52 GMT+01:00 oleg yusim <olegyu...@gmail.com>:
>
>> Hi Pavel,
>>
>> Thanks, for your response, it helps. Now, from my observations
>> (PostgreSQL 9.4.5, installed on Linux box), if I enter psql prompt at my
>> ssh to the box session and leave it open like that, it doesn't time out. Is
>> it really a case? Session to PostgreSQL DB doesn't terminate on timeout (or
>> rather doesn't have one), or I just happened to miss configuration option?
>>
>>
> any unbound process started as custom session means critical error - and
> there are not any related known bug. Postgres hasn't any build option for
> terminating session. If you need it - the pgbouncer has one or you can
> terminate session via pg_terminate_backend and cron. Maybe somebody will
> write background worker for this purpose. Internally, the system processes
> and sessions has pretty strong relation in Postgres. - there cannot be
> process without session and session without process.
>
> Pavel
>
>
>> Thanks,
>>
>> Oleg
>>
>> On Sun, Dec 20, 2015 at 10:08 AM, Pavel Stehule <pavel.steh...@gmail.com>
>> wrote:
>>
>>> Hi
>>>
>>> 2015-12-20 16:16 GMT+01:00 oleg yusim <olegyu...@gmail.com>:
>>>
>>>> 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 questions:
>>>>
>>>> From the security standpoint we have to assure that database
>>>> invalidates session identifiers upon user logout or other session
>>>> termination (timeout counts too).
>>>>
>>>> Does PostgreSQL perform this type of actions? If so, where are those
>>>> Session IDs are stored, so I can verify it?
>>>>
>>>
>>> Postgres is based on processes - for any session is created new process
>>> when user is logged and this process is destroyed when user does logout.
>>> Almost all data are in process memory only, but shared data related to
>>> sessions are stored in shared memory - in array of PGPROC structures.
>>> Postgres invalidates these data immediately when process is destroyed.
>>> Search PGPROC in our code. Look to postmaster.c, where these operations are
>>> described.
>>>
>>> What I know, there are not any other session data - so when process is
>>> destroyed, then all is destroyed by o.s.
>>>
>>> Can be totally different if you use some connection pooler like pgpool
>>> or pgbouncer - these applications can reuse Postgres server sessions for
>>> more user sessions.
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>>
>>>> Thanks,
>>>>
>>>> Oleg
>>>>
>>>
>>>
>>
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

Thanks you very much Melvin, once again, very useful. So, let me see if I
got it right, following configuration should cause my database connection
to terminate in 15 minutes, right?

tcp_keepalives_idle = 900
tcp_keepalives_interval=1
tcp_keepalives_count=3

Oleg

On Sun, Dec 20, 2015 at 11:14 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:

> Regarding timeouts, PostgreSQL will use the system tcp_keepalives_* parms
> by default, but you can also configure it separately in postgresql.conf.
> http://www.postgresql.org/docs/9.4/static/runtime-config-connection.html
>
> I suggest you review all available parameters in the postgresql.conf, as
> it will probably answer some additional questions for you.
>
> On Sun, Dec 20, 2015 at 12:02 PM, Pavel Stehule <pavel.steh...@gmail.com>
> wrote:
>
>>
>>
>> 2015-12-20 17:52 GMT+01:00 oleg yusim <olegyu...@gmail.com>:
>>
>>> Hi Pavel,
>>>
>>> Thanks, for your response, it helps. Now, from my observations
>>> (PostgreSQL 9.4.5, installed on Linux box), if I enter psql prompt at my
>>> ssh to the box session and leave it open like that, it doesn't time out. Is
>>> it really a case? Session to PostgreSQL DB doesn't terminate on timeout (or
>>> rather doesn't have one), or I just happened to miss configuration option?
>>>
>>>
>> any unbound process started as custom session means critical error - and
>> there are not any related known bug. Postgres hasn't any build option for
>> terminating session. If you need it - the pgbouncer has one or you can
>> terminate session via pg_terminate_backend and cron. Maybe somebody will
>> write background worker for this purpose. Internally, the system processes
>> and sessions has pretty strong relation in Postgres. - there cannot be
>> process without session and session without process.
>>
>> Pavel
>>
>>
>>> Thanks,
>>>
>>> Oleg
>>>
>>> On Sun, Dec 20, 2015 at 10:08 AM, Pavel Stehule <pavel.steh...@gmail.com
>>> > wrote:
>>>
>>>> Hi
>>>>
>>>> 2015-12-20 16:16 GMT+01:00 oleg yusim <olegyu...@gmail.com>:
>>>>
>>>>> 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 questions:
>>>>>
>>>>> From the security standpoint we have to assure that database
>>>>> invalidates session identifiers upon user logout or other session
>>>>> termination (timeout counts too).
>>>>>
>>>>> Does PostgreSQL perform this type of actions? If so, where are those
>>>>> Session IDs are stored, so I can verify it?
>>>>>
>>>>
>>>> Postgres is based on processes - for any session is created new process
>>>> when user is logged and this process is destroyed when user does logout.
>>>> Almost all data are in process memory only, but shared data related to
>>>> sessions are stored in shared memory - in array of PGPROC structures.
>>>> Postgres invalidates these data immediately when process is destroyed.
>>>> Search PGPROC in our code. Look to postmaster.c, where these operations are
>>>> described.
>>>>
>>>> What I know, there are not any other session data - so when process is
>>>> destroyed, then all is destroyed by o.s.
>>>>
>>>> Can be totally different if you use some connection pooler like pgpool
>>>> or pgbouncer - these applications can reuse Postgres server sessions for
>>>> more user sessions.
>>>>
>>>> Regards
>>>>
>>>> Pavel
>>>>
>>>>
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Oleg
>>>>>
>>>>
>>>>
>>>
>>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

Tom,

I understand the idea that for external communication you rely on SSL.
However, how about me opening psql prompt into the database directly from
my Linux box, my db is installed at? I thought, it would be considered
local connection and would not go through the SSL channels. If that is the
case, here we would be dealing with Session IDs belonging to DB itself, not
OpenSSL.

Please, correct me if I'm wrong.

Thanks,

Oleg

On Sun, Dec 20, 2015 at 11:28 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> oleg yusim <olegyu...@gmail.com> writes:
> > Got it, thanks... Now, is it any protection in place currently against
> > replacing Session ID (my understanding, it is kept in memory, belonging
> to
> > the session process) or against guessing Session ID (i.e. is Session ID
> > generated using FIPS 140-2 compliant algorithms, or anything of that
> sort)?
>
> I don't think Postgres even has any concept that matches what you seem
> to think a Session ID is.
>
> If you're looking for communication security/integrity checking, that's
> something we leave to other software such as SSL.
>
> regards, tom lane
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

Thanks Melvin,

Let me experiment with it for a bit. I will let you know results.

Oleg

On Sun, Dec 20, 2015 at 11:33 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:

> Actually, I'm not an expert on the tcp_keepalives, but I  believe the 
> tcp_keepalives_count
> should be 1, otherwise it will take 45 minutes minutes to timeout. Then
> again, I could be wrong.
>
> On Sun, Dec 20, 2015 at 12:28 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
>> oleg yusim <olegyu...@gmail.com> writes:
>> > Got it, thanks... Now, is it any protection in place currently against
>> > replacing Session ID (my understanding, it is kept in memory, belonging
>> to
>> > the session process) or against guessing Session ID (i.e. is Session ID
>> > generated using FIPS 140-2 compliant algorithms, or anything of that
>> sort)?
>>
>> I don't think Postgres even has any concept that matches what you seem
>> to think a Session ID is.
>>
>> If you're looking for communication security/integrity checking, that's
>> something we leave to other software such as SSL.
>>
>> regards, tom lane
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

So Pavel, are are saying there is no such thing as Session ID in PostgreSQL
DB at all? Everything is tight to the process, session is accociated with,
so in essence pid is session id?

Oleg

On Sun, Dec 20, 2015 at 11:40 AM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-12-20 18:37 GMT+01:00 oleg yusim <olegyu...@gmail.com>:
>
>> Tom,
>>
>> I understand the idea that for external communication you rely on SSL.
>> However, how about me opening psql prompt into the database directly from
>> my Linux box, my db is installed at? I thought, it would be considered
>> local connection and would not go through the SSL channels. If that is the
>> case, here we would be dealing with Session IDs belonging to DB itself, not
>> OpenSSL.
>>
>
> all necessary data are stored local in process memory. No session ID is
> required.
>
> Pavel
>
>
>>
>> Please, correct me if I'm wrong.
>>
>> Thanks,
>>
>> Oleg
>>
>> On Sun, Dec 20, 2015 at 11:28 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>>
>>> oleg yusim <olegyu...@gmail.com> writes:
>>> > Got it, thanks... Now, is it any protection in place currently against
>>> > replacing Session ID (my understanding, it is kept in memory,
>>> belonging to
>>> > the session process) or against guessing Session ID (i.e. is Session ID
>>> > generated using FIPS 140-2 compliant algorithms, or anything of that
>>> sort)?
>>>
>>> I don't think Postgres even has any concept that matches what you seem
>>> to think a Session ID is.
>>>
>>> If you're looking for communication security/integrity checking, that's
>>> something we leave to other software such as SSL.
>>>
>>> regards, tom lane
>>>
>>
>>
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

Hi Pavel,

Thanks, for your response, it helps. Now, from my observations (PostgreSQL
9.4.5, installed on Linux box), if I enter psql prompt at my ssh to the box
session and leave it open like that, it doesn't time out. Is it really a
case? Session to PostgreSQL DB doesn't terminate on timeout (or rather
doesn't have one), or I just happened to miss configuration option?

Thanks,

Oleg

On Sun, Dec 20, 2015 at 10:08 AM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

> Hi
>
> 2015-12-20 16:16 GMT+01:00 oleg yusim <olegyu...@gmail.com>:
>
>> 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 questions:
>>
>> From the security standpoint we have to assure that database invalidates
>> session identifiers upon user logout or other session termination (timeout
>> counts too).
>>
>> Does PostgreSQL perform this type of actions? If so, where are those
>> Session IDs are stored, so I can verify it?
>>
>
> Postgres is based on processes - for any session is created new process
> when user is logged and this process is destroyed when user does logout.
> Almost all data are in process memory only, but shared data related to
> sessions are stored in shared memory - in array of PGPROC structures.
> Postgres invalidates these data immediately when process is destroyed.
> Search PGPROC in our code. Look to postmaster.c, where these operations are
> described.
>
> What I know, there are not any other session data - so when process is
> destroyed, then all is destroyed by o.s.
>
> Can be totally different if you use some connection pooler like pgpool or
> pgbouncer - these applications can reuse Postgres server sessions for more
> user sessions.
>
> Regards
>
> Pavel
>
>
>>
>> Thanks,
>>
>> Oleg
>>
>
>

Re: [GENERAL] Session Identifiers

[oleg yusim]

Hi Andrew,

Exactly! Vulnerability is the direct analogy of one with Unix shell. The
way we generally deal with Unix shell vulnerability, we configure the shell
to terminate on its own if timeout was exceeded. The question here is, can
we configure psql client to behave the same?

Thanks,

Oleg


On Sun, Dec 20, 2015 at 1:38 PM, Andrew Sullivan <a...@crankycanuck.ca>
wrote:

> On Sun, Dec 20, 2015 at 11:25:45AM -0600, oleg yusim wrote:
> > Thanks you very much Melvin, once again, very useful. So, let me see if I
> > got it right, following configuration should cause my database connection
> > to terminate in 15 minutes, right?
> >
> > tcp_keepalives_idle = 900
> > tcp_keepalives_interval=1
> > tcp_keepalives_count=3
>
> Only if your psql session ends.  Psql is a client program.  It keeps
> its connection to the database alive.
>
> In this sense, the vulnerability you're looking at is analagous to the
> case where someone logs into a UNIX shell and then leaves the shell
> open.  If the system can be compromised such that someone else can get
> control of that shell, you have a problem.  Otherwise, the session
> can't really be taken over.  So, your exposure is exactly as great as
> the exposure from UNIX process takeover.
>
> You can prove to yourself that the process doesn't linger by opening
> up a TCP connection (or for that matter a UNIX socket connection) and
> somehow making the containing program fail (e.g. open a psql
> connection and then sever your connection to the machine that had the
> shell that initiated the psql session, without properly closing the
> shell so that the session hangs around).  Eventually, the Postgres
> backend will try to talk to the session and discover it isn't there,
> and you'll get a termination logged (assuming you have loging turned
> up that high).
>
> A
>
> --
> Andrew Sullivan
> a...@crankycanuck.ca
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

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 <
akretsch...@spamfence.net> wrote:

> oleg yusim <olegyu...@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: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

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 <pie...@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: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

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 <gsiever...@comcast.net>
wrote:

> Scott Mead <sco...@openscg.com> writes:
>
> > On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim <olegyu...@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 <pie...@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.consult...@comcast.net
> p: 312.241.7800
>

Re: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

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.kla...@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.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> wrote:
>>
>> On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim <olegyu...@gmail.com
>> <mailto:olegyu...@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.kla...@aklaver.com
>

Re: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

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-91-DB-66 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.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@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.johns...@gmail.com <mailto:david.g.johns...@gmail.com>
>> <mailto:david.g.johns...@gmail.com
>> <mailto:david.g.johns...@gmail.com>>> wrote:
>>
>>  On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim
>> <olegyu...@gmail.com <mailto:olegyu...@gmail.com>
>>  <mailto:olegyu...@gmail.com
>> <mailto:olegyu...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

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.johns...@gmail.com> wrote:

> On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim <olegyu...@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: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

Hi David,

Can you, please, give me example?

Thanks,

Oleg

On Thu, Dec 10, 2015 at 2:25 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Dec 10, 2015 at 1:20 PM, oleg yusim <olegyu...@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: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

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.kla...@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-91-DB-66 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 100. 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.kla...@aklaver.com <mailto:adrian.kla...@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

Re: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

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 <t...@sss.pgh.pa.us> wrote:

> oleg yusim <olegyu...@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: [GENERAL] Loggingt psql meta-commands

[oleg yusim]

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

[GENERAL] Loggingt psql meta-commands

[oleg yusim]

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