To: pgsql-sql@postgresql.org
From: Jason Aleski <jason.aleski@gmail.com>
Subject: Replication and Field Level Encryption
Message-ID: <482a34c7-d0dc-6b8b-0b92-cfef0ad6eadf@gmail.com>
Date: Thu, 10 Aug 2017 12:04:41 -0500
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.2.1
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------5FA35D7AA90405EFF5B438AF"
Content-Language: en-US
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------5FA35D7AA90405EFF5B438AF
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

I really need some assistance to see if I'm heading in the right 
direction and get a second opinion.  Also to see if there is anything I 
may need to reconsider.

I'm building a database design and application from the ground up. The 
database is going to be synced in three locations using BDR.The local 
office will use the instance closest to them.  Each location will also 
have an additional "BI/Reporting" instance with data being updated once 
a day.

I need to be able to do field level encryption, with both 2-way 
encryption and 1-way hashing (ie: passwords, PII, PHI data).  I believe 
I want to use PGCrypto and keep as much programming logic on the SQL 
server.  Right now, there will be a desktop, mobile and web application; 
plus additional web services to support the applications.  Even though 
the data will be encrypted and decrypted on the server, data transport 
security will rely upon SSL/TLS between the client and server.  I'd like 
to keep the client applications as "dumb" as possible so if we decide to 
change encryption methods, keys, etc.  We don't have to rollout entirely 
new applications just for key changes or if we have to re-hash/re-salt 
the database.

The things I keep going back and forth on are:

 1. Should the encryption and decryption be at the client or server?
     1. Advantage to the client
         1. Data is secured end-to-end
     2. Disadvantage
         1. Managing application changes during security key changes.
     3. Advantage to the server
         1. Consolidates programming logic and reduces the encryption
            process on the client
     4. Disadvantage to the server
         1. Data relies on communications SSL/TLS for encryption
 2. Is BDR ready for multi-master in the production world?  Or should I
    use a master/hot-standby at the main facility?  I'm mainly the two
    of the locations are in rural areas and want to make sure if their
    internet connection goes down, they can still operate?  Yes, I'm
    aware there are commercial packages out there.  We are weighing
    those, but need to show that if we did it without the use of
    commercial software/support/services, there is risk/reward.

Any thoughts or items I should also consider?

-JA-


--------------5FA35D7AA90405EFF5B438AF
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    I really need some assistance to see if I'm heading in the right
    direction and get a second opinion.  Also to see if there is
    anything I may need to reconsider.<br>
    <br>
    I'm building a database design and application from the ground up. 
    The database is going to be synced in three locations using BDR.<span
style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;"></span> 
    The local office will use the instance closest to them.  Each
    location will also have an additional "BI/Reporting" instance with
    data being updated once a day.<br>
    <br>
    I need to be able to do field level encryption, with both 2-way
    encryption and 1-way hashing (ie: passwords, PII, PHI data).  I
    believe I want to use PGCrypto and keep as much programming logic on
    the SQL server.  Right now, there will be a desktop, mobile and web
    application; plus additional web services to support the
    applications.  Even though the data will be encrypted and decrypted
    on the server, data transport security will rely upon SSL/TLS
    between the client and server.  I'd like to keep the client
    applications as "dumb" as possible so if we decide to change
    encryption methods, keys, etc.  We don't have to rollout entirely
    new applications just for key changes or if we have to
    re-hash/re-salt the database.<br>
    <br>
    The things I keep going back and forth on are:<br>
    <ol>
      <li>Should the encryption and decryption be at the client or
        server?  <br>
      </li>
      <ol>
        <li>Advantage to the client</li>
        <ol>
          <li>Data is secured end-to-end</li>
        </ol>
        <li>Disadvantage</li>
        <ol>
          <li>Managing application changes during security key changes.</li>
        </ol>
        <li>Advantage to the server</li>
        <ol>
          <li>Consolidates programming logic and reduces the encryption
            process on the client</li>
        </ol>
        <li>Disadvantage to the server</li>
        <ol>
          <li>Data relies on communications SSL/TLS for encryption</li>
        </ol>
      </ol>
      <li>Is BDR ready for multi-master in the production world?  Or
        should I use a master/hot-standby at the main facility?  I'm
        mainly the two of the locations are in rural areas and want to
        make sure if their internet connection goes down, they can still
        operate?  Yes, I'm aware there are commercial packages out
        there.  We are weighing those, but need to show that if we did
        it without the use of commercial software/support/services,
        there is risk/reward.</li>
    </ol>
    <p>Any thoughts or items I should also consider?</p>
    <p>-JA-<br>
    </p>
    <br>
    <br>
  </body>
</html>

--------------5FA35D7AA90405EFF5B438AF--