Re: Encryption For Specific Column- Where to store the key

Hello

try to use a security definer functions

http://www.postgresql.org/docs/current/static/sql-createfunction.html

inside this function you can access to resourcess thats are no
available from outer for web user

Regards

Pavel Stehuke

2011/6/15 Manuel Gysin <manuel(dot)gysin(at)quantum-bytes(dot)com>:
> Hello
>
>
> I'm currently designing a database layout where some columns are encrypted.
> Some tables contains sensitive user data which needs a special protection.
>
> I used http://www.postgresql.org/docs/8.1/static/encryption-options.html as a guide.
>
> - For the password field I just used a hash algorithm with some loops to protect the passwords ("Password Storage Encryption" with bcrypt).
> - For the sensitive columns I used "Encryption For Specific Columns", here I have later some questions.
> - For general data encryption I used luks (crypsetup) "Data Partition Encryption"
> - Connection is secured like desc. in "Encrypting Data Across A Network" with "SSL Host Authentication"
>
> Much attack use cases are covered with this but I see one problem:
>
> 1. There is a frontend (webserver) and a backend (database)
>   - backend must be configured to not allow to much queries in a given time, else there is a possibility to get around the whole security stuff
>   - frontend needs too some protection against brute force
> 2. When encrypting some columns I need to save somewhere the key.
>   - Frontend (very bad idea, first point of failure)
>   - Backend (when someone can dump the database, he got the key too, encryption is in this use case useless)
>   - Remote database (when someone can hack to the first db, it's not far away to the second db I think, but there is more time to register an attack and force shutdown everything)
>   - Write an dedicated application (when someone hacked this server, it's only a matter of time before he can find out where the key is stored in the RAM)
>
> So it seems there is no protection when someone gained access to the database server. Or is there a way? I can't see any.
> I'm not fit enough in attack a database server, but I think when someone has access to the database, he can simply dump the whole tables, while the key is stored in the table, he has full access to everything in the database. At the end the question is, where and how I should store the key to decrypt the columns?
>
> A discussion about this topic can be found under http://www.experts-exchange.com/Database/PostgreSQL/Q_21934798.html (answers are not all the time displayed...)
> But there were no final solution at all.
>
>
> Kind regards
> Manuel Gysin
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>