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. 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 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.
Symmetric encryption example (AES)
This example shows how to encrypt and decrypt data using AES encryption with a secret key:
-- Symmetric encryption using AES (Advanced Encryption Standard)
-- Encrypting data using pgp_sym_encrypt
SELECT pgp_sym_encrypt('Sensitive Data', 'secret_key');
-- Decrypting data using pgp_sym_decrypt
SELECT pgp_sym_decrypt(pgp_sym_encrypt('Sensitive Data', 'secret_key'), 'secret_key');
pgp_sym_encrypt encrypts the data using the specified key (in this case, 'secret_key').
pgp_sym_decrypt decrypts the data, returning it to its original form.
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.
Generating a digital signature
To generate a digital signature, we first sign the data with a private key using the pgp_sign() function. The private key is used to create a digital signature that can later be verified with the corresponding public key.
-- Generate a digital signature using the private key
SELECT pgp_sign('Message to be signed', dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
...
-----END PGP PRIVATE KEY BLOCK-----'));
pgp_sign() signs the message using the provided private key.
Replace the PRIVATE KEY BLOCK with the actual private key (in armored format) used to sign the data.
Verifying a digital signature
To verify the signature, we use the pgp_verify_signature() function with the original message, the signature, and the public key.
-- Verify the digital signature using the public key
SELECT pgp_verify_signature(
'Message to be signed',
dearmor('-----BEGIN PGP SIGNATURE-----
...
-----END PGP SIGNATURE-----'),
dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
...
-----END PGP PUBLIC KEY BLOCK-----')
);
pgp_verify_signature() checks whether the signature is valid for the provided message using the public key.
Replace the SIGNATURE BLOCK with the actual signature generated during the signing process, and replace the PUBLIC KEY BLOCK with the corresponding public key.
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.
Here's a complete example that covers both registration (storing hashed passwords) and login (authenticating the user):
-- Step 1: Create a table to store user credentials
CREATE TABLE users (
id serial PRIMARY KEY,
username text NOT NULL UNIQUE,
password_hash text NOT NULL
);
-- Step 2: Hash the password and insert a new user (registration)
INSERT INTO users (username, password_hash)
VALUES ('user1', crypt('my_secure_password', gen_salt('bf')));
-- Step 3: Authenticate the user (login)
SELECT username
FROM users
WHERE username = 'user1'
AND crypt('my_secure_password', password_hash) = password_hash;
Summary:
The crypt() function hashes passwords securely using a one-way hashing algorithm.
gen_salt() is used to generate a salt to add randomness and protect against rainbow table attacks.
When verifying passwords, you rehash the entered password using the same salt and compare the result to the stored hash.
This approach helps protect user passwords and adds significant security to sensitive accounts stored in the database.
How to Install pgcrypto With Timescale
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:
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 quickly, cost-effectively, and with significant storage savings.
To install pgcrypto, run the following command:
CREATE EXTENSION IF NOT EXISTS pgcrypto;
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
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 to encrypt in your database. To read this encrypted data, you will need to provide the key every time. The data will even be encrypted in backups and dumps.
CREATE TABLE sensor_readings (
time TIMESTAMPTZ NOT NULL,
device_id BIGINT,
reading BYTEA
-- other columns);
SELECT create_hypertable('sensor_readings', 'time');
Notice that the reading column is a BYTEA—it will store our encrypted payload.
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'));
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 securely store and manage this key.
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;
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.
How OpenSauced Is Building a Copilot for Git History With pgvector and Timescale