Security

PostgreSQL Extensions: Secure Your Time-Series Data With pgcrypto

pgcrypto is a PostgreSQL extension that enables you to encrypt, decrypt, hash, and create digital signatures within your database. Here at Timescale, we know how precious your data is and fully recognize the significance of safeguarding sensitive information.

In this article, we'll show you how you can install and use pgcrypto to bring an added layer of security to your database. Whether you're dealing with financial records, IoT telemetry, or other sensitive time-series data, we hope to provide you with valuable insights and practical strategies to robust your data security.

Understanding the Basics of pgcrypto

The pgcrypto extension in PostgreSQL provides cryptographic functions and capabilities within the database. It offers various functions for performing cryptographic operations, including encryption, decryption, hashing, and digital signatures.

What can you use pgcrypto for?

Here are some key features and use cases of pgcrypto:

1. Encryption and decryption: pgcrypto allows you to encrypt and decrypt data within the database. It supports symmetric encryption algorithms like AES, Blowfish, and DES and asymmetric encryption algorithms like RSA. This functionality is useful for protecting sensitive data stored in the database.

2. Hashing: The extension provides hash functions such as MD5, SHA-1, SHA-256, and others. Hashing is commonly used to store passwords securely by storing only the hash of a password rather than the actual password itself. When a user enters a password, it can be hashed and compared to the stored hash for authentication.

3. Digital signatures: pgcrypto supports digital signatures using public-key cryptography. It allows you to generate and verify digital signatures, ensuring data integrity and authenticity. Digital signatures are commonly used for data verification and non-repudiation purposes.

4. Key management: pgcrypto offers functions for generating and managing encryption keys. It allows you to create and use symmetric and asymmetric keys for encryption and decryption operations. Key management is crucial for ensuring the security of encrypted data.

5. Secure hashed passwords: pgcrypto includes a function called crypt() that can be used to generate secure hashed passwords. It uses a one-way function to hash passwords and incorporates salt for added security. This helps protect user passwords and prevent unauthorized access to sensitive accounts.

Overall, pgcrypto is a powerful extension that enhances the security capabilities of PostgreSQL. It provides a convenient way to perform cryptographic operations within the database, allowing you to secure data, protect passwords, and ensure the integrity and authenticity of the information.

How to Install pgcrypto With Timescale

By using the pgcrypto extension with Timescale, you can leverage its cryptographic functions to encrypt sensitive information within your time-series data or to hash and verify data integrity.

Here's an example of how you can incorporate pgcrypto into your Timescale setup:

Install pgcrypto: First, ensure you have the pgcrypto extension installed in your database. Timescale is built on PostgreSQL but significantly expands its boundaries, allowing you to build applications faster, more cost-effectively, and with significant storage savings.

To install pgcrypto, run the following command:

CREATE EXTENSION IF NOT EXISTS pgcrypto;Copy

On Timescale, you can find available extensions by going to Operations > Extensions from your service overview, which will also give you installation instructions.

Securing your time-series data with Timescale and pgcrypto

1. Create a hypertable: Create a hypertable in Timescale to store your time-series data. For example, let's say you want to create a hypertable to store sensitive device readings which you want encrypted in your database. This means that to read the data you need to provide the key every time. The data will even be encrypted in backups and dumps.

2. CREATE TABLE sensor_readings (
   time      TIMESTAMPTZ NOT NULL,
   device_id BIGINT,
   reading   BYTEA
   -- other columns);
   SELECT create_hypertable('sensor_readings', 'time');Copy

Notice that the reading column is a BYTEA—it will store our encrypted payload.

1. Now you can use the pgp_sym_encrypt function to encrypt the data. Let's insert an encrypted temperature reading:

   
   INSERT INTO sensor_readings (time, device_id, reading)VALUES (NOW(), 1, pgp_sym_encrypt('20.5', 'supersecretkey'));Copy

The '20.5' value is the reading to encrypt, and 'supersecretkey' is the secret key to use for encryption. In a real-world scenario, you'd probably get these values from an application, and the secret key would be securely stored and managed.

To decrypt this data, you'd use the pgp_sym_decrypt function:

SELECT time, device_id, pgp_sym_decrypt(reading, 'supersecretkey') AS reading
FROM sensor_readings
WHERE device_id = 1;Copy

This query will return the time, device ID, and decrypted temperature for the device with ID 1.

Note: These examples use symmetric encryption, where the same key is used for both encryption and decryption. pgcrypto also supports asymmetric encryption if that better fits your needs. Make sure to handle and store your keys securely to maintain the security of your encrypted data.

By combining Timescale's time-series capabilities with pgcrypto's cryptographic functions, you can secure sensitive data within your time-series database, encrypt specific columns, and ensure the integrity of your data using hashing. Remember to choose appropriate encryption algorithms and ensure proper key management practices to maintain the security of your encrypted data.

Start Securing Your Data Today

If you are working with time-series data and want to add another protection layer to your database, install pgcrypto and start using it with Timescale today.

Haven't tried Timescale yet? Sign up for a free 30-day trial (no credit card required) or self-host Timescale's database core, TimescaleDB.