agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedReplication and Field Level Encryption
2+ messages / 2 participants
[nested] [flat]
* Replication and Field Level Encryption
@ 2017-08-10 17:04 Jason Aleski <jason.aleski@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jason Aleski @ 2017-08-10 17:04 UTC (permalink / raw)
To: pgsql-sql
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-
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Replication and Field Level Encryption
@ 2017-08-10 22:11 Steve Midgley <science@misuse.org>
parent: Jason Aleski <jason.aleski@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Steve Midgley @ 2017-08-10 22:11 UTC (permalink / raw)
To: Jason Aleski <jason.aleski@gmail.com>; +Cc: pgsql-sql
On Aug 10, 2017 10:07 AM, "Jason Aleski" <jason.aleski@gmail.com> wrote:
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
Any thoughts or items I should also consider?
If I were building this I'd put a middle tier api layer between the sql
server and the various clients. I think you'll get more expressiveness in
the api design and therefore even dumber clients (a good goal). You should
also have an easier time building a test harness and all the tests you'll
want.
You'd abstract the encryption and similar stuff into the api, and it can
either be handled there are delegated down to postgres (and as you point
out you can change your strategy over time).
Getting the api design right is an important early goal for the project.
That and quality tests..
Common middle tier api options are ruby, python, node.js, and Java.
Steve
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-08-10 22:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-10 17:04 Replication and Field Level Encryption Jason Aleski <jason.aleski@gmail.com>
2017-08-10 22:11 ` Steve Midgley <science@misuse.org>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox