Sensitive Data Encryption With Supabase

Sensitive data often includes personally identifiable information such as names, addresses, social security numbers and financial details. Protecting this information is essential to safeguard individuals' privacy and prevent unauthorized access or misuse. Businesses and organizations that handle sensitive data are entrusted with the responsibility of maintaining the trust of their customers, clients and stakeholders. A data breach can severely damage reputation and erode trust, resulting in financial losses and long-term consequences.

Encryption in PostgreSQL

In PostgreSQL, the pg_crypto extension provides cryptographic functions, including encryption and hashing. If you want to perform encryption in PostgreSQL using pg_crypto, you can use the functions provided by this extension. Below is an example of how to use pg_crypto for encryption in PostgreSQL.

We should ensure that the pg_crypto extension is available and installed in your PostgreSQL database. It is often included by default, but you can verify its existence by running the following query.

CREATE EXTENSION IF NOT EXISTS pg_crypto;

Use the pgp_sym_encrypt function to encrypt data. Refer to the below query.

SELECT pgp_sym_encrypt('Hello, World!', 'my_secret_key');

Replace my_secret_key with your actual secret encryption key.

SELECT pgp_sym_decrypt('encrypted_data', 'my_secret_key');

It is necessary to use an efficient key management system. Remember that the security of your encrypted data heavily depends on the security of the encryption key. Treat it with the utmost care and follow security best practices to protect it from unauthorized access.

Supabase - The Saviour

Supabase has introduced the concept of vault. Vault, an extension for Postgres along with the Supabase UI, provides a secure and user-friendly solution for storing encrypted secrets and various data in your database. This enables innovative uses of Postgres beyond the capabilities offered in a standard distribution.

Vault offers two different categories.

Secrets

Encryption Key

Secrets

In Secrets, you can store important information like environment variables, API keys and much more. We can access these secrets in Postgres Functions, Triggers and WebHooks.

Encryption Key

While using standard PostgreSQL, we need to use an external key management system to store such encryption keys securely. Supabase takes a step forward by giving a simplified solution.

We have to just type in the key name and Supabase takes care of the rest.

We can then access the key anywhere in the database with its name.

Let me show you a simple encryption and decryption in Supabase. In Supabase's SQL editor, enter the below query and run it.

We are now going to insert encrypted data into clients table.

To do that, we need an encryption key. Let us go to Project settings in the Supabase dashboard and click on vault. In Vault, click on Encryption Keys tab and click Add a new key.

Enter a key name and click Add Key.

In SQL editor, run the query as shown in the image.

Give your key name in the place of encryptKey.

Let us now look at the table data.

We can see that the values in the name column are stored as encryted values.

Note: Kindly check whether the pg_crypto extension is enabled in Supabase. You can check it in the Database section of the Dashboard. If it is disabled, kindly enable it.

Decryption

To access the original value from the encrypted value, we have to decrypt it.

While creating the clients table, we used varchar datatype for name column. So while decrypting, we have to perform datatype casting for name column using bytea. To avoid this, we can use bytea datatype instead of varchar, while creating the table.

CREATE TABLE public.clients (id serial primary key,name bytea not null);

Note: When implementing encryption and decryption in PostgreSQL, the choice of datatype depends on the specific requirements of the application and the characteristics of the data being encrypted. The bytea datatype is a common and suitable choice for scenarios where binary representation and compatibility with encryption libraries are key considerations.

Encryption Simplified

By leveraging Supabase's robust security features and its integration with PostgreSQL, users can implement a secure and efficient encryption strategy for protecting sensitive data. The use of cryptographic functions, careful key management and adherence to best practices contribute to a comprehensive approach to data security.

Thank you for your wonderful time. Let us meet again in an interesting blog.