The Ubiq Postgres / Greenplum integration enables data-focused roles and use-cases where report writers, data analysts, data scientists, and even data pipelines need to protect data without having an “application” in between the user and the data. Encrypt, decrypt, and protect your data with granular access controls natively in Postgres / Greenplum without changing how your users work with their data. This overview focuses specifically on how the Postgres / Greenplum library works and how to use it.

How does Ubiq Work on Postgres / Greenplum?

For some background, our whitepaper gets into the nitty gritty on how Ubiq works when integrated into an application. The native integration with the Postgres / Greenplum library works just the same… but without the need for an application.

Integration instructions are available in our public docs as you’d expect here, and once you’re ready you use it, you use the same, simple code just like our other libraries:

Using with Visualization or Other Tools

Once you have the power to encrypt/decrypt at the SQL level, you can leverage that from any tool that’s accessing your data. Take, for example, the scenario where you have data encrypted with Ubiq and you don’t want a DBA that’s querying data to be able to see it, but you have a report in Tableau or PowerBI or some other visualization tool that needs to show the data decrypted in plain text.

Your data flow might look something like this:

Where your data is exposed through the entire ETL and reporting flow:

• Stored in plain text in Postgres / Greenplum
• Shown in plain text when a Postgres / Greenplum user or DBA queries it
• Retrieved in plain text from the reporting / visualization tool
• Shown in plain text on a report to all users

Once data is encrypted in Postgres / Greenplum itself, however, you can choose where to expose (or not) the plain text data anywhere that it is queried. In this example, we would choose not to allow a DBA or regular Postgres / Greenplum user to decrypt our SSNs, and we also will choose not to expose SSNs by default in any existing reports. But we can then choose to create a report that explicitly decrypts and shows that sensitive data.

Postgres / Greenplum Integration vs. Other Ubiq Libraries

It’s all the same - the Postgres / Greenplum integration and its encrypt/decrypt is completely cross-compatible with all of the other libraries and languages.

All of the same features and values still hold true for using Ubiq on Postgres / Greenplum:

• Data never leaves your environment to encrypt or decrypt data
• No changes needed to your Postgres / Greenplum schema to store data encrypted vs. plaintext
• Data is encrypted before it gets persisted - so access from anyone else to your data will only see the ciphertext unless they have access to decrypt
• No key management required - just like our application library usage, keys “follow the data” and are managed in the Ubiq SaaS UI for seamless key rotation and revocation
• Flexible access controls; Ubiq API keys are used to authenticate the Postgres / Greenplum user to Ubiq, and that gives them access (or not) to encrypt or decrypt various sets of data
• Flexible key association - your key and dataset design in the Ubiq SaaS UI can enable granular key usage (like a unique key per table, per column or per Postgres / Greenplum database) without any implementation complexity - the SQL queries don’t need to change or even know about the keys
• Cross-library compatibility - our Postgres / Greenplum integration uses the same NIST-approved structured (format-preserving) encryption algorithm, so you can encrypt/decrypt with Postgres / Greenplumy and then encrypt/decrypt with any other Ubiq library

Summary

The Postgres / Greenplum library works the same way as our datawarehouse integrations and application-language-specific libraries:

• Completely self-contained - no external dependencies or customizations in Postgres / Greenplum
• No change to your data flow (data never leaves Postgres / Greenplum to encrypt/decrypt)
• No encryption knowledge required to implement … simple exposure of an ubiq_encrypt() and ubiq_decrypt()function (UDF) that is directly callable from SQL
• Similar performance profile to application-language libraries and similar performance design considerations for authenticating to our backend and key caching
• Cross-compatible encryption/decryption with every other Ubiq library
• Total feature parity - including key management and key rotation features delivered and managed through the Ubiq UI