Encryption of data on PostgreSQL

2»

Comments

  • kristiakkristiak Posts: 1,301 ✭✭✭
    Re: [Developers] Encryption of data on PostgreSQL
    Hi,

    I probably do not understand this discussion but our certificates works fine in TOMCAT!!

    Regards

    Krister
    Sent: 15 December 2011 16:46
    To: [email protected]
    Subject: Re: [Developers] Encryption of data on PostgreSQL

    Hi Shaun
    This is a bit off topic but a nice idea too. We accomplish this by opening ssl tunnels if necessary but having the feature supported directly would be nice.
    Regards,
    Christian
    Am 15.12.2011 15:25, schrieb Shaun Martin:
    Hi All,
    Postgres does support SSL encryption. I have submitted the feature request for OpenClinica to support a postgres SSL connection awhile back on this ticket. https://issuetracker.openclinica.com/view.php?id=5611
    Please feel free to add a note or use the new community support module to vote for it.
    More information about Postgres and SSL is here .
    http://www.postgresql.org/docs/devel/static/ssl-tcp.html
    Please be aware that enabling an SSL database connection will decrease performance. You always loose performance when you wrap it an SSL tunnel.
    Thanks,
    Shaun
    On 12/15/11 08:47 AM, "Krister Kristianson" wrote:
    Hi Tom,

    I my country we have a system based upon an ID card with a microchip that you insert in a card reader and key a six digit key to access e.g. your bank account, tax records, medical records any many other records that authorities keep about you as a person. You only use it to log in and protection of the data is handled by physically protection of the servers.
    I believe this method won’t influence performance.

    Regards

    Krister
    Sent: 15 December 2011 14:24
    To: [email protected]
    Subject: Re: [Developers] Encryption of data on PostgreSQL
    Hi Tom
    Am 15.12.2011 13:45, schrieb Tom Hickerson:
    Would application-level encryption on certain columns support the requirements listed in this thread?
    It depends on the concept. Storing the password in datainfo.properties (or something similar) won't be suitable. Let the client provide the key per request might be interesting.
    Regards,
    Christian
  • JanusJanus Posts: 260
    Hi Krister,
    It's probably a solution to a situation where database and web application server are installed on separate servers and connected via an internet (as in: untrusted) connection. Not that that would in any way be a desired solution performance wise.
    I agree, that this is off topic, so let's keep this thread to the topic: encrypting the (subject) data in the database. How could this be done in an way so even physical theft of the OpenClinica server cannot disclose the data? Still, I think certificates issued to the authenticated users of the OpenClinica installation (*) is the way to go. Now, even if a thief could access the users table of the OpenClinica database, he or she would not be able to decrypt the data: Just logging in does not automatically expose the subject data, something more (the certificate!) is required.
    I have absloutely no idea of how this should be implemented, but a certificate could contain some token identifying which installation it belongs to. This authenticates the user for that particular installation along with the username/password combination. Then, the certificate contains the encryption key for that particular installation, hence encryption of data sent to that OpenClinica installation and decryption of data to be shown to the user is facilitated. The encryption key is not stored on the server itself.
    Do you think this is a way to do it, or do you see any problems in this? How could this method work when extracting data from the OpenClinica installation?
    Best regards,
    Janus
    (*) Certificates could be issued to either the database engine, the OpenClinica installation or the trial: One database engine could host several OpenClinica installations' databases and each OpenClinica installation could host several trials.
    "Krister Kristianson"
    Sent by: [email protected]
    15-12-2011 17:35
    Please respond to
    [email protected]

    To

    cc

    Subject
    Re: [Developers] Encryption of data on PostgreSQL

    Hi,

    I probably do not understand this discussion but our certificates works fine in TOMCAT!!

    Regards

    Krister
    Sent: 15 December 2011 16:46
    To: [email protected]
    Subject: Re: [Developers] Encryption of data on PostgreSQL

    Hi Shaun
    This is a bit off topic but a nice idea too. We accomplish this by opening ssl tunnels if necessary but having the feature supported directly would be nice.
    Regards,
    Christian
    Am 15.12.2011 15:25, schrieb Shaun Martin:
    Hi All,
    Postgres does support SSL encryption. I have submitted the feature request for OpenClinica to support a postgres SSL connection awhile back on this ticket. https://issuetracker.openclinica.com/view.php?id=5611
    Please feel free to add a note or use the new community support module to vote for it.
    More information about Postgres and SSL is here .
    http://www.postgresql.org/docs/devel/static/ssl-tcp.html
    Please be aware that enabling an SSL database connection will decrease performance. You always loose performance when you wrap it an SSL tunnel.
    Thanks,
    Shaun
    On 12/15/11 08:47 AM, "Krister Kristianson" wrote:
    Hi Tom,
    I my country we have a system based upon an ID card with a microchip that you insert in a card reader and key a six digit key to access e.g. your bank account, tax records, medical records any many other records that authorities keep about you as a person. You only use it to log in and protection of the data is handled by physically protection of the servers.
    I believe this method won’t influence performance.
    Regards
    Krister
    Sent: 15 December 2011 14:24
    To: [email protected]
    Subject: Re: [Developers] Encryption of data on PostgreSQL
    Hi Tom
    Am 15.12.2011 13:45, schrieb Tom Hickerson:
    Would application-level encryption on certain columns support the requirements listed in this thread?
    It depends on the concept. Storing the password in datainfo.properties (or something similar) won't be suitable. Let the client provide the key per request might be interesting.
    Regards,
    Christian
  • On Friday, December 16, 2011 11:15:04 AM Janus Engstrøm wrote:
    Hi all,
    We are starting to talk about different layer of encryption here.
    a) encrpyption of the traffic between browser and webserver
    b) encryption between PG database and pg client (webapp)
    c) encryption of data in a database (unencrypted filesystem)
    d) encryption of filesystem
    For full security you might aim for a combination of the above. However
    consider what happens when you loose the key/certificate. Go tell the
    investigators that their data is safely stored but neither you nor them can
    access it any more. You will be in for a lot of fun.
    If you read up on the subject you will quickly find out that the human element
    is the biggest problem. Password on sticky notes on the computer monitor and
    so on.
    Data flow between database server and webbrowser can be secured fairly easy.
    Encrypting the data itself makes you depend on not ever ever losing the
    encryption key/password/token/device.
    Same for encrypted filesystems. Encrypted filesystems on Linux are not the big
    problem any more. One better has strategies to deal with failing harddrives
    and so on. Forget about ever breaking that encryption.
    So the unless you know what you are doing and have the ressources to support
    it you better aim for encrypted data transfer and physical security.
    German courts don'f force you to do everything humanly possible at any rate.
    They want you to show that you had a best of breed concept and stuck to it
    (physical access control to server rooms, changing password, strong password,
    ssl encryption and so on).
    Besides the most vulnerable phase in your project is when your IT department
    starts to sell old drives on ebay (no joke). Then you wish you had data
    encrypted on the drive.
    All this is as always personal opinion.
    Regards,
    Sebastian Hilbert
  • JanusJanus Posts: 260
    Hi Sebastian,
    Thank you for your reply on this thread.
    I see your point, the human factor is always the bigger issue! I have just looked into Danish legislation on the subject, and they are actually pretty clear on this area: The login facility of OpenClinica, combined with the physical environment towards securing the server and the logical security (firewall, authenticated access to the OS of the server, etc) are sufficient for securing the data. They advocate for using a pseudo-anonymisation by using ID-numbers instead of personal identification and then let the investigators keep af reference list between the ID-number and the personal identification number.
    This makes - at least for me - things easier, but this may of course not be the issue of OpenClinica users in other countries. Too, if the study is to be FDA compliant, the quote posted by Krister _may_ be interpreted as a requirement of encryption.
    Best regards,
    Janus Engstrøm
    Application Developer
    Copenhagen Trial Unit
    Rigshospitalet, afd. 33.44
    Tlf. 3545 7161
    Sebastian Hilbert
    Sent by: [email protected]
    16-12-2011 12:57
    Please respond to
    [email protected]

    To
    [email protected]
    cc

    Subject
    Re: [Developers] Encryption of data on PostgreSQL

    On Friday, December 16, 2011 11:15:04 AM Janus Engstrøm wrote:
    Hi all,
    We are starting to talk about different layer of encryption here.
    a) encrpyption of the traffic between browser and webserver
    b) encryption between PG database and pg client (webapp)
    c) encryption of data in a database (unencrypted filesystem)
    d) encryption of filesystem
    For full security you might aim for a combination of the above. However
    consider what happens when you loose the key/certificate. Go tell the
    investigators that their data is safely stored but neither you nor them can
    access it any more. You will be in for a lot of fun.
    If you read up on the subject you will quickly find out that the human element
    is the biggest problem. Password on sticky notes on the computer monitor and
    so on.
    Data flow between database server and webbrowser can be secured fairly easy.
    Encrypting the data itself makes you depend on not ever ever losing the
    encryption key/password/token/device.
    Same for encrypted filesystems. Encrypted filesystems on Linux are not the big
    problem any more. One better has strategies to deal with failing harddrives
    and so on. Forget about ever breaking that encryption.
    So the unless you know what you are doing and have the ressources to support
    it you better aim for encrypted data transfer and physical security.
    German courts don'f force you to do everything humanly possible at any rate.
    They want you to show that you had a best of breed concept and stuck to it
    (physical access control to server rooms, changing password, strong password,
    ssl encryption and so on).
    Besides the most vulnerable phase in your project is when your IT department
    starts to sell old drives on ebay (no joke). Then you wish you had data
    encrypted on the drive.
    All this is as always personal opinion.
    Regards,
    Sebastian Hilbert
  • On Friday, December 16, 2011 01:53:16 PM Janus Engstrøm wrote:
    Hi,

    > > This makes - at least for me - things easier, but this may of course not
    > > be the issue of OpenClinica users in other countries. Too, if the study is
    > > to be FDA compliant, the quote posted by Krister _may_ be interpreted as a
    > > requirement of encryption.
    > >
    How do you encrypt the data on paper CRFs before you send them via snail mail
    to the US. Beats me and I would like to see that requirement defended in
    court.
    Sebastian
  • JanusJanus Posts: 260
    Well, you can't :) I guess the requirements apply to the electronic equivalence of a CRF simply because they are possible in that context. Besides that, paper CRFs are to be kept behind closed doors and hence away from unauthorised personnel. Transfer of paper CRFs should probably be either transferred as electronic copies (scanned copies securely transmitted by use of asynchonous keys or perhaps by fax). Nevertheless, I do see your point!
    Best regards,
    Janus
    Sebastian Hilbert
    Sent by: [email protected]
    16-12-2011 14:03
    Please respond to
    [email protected]

    To
    [email protected]
    cc

    Subject
    Re: [Developers] Encryption of data on PostgreSQL

    On Friday, December 16, 2011 01:53:16 PM Janus Engstrøm wrote:
    Hi,
    > This makes - at least for me - things easier, but this may of course not
    > be the issue of OpenClinica users in other countries. Too, if the study is
    > to be FDA compliant, the quote posted by Krister _may_ be interpreted as a
    > requirement of encryption.
    >
    How do you encrypt the data on paper CRFs before you send them via snail mail
    to the US. Beats me and I would like to see that requirement defended in
    court.
    Sebastian
  • kristiakkristiak Posts: 1,301 ✭✭✭
    Well yes, but the safest bet is definitely an encrypted link to a server in
    an absolutely safe vault, e.g. at one of the commercial hosting sites and
    they guarantee the uptime as well. But not even Fort Nox is completely safe
    when we have crooks like WIKI-leaks. But I will try TrueCrypt (OpenSource)
    to see if it is possible to install OC under this shield. I have tried for
    other type of data and it works fine.
    Regards
    Krister
    -----Original Message-----
    [mailto:[email protected]] On Behalf Of Sebastian Hilbert
    Sent: 16 December 2011 12:51
    To: [email protected]
    Subject: Re: [Developers] Encryption of data on PostgreSQL
    On Friday, December 16, 2011 11:15:04 AM Janus Engstrøm wrote:
    Hi all,
    We are starting to talk about different layer of encryption here.
    a) encrpyption of the traffic between browser and webserver
    b) encryption between PG database and pg client (webapp)
    c) encryption of data in a database (unencrypted filesystem)
    d) encryption of filesystem
    For full security you might aim for a combination of the above. However
    consider what happens when you loose the key/certificate. Go tell the
    investigators that their data is safely stored but neither you nor them can
    access it any more. You will be in for a lot of fun.
    If you read up on the subject you will quickly find out that the human
    element is the biggest problem. Password on sticky notes on the computer
    monitor and so on.
    Data flow between database server and webbrowser can be secured fairly easy.
    Encrypting the data itself makes you depend on not ever ever losing the
    encryption key/password/token/device.
    Same for encrypted filesystems. Encrypted filesystems on Linux are not the
    big problem any more. One better has strategies to deal with failing
    harddrives and so on. Forget about ever breaking that encryption.
    So the unless you know what you are doing and have the ressources to support
    it you better aim for encrypted data transfer and physical security.
    German courts don'f force you to do everything humanly possible at any rate.
    They want you to show that you had a best of breed concept and stuck to it
    (physical access control to server rooms, changing password, strong
    password, ssl encryption and so on).
    Besides the most vulnerable phase in your project is when your IT department
    starts to sell old drives on ebay (no joke). Then you wish you had data
    encrypted on the drive.
    All this is as always personal opinion.
    Regards,
    Sebastian Hilbert
  • kristiakkristiak Posts: 1,301 ✭✭✭
    Many agencies are becoming very restrictive with data both on paper and
    digital and at least in my experience they are unlikely to have you export
    data outside of the EU. It would require a specific consent that the
    patients are fully aware of the risks involved sending data abroad.
    Hospitals have been fined large sums of money because they faxed hospital
    records to another hospital without having a verified link and a verify
    authorized recipient .
    /Krister
    -----Original Message-----
    [mailto:[email protected]] On Behalf Of Sebastian Hilbert
    Sent: 16 December 2011 13:59
    To: [email protected]
    Subject: Re: [Developers] Encryption of data on PostgreSQL
    On Friday, December 16, 2011 01:53:16 PM Janus Engstrøm wrote:
    Hi,

    > > This makes - at least for me - things easier, but this may of course
    > > not be the issue of OpenClinica users in other countries. Too, if the
    > > study is to be FDA compliant, the quote posted by Krister _may_ be
    > > interpreted as a requirement of encryption.
    > >
    How do you encrypt the data on paper CRFs before you send them via snail
    mail to the US. Beats me and I would like to see that requirement defended
    in court.
    Sebastian
  • kristiakkristiak Posts: 1,301 ✭✭✭
    Well that is the same as here in Sweden. You get away with that as long as everything is handled professionally.

    /Krister
    Sent: 16 December 2011 13:53
    To: [email protected]
    Subject: Re: [Developers] Encryption of data on PostgreSQL

    Hi Sebastian,
    Thank you for your reply on this thread.
    I see your point, the human factor is always the bigger issue! I have just looked into Danish legislation on the subject, and they are actually pretty clear on this area: The login facility of OpenClinica, combined with the physical environment towards securing the server and the logical security (firewall, authenticated access to the OS of the server, etc) are sufficient for securing the data. They advocate for using a pseudo-anonymisation by using ID-numbers instead of personal identification and then let the investigators keep af reference list between the ID-number and the personal identification number.
    This makes - at least for me - things easier, but this may of course not be the issue of OpenClinica users in other countries. Too, if the study is to be FDA compliant, the quote posted by Krister _may_ be interpreted as a requirement of encryption.
    Best regards,
    Janus Engstrøm
    Application Developer
    Copenhagen Trial Unit
    Rigshospitalet, afd. 33.44
    Tlf. 3545 7161
    Sebastian Hilbert
    Sent by: [email protected]
    16-12-2011 12:57
    Please respond to
    [email protected]

    To

    [email protected]
    cc

    Subject

    Re: [Developers] Encryption of data on PostgreSQL


    On Friday, December 16, 2011 11:15:04 AM Janus Engstrøm wrote:
    Hi all,
    We are starting to talk about different layer of encryption here.
    a) encrpyption of the traffic between browser and webserver
    b) encryption between PG database and pg client (webapp)
    c) encryption of data in a database (unencrypted filesystem)
    d) encryption of filesystem
    For full security you might aim for a combination of the above. However
    consider what happens when you loose the key/certificate. Go tell the
    investigators that their data is safely stored but neither you nor them can
    access it any more. You will be in for a lot of fun.
    If you read up on the subject you will quickly find out that the human element
    is the biggest problem. Password on sticky notes on the computer monitor and
    so on.
    Data flow between database server and webbrowser can be secured fairly easy.
    Encrypting the data itself makes you depend on not ever ever losing the
    encryption key/password/token/device.
    Same for encrypted filesystems. Encrypted filesystems on Linux are not the big
    problem any more. One better has strategies to deal with failing harddrives
    and so on. Forget about ever breaking that encryption.
    So the unless you know what you are doing and have the ressources to support
    it you better aim for encrypted data transfer and physical security.
    German courts don'f force you to do everything humanly possible at any rate.
    They want you to show that you had a best of breed concept and stuck to it
    (physical access control to server rooms, changing password, strong password,
    ssl encryption and so on).
    Besides the most vulnerable phase in your project is when your IT department
    starts to sell old drives on ebay (no joke). Then you wish you had data
    encrypted on the drive.
    All this is as always personal opinion.
    Regards,
    Sebastian Hilbert
  • JanusJanus Posts: 260
    Don't we just love Scandinavia? :)
    Med venlig hilsen
    Janus Engstrøm
    Application Developer
    Copenhagen Trial Unit
    Rigshospitalet, afd. 33.44
    Tlf. 3545 7161
    "Krister Kristianson"
    Sent by: [email protected]
    16-12-2011 15:44
    Please respond to
    [email protected]

    To

    cc

    Subject
    Re: [Developers] Encryption of data on PostgreSQL

    Well that is the same as here in Sweden. You get away with that as long as everything is handled professionally.

    /Krister
    Sent: 16 December 2011 13:53
    To: [email protected]
    Subject: Re: [Developers] Encryption of data on PostgreSQL

    Hi Sebastian,
    Thank you for your reply on this thread.
    I see your point, the human factor is always the bigger issue! I have just looked into Danish legislation on the subject, and they are actually pretty clear on this area: The login facility of OpenClinica, combined with the physical environment towards securing the server and the logical security (firewall, authenticated access to the OS of the server, etc) are sufficient for securing the data. They advocate for using a pseudo-anonymisation by using ID-numbers instead of personal identification and then let the investigators keep af reference list between the ID-number and the personal identification number.
    This makes - at least for me - things easier, but this may of course not be the issue of OpenClinica users in other countries. Too, if the study is to be FDA compliant, the quote posted by Krister _may_ be interpreted as a requirement of encryption.
    Best regards,
    Janus Engstrøm
    Application Developer
    Copenhagen Trial Unit
    Rigshospitalet, afd. 33.44
    Tlf. 3545 7161
    Sebastian Hilbert
    Sent by: [email protected]
    16-12-2011 12:57
    Please respond to
    [email protected]

    To
    [email protected]
    cc

    Subject
    Re: [Developers] Encryption of data on PostgreSQL


    On Friday, December 16, 2011 11:15:04 AM Janus Engstrøm wrote:
    Hi all,
    We are starting to talk about different layer of encryption here.
    a) encrpyption of the traffic between browser and webserver
    b) encryption between PG database and pg client (webapp)
    c) encryption of data in a database (unencrypted filesystem)
    d) encryption of filesystem
    For full security you might aim for a combination of the above. However
    consider what happens when you loose the key/certificate. Go tell the
    investigators that their data is safely stored but neither you nor them can
    access it any more. You will be in for a lot of fun.
    If you read up on the subject you will quickly find out that the human element
    is the biggest problem. Password on sticky notes on the computer monitor and
    so on.
    Data flow between database server and webbrowser can be secured fairly easy.
    Encrypting the data itself makes you depend on not ever ever losing the
    encryption key/password/token/device.
    Same for encrypted filesystems. Encrypted filesystems on Linux are not the big
    problem any more. One better has strategies to deal with failing harddrives
    and so on. Forget about ever breaking that encryption.
    So the unless you know what you are doing and have the ressources to support
    it you better aim for encrypted data transfer and physical security.
    German courts don'f force you to do everything humanly possible at any rate.
    They want you to show that you had a best of breed concept and stuck to it
    (physical access control to server rooms, changing password, strong password,
    ssl encryption and so on).
    Besides the most vulnerable phase in your project is when your IT department
    starts to sell old drives on ebay (no joke). Then you wish you had data
    encrypted on the drive.
    All this is as always personal opinion.
    Regards,
    Sebastian Hilbert
This discussion has been closed.