Need technical support in decryption of a columns in Vertica DB through any supporting client tools

manju_rao

Currently, we are loading the data to Vertica database through our internal ETL process and we are encrypting required columns using the encryption mechanism: AES 256 CBC with Message Digest SHA256 algorithm.

We need support to know if there is a mechanism to get Decrypt data while data gets viewed in all Vertica database client tools like Toad, DB Visualizer and other tools. Currently, we are connecting this Vertica Database using ODBC driver in Windows for your references.
Please help us to know your expertise suggestions and feedback for the same.

Bryan_H

You would need to implement a UDx function similar to https://github.com/vertica/Vertica-Extension-Packages/tree/master/encryption_package and find a way to pass the key(s) into the function securely.

manju_rao

Hi Bryan,

Thanks for your solution feedback. Currently, we are looking for an interceptor implementation between the result-set view of the query so that any column’s encryption in the result can be decrypted with the configurable passphrase, while viewing in Vertica supported client tools (like Toad, DB Visualizer and any other client tools).

Is there any extension interfaces from Vertica that can be used to develop this feature and integrate with Vertica ODBC. Appreciate, if you can help us to provide response for the same.

Thanks
Manjunatha Rao V.

Bryan_H

Unfortunately, the ODBC driver is closed source, and I am not aware of any timeline to release it as open source. We could not provide support for a modification, though it might be possible though difficult to write a wrapper for the ODBC/JDBC drivers that implemented a decrypt function. The Python driver is open source, so it might be technically possible to write in Python, though none of the applications you list support Python.
What problem are you trying to solve here? It's not clear to me how the driver would identify an encrypted field - or is it assumed all fields are encrypted? If you're trying to avoid sending the key over the wire, it would be possible for the User Defined Function to fetch keys from a backend service using provided info like the field name and user name. If you need to avoid rewriting queries from the application front end, then the decryption UDF could be written into a view on the encrypted table so it is transparent to the end user. Also, current Vertica versions support TLS and SSL certificates for client and node-to-node communication so it might be acceptable for your application to use wire-level encryption rather than encrypting fields.

twall

I think you may be able to achieve this by combining a few features. I'll describe an approach to achieve this, but know that there may be entirely better ways of doing if you take a step back to look at the bigger picture and consider all your threat model and security requirements as a whole, and not just the Vertica query part.

Your solution would start with an encryption & decryption function. Vertica does not have any AES encryption/decryption functions out of the box, but you can write one using our extension framework, as Bryan suggested. Vertica does integrate with Voltage's SecureData platform, which provides format preserving encryption functions but not AES.

You can use Vertica's column access policies to transparently rewrite a column reference to a different expression when a user runs a query. This Voltage integration blog shows an example of how you can use access policies to transparently call a decryption function to selectively decrypt a column based on a user's role: https://www.vertica.com/blog/whats-new-9-1-voltage-securedata-integration/. That example uses the Voltage functions but you could follow the same approach with your own AES functions.

Your encryption function will need a way for the user to specify their key or secret needed to decrypt. There are various ways you can do this.

The voltage example does role-based decryption. The keys were persisted in a configuration file Vertica has access to, and the fact that the user could log in to Vertica was sufficient enough proof to warrant access to the keys.

If that is insufficient, you probably want to explore user-defined session parameters to store secrets. Session parameters are transient, in-memory key/value pairs scoped to the lifetime of your database session. Different users can log in and set their own session parameters. They cannot be seen from other sessions and are automatically cleaned up when the session is ended.

You could require that users set a password or decryption key in a session parameter, and your decryption function can read that parameter to perform decryption. The Voltage integration can accept username & password as session parameters. This information is used to authenticate to the SecureData service, which then provides the keys to do decryption.

You can read more about session parameters here: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ExtendingVertica/UDx/Parameters/UDSessionParameters.htm.

Finally, you want a clean way users to set up session parameters. It is annoying for users to have to remember to type an ALTER SESSION SET UDPARAMETER ... at the start of each of their sessions in order to be able to successfully read decrypted data. Each client has a way to initialize a session with SQL. In vsql, users could put their password in an ALTER SESSION SET UDPARAMETER ... command in their .vsqlrc file so they don't have to type it every time. In ODBC, the parameter to do this is called ConnSettings (https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/ClientODBC/DSNParameters.htm). This could be persisted in a DSN definition so that a user doesn't have to remember to do it all the time when they are using their favorite tools.

So, to recap:
1. User sets a session parameter with a secret in a their DSN via ConnSettings
2. User queries a table with encrypted data as if it were a normal column
3. The encrypted column has an access policy which can do a permissions check, and if necessary call your custom decrypt() function
4. Your decrypt function reads the session parameter and uses it to perform AES decryption

Of course, this approach has some downsides. Now you have all your users writing down sensitive keys or passwords in various configuration files, and if the sessions they create aren't using TLS, then those ALTER SESSION commands to set the password are happening in clear text on your network. You can configure Vertica to forbid plaintext client connections, and you could write a fancier decrypt function that uses kerberos and/or a key management service to avoid persisting secrets in configuration. There's always going to be tradeoffs between ease of use, development time and security; running through them all before you start will make for a much easier implementation.