Re: Postgresql database encryption

PS. the following database servers do offer internal encryption on a
page/block oriented read/write (for encrypted data at rest security
requirements)

PremierSQL TDE
MariaDB 10.1.3+
*MySQL* 5.7.11+
Microsoft uses TDE
Oracle AdvSec uses TDE
DB2 v7.2 UDB
MangoDB uses AES-256
PostgreSQL does - but the key is public (dumb)
https://www.postgresql.org/message-id/CA%2BCSw_tb3bk5i7if6inZFc3yyf%2B9HEVNTy51QFBoeUk7UE_V%3Dw@mail.gmail.com

Just because you do not see the reason for it, does not make the reason a
bad idea.

On Fri, Apr 20, 2018 at 8:19 PM Ozz Nixon <ozznixon(at)gmail(dot)com> wrote:

> Well, actually since 2003, this has been a standard requirement from the
> Credit Card industry. And it does make sense in the field of "while at
> rest" the data still cannot be accessed.
>
> Requirement 1. No NPI data should be displayed without controls - e.g.
> reports, PDF, etc.
> Requirement 2. Same data, must be secured during transmission - fetching
> to client screen etc.
> Requirement 3. NPI data should not be logged nor stored on a physical
> device in non-encrypted mode.
>
> There are more steps to this, but, to chalk it off as another half-assed
> required is typical. Hashing is a useful one-way technique, however,
> trapping the hash made using a hash useless! When I worked for the credit
> bureaus we ran encrypted drive arrays, DB/2 encrypted, SSL/TLS encryption
> over P2P VPN connections, and masked output fields when the data would go
> to reports or screens to PCs outside our control.
>
> Anyone with Linux and use LUKS encryption on an LVM partition to achieve
> security where the database may not, or logs or something may exist where
> NPI might be see. Oh yeah, NPI (Non-Pubic Information, like your social,
> you bank account, you paycheck information, etc. things that should not
> exist outside of controls)...
>
> PS. You cannot simply take a drive from one machine to another, when doing
> proper RAID and LUKS encryption.
>
> Ozz
> 15 years experience with federal data security requirements.
>
> On Fri, Apr 20, 2018 at 7:55 PM Tim Cross <theophilusx(at)gmail(dot)com> wrote:
>
>>
>> Vikas Sharma <shavikas(at)gmail(dot)com> writes:
>>
>> > Hello Guys,
>> >
>> > Could someone throw light on the postgresql instance wide or database
>> wide
>> > encryption please? Is this possible in postgresql and been in use in
>> > production?.
>> >
>> > This is a requirement in our production implementation.
>> >
>>
>> This sounds like a lazy management requirement specified for 'security'
>> purposes by people with little understanding of either technology or
>> security. I suspect it comes form a conversation that went along the
>> lines of ....
>>
>> "There has been lots in the news about cyber threats"
>>
>> "Yes, we need our system to be secure"
>>
>> "I know, lets make one of the requirements that everything must be
>> encrypted, that will stop them"
>>
>> "Great idea, I'll add it as requirement 14".
>>
>> This is a very poor requirement because it is not adequately specified,
>> but more critically, because it is specifying a 'solution' rather than
>> articulating the requirement in a way which would allow those with the
>> necessary expertise to derive an appropriate solution - one which may or
>> may not involve encryption or hashing of data and which may or may not
>> be at the database level.
>>
>> What you really need to do is go back to your stakeholders and ask them
>> a lot of questions to extract what the real requirement is. Try to find
>> out what risk they are trying to mitigate with encryption. Once this is
>> understood, then look at what the technology can do and work out the
>> design/implementation from there.
>>
>> It is extremely unlikely you just want all the data in the database
>> encrypted. When you think about it, such an approach really doesn't make
>> sense. In basic terms, if the data is encrypted, the database engine
>> will need to be able to decrypt it in order to operate (consider how a
>> where clause needs to be able to interpret actions etc). If the db can
>> read the data, the keys must be in the database. If the keys are in the
>> database and your database is compromised, then your keys are
>> compromised. So provided you protect your database from compromise, you
>> achieve the same level of security as you do with full data encryption
>> EXCEPT for access to the underlying data files outside of the database
>> system. For this, you will tend to use some sort of file system
>> encryption, which is typically managed at the operating system
>> level. Again, for the operating system to be able to read the file
>> system, the OS must have access to the decryption keys, so if your OS is
>> compromised, then that level of protection is lost as well (well, that
>> is over simplified, but you get the idea). What this level of protection
>> does give you is data at rest protection - if someone is able to access
>> hour disks through some other means, they cannot read the data. This is
>> the same principal most people should be using with their
>> laptops. Protect the OS with a password and have the data on disk
>> encrypted. Provided nobody can login to your laptop, they cannot read
>> your data. Without this encryption, you can just take the disk out of
>> the laptop, mount it on another system and you have full access. With
>> disk encryption, you cannot do that. Same basic principal with the
>> server.
>>
>> At the database level, a more typical approach is to use one way hashing
>> for some sensitive data (i.e. passwords) and possibly column level
>> encryption on a specific column (much rarer) or just well structured
>> security policies and user roles that restrict who has access to various
>> tables/columns. To implement this successfully, you need details
>> regarding the domain, sensitivity of various data elements and the
>> threats you need to protect against. If you cannot get this information
>> because your stakeholders don't really know what their risks are and
>> have not done a proper assessment and what you are really dealing with
>> is bureaucracy which just as a dumb "data must be encrypted" policy,
>> just use full disk encryption and state that all data is encrypted on
>> disk" and your done.
>>
>> Tim
>>
>>
>> --
>> Tim Cross
>>
>>