pgcrypto in PostgreSQL Explained: Encryption, Hashing, Functions

When it comes to securing sensitive information inside a database, pgcrypto in PostgreSQL is one of the most powerful and reliable extensions available. In modern applications where data breaches, cyberattacks, and compliance requirements are major concerns, pgcrypto provides developers and database administrators with a flexible, built-in cryptographic toolkit. This article explores everything you need to know about pgcrypto in PostgreSQL – how it works, why it’s used, and how to implement it safely – with examples and best practices.

What is pgcrypto in PostgreSQL?

pgcrypto in PostgreSQL is a cryptographic extension that allows you to encrypt, decrypt, hash, and digitally sign data directly inside your PostgreSQL database. It comes with a set of mathematical and cryptographic functions designed to help store sensitive information securely.

You can use pgcrypto for tasks like:

• Data encryption and decryption
• Password hashing
• Generating digital signatures
• Encrypting large text or bytea data
• Securing personally identifiable information (PII)

In short, pgcrypto gives PostgreSQL strong, built-in cryptographic capabilities – without needing an external service or tool.

Uses of pgcrypto in PostgreSQL?

Cybersecurity threats continue to rise every year. Storing plain-text sensitive information in a database is one of the biggest security mistakes developers can make.

Here’s why pgcrypto is essential:

• Protects Sensitive Data at Rest: Encryption ensures that even if someone gains unauthorized access to your database, they can’t read the data.
• Helps with GDPR, HIPAA, PCI-DSS, and ISO Compliance: Many industries require encryption of personal or financial data.
• Provides Built-In Password Hashing: pgcrypto supports industry-standard hashing algorithms like: bcrypt, SHA-256, and SHA-512
• Works Inside SQL: You can generate hashes, encrypt data, and decrypt values directly inside queries—making it easier to build secure applications.

Because of all these reasons, thousands of businesses rely on pgcrypto in PostgreSQL to secure their applications.

How to Install pgcrypto in PostgreSQL

Installing pgcrypto is simple. Most PostgreSQL installations already include the extension, and you only need to enable it inside the database.

Run:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

You can verify installation with:

SELECT * FROM pg_extension WHERE extname = 'pgcrypto';

SELECT * FROM pg_extension WHERE extname = 'pgcrypto';

If it’s listed, the extension is active.

Types of Encryption in pgcrypto

Encryption is the process of converting readable data (plaintext) into unreadable data (ciphertext) so that unauthorized users cannot access it. In PostgreSQL, pgcrypto provides two main types of encryption: symmetric encryption and asymmetric encryption. Each type serves different purposes depending on your use case.

Symmetric Encryption

Symmetric encryption uses one key for both encryption and decryption. This means the same secret key must be shared between whoever is encrypting and decrypting the data. It’s faster than asymmetric encryption and is ideal for encrypting large amounts of data within the database.

How Symmetric Encryption Works

1. You choose a secret key (passphrase).
2. Use that key to convert plaintext into ciphertext.
3. Store the ciphertext in your database.
4. Use the same key to decrypt the data when needed.

Supported Algorithms in pgcrypto

• AES (Advanced Encryption Standard): AES-128, AES-192, AES-256
• Blowfish
• Triple DES (3DES)

AES is the most recommended today because of its high security and performance.

Example of Symmetric Encryption

Encrypting data:

SELECT encrypt('Secret Message', 'my_strong_password', 'aes');

SELECT encrypt('Secret Message', 'my_strong_password', 'aes');

This converts 'Secret Message' into unreadable bytes.

Decrypting data:

SELECT convert_from(
    decrypt(encrypted_column, 'my_strong_password', 'aes'), 
    'UTF8'
);

SELECT convert_from(
    decrypt(encrypted_column, 'my_strong_password', 'aes'), 
    'UTF8'
);

Asymmetric Encryption

Asymmetric encryption uses a key pair: a public key for encryption and a private key for decryption. Unlike symmetric encryption, you don’t need to share the private key. This makes asymmetric encryption ideal for secure communication or situations where multiple users need to encrypt data but only one should decrypt it.

How Asymmetric Encryption Works

1. Generate a key pair: public and private keys.
2. Share the public key with anyone who needs to encrypt data.
3. Use the private key to decrypt the data.

Supported Algorithms in pgcrypto

• RSA (most common)
• DSA

Example of Asymmetric Encryption:

Encrypting data using a public key:

SELECT pgp_pub_encrypt('Sensitive Data', dearmor('PUBLIC_KEY'));

SELECT pgp_pub_encrypt('Sensitive Data', dearmor('PUBLIC_KEY'));

Decrypting using the private key:

SELECT pgp_pub_decrypt(encrypted_column, dearmor('PRIVATE_KEY'));

SELECT pgp_pub_decrypt(encrypted_column, dearmor('PRIVATE_KEY'));

Keep private keys secure and never share them publicly. They are required for decryption. Asymmetric encryption is more secure for external communication but slower than symmetric encryption.

Hashing Using pgcrypto in PostgreSQL

Hashing is different from encryption. Encryption can be reversed (with a key), but hashing cannot be reversed.

Popular use cases:

• Password storage
• Token generation
• Data integrity verification

Example: SHA-256 Hashing

SELECT digest('my_password', 'sha256');

SELECT digest('my_password', 'sha256');

Example: bcrypt Hashing

This is the most secure method for password storage.

SELECT crypt('my_password', gen_salt('bf'));

SELECT crypt('my_password', gen_salt('bf'));

To verify passwords:

SELECT crypt('my_password', stored_hash) = stored_hash;

SELECT crypt('my_password', stored_hash) = stored_hash;

This is how secure authentication is built using pgcrypto in PostgreSQL.

Encrypting Text and Bytea Data

pgcrypto makes it easy to encrypt both text and bytea values directly inside PostgreSQL. This helps protect sensitive data such as names, emails, or file contents stored in the database.

Encrypting Data

To encrypt a text value, you simply pass the data, your secret key, and the encryption algorithm. The output will be stored as unreadable ciphertext.

SELECT encrypt('customer name', 'pass123', 'aes');

SELECT encrypt('customer name', 'pass123', 'aes');

Decrypting Data

When you need to read the encrypted value, decrypt it using the same key. convert_from converts the bytea output back into readable text.

SELECT convert_from(
    decrypt(encrypted_column, 'pass123', 'aes'), 
    'UTF8'
);

SELECT convert_from(
    decrypt(encrypted_column, 'pass123', 'aes'), 
    'UTF8'
);

In production, it’s recommended to never hard-code encryption keys. Use environment variables or an external key management system.

Storing Encrypted Data in bytea

Whenever you encrypt information using pgcrypto, PostgreSQL stores the result as bytea (binary data). This ensures the encrypted content remains unreadable and secure while still being easy to query and retrieve when needed.

Example table:

A secure table typically stores sensitive fields, such as names, emails, and notes, in bytea columns so they can safely hold encrypted values.

CREATE TABLE secure_users (
    id SERIAL PRIMARY KEY,
    full_name BYTEA,
    email BYTEA,
    encrypted_notes BYTEA
);

CREATE TABLE secure_users (
    id SERIAL PRIMARY KEY,
    full_name BYTEA,
    email BYTEA,
    encrypted_notes BYTEA
);

Insert encrypted values:

Before inserting data, encrypt it using your chosen key and algorithm. PostgreSQL will save the encrypted output as binary, keeping the original text protected.

INSERT INTO secure_users(full_name, email)
VALUES(
    encrypt('John Doe', 'mykey', 'aes'),
    encrypt('john@example.com', 'mykey', 'aes')
);

INSERT INTO secure_users(full_name, email)
VALUES(
    encrypt('John Doe', 'mykey', 'aes'),
    encrypt('john@example.com', 'mykey', 'aes')
);

Using pgcrypto for Real-World Use Cases

The pgcrypto extension is not just a theoretical tool – it powers many real-world database security implementations. Below are expanded, practical examples of how pgcrypto is used in production systems, with clarity and human-style explanations.

Encrypting Customer Data

Businesses often store personal information like SSN, or passport details. With pgcrypto, this data is encrypted before storing it in the database, ensuring that even if someone accesses the table, they cannot read the sensitive values.

UPDATE customers
SET aadhaar = encrypt(aadhaar_text, 'secure_key', 'aes');

UPDATE customers
SET aadhaar = encrypt(aadhaar_text, 'secure_key', 'aes');

Securing API Keys

API keys and integration secrets should never be stored in plain text. pgcrypto allows you to encrypt these keys so they remain secure and unusable to unauthorized users.

UPDATE integrations
SET api_key = encrypt(api_key_raw, 'key123', 'aes');

UPDATE integrations
SET api_key = encrypt(api_key_raw, 'key123', 'aes');

Password Hashing in Authentication Systems

Instead of encrypting passwords (which can be decrypted), pgcrypto uses secure one-way hashing. This ensures passwords are stored safely and cannot be reversed even if the database is exposed.

INSERT INTO users(username, password)
VALUES('admin', crypt('admin123', gen_salt('bf')));

INSERT INTO users(username, password)
VALUES('admin', crypt('admin123', gen_salt('bf')));

Logging Sensitive Audit Data Securely

Audit logs sometimes contain sensitive transaction details. Encrypting them ensures they remain protected while still allowing secure retrieval when needed.

INSERT INTO audit_logs(data)
VALUES(encrypt('Payment completed', 'key99', 'aes'));

INSERT INTO audit_logs(data)
VALUES(encrypt('Payment completed', 'key99', 'aes'));

Performance Considerations

Using pgcrypto in PostgreSQL does come with some performance overhead, especially for:

• Large text fields
• Frequent encryption/decryption
• Asymmetric cryptography

To optimize:

• Use symmetric encryption for large files
• Avoid decrypting data in SELECT * queries
• Use hashed values for comparisons instead of decrypting

Best Practices for Using pgcrypto in PostgreSQL

• Never store encryption keys inside SQL queries: Use .env files, KMS (AWS, GCP, Azure), and HashiCorp Vault
• Avoid encrypting indexed columns: Encrypted values are unpredictable -> indexes become useless.
• For passwords, always use bcrypt: Do NOT use SHA-256 for passwords.
• Separate encrypted and unencrypted data: Only encrypt what is necessary.
• Use pgcrypto functions inside stored procedures: Keeps sensitive operations centralized.

Limitations of pgcrypto in PostgreSQL

While powerful, pgcrypto has some limitations:

• Cannot index encrypted columns
• Decryption is CPU-intensive
• Requires a key management strategy
• Asymmetric keys must be stored securely
• Not ideal for extremely large binary files (use S3 + client-side encryption)

Still, for everyday encryption needs, pgcrypto in PostgreSQL is more than enough.

Final Thoughts

In today’s security-focused environment, encrypting sensitive data is not optional—it’s a necessity. pgcrypto in PostgreSQL offers a simple, reliable, and highly flexible solution for database-level encryption, hashing, and secure data management. Whether you’re building authentication systems, protecting customer data, or complying with global data protection laws, pgcrypto gives you the tools to secure your PostgreSQL database effectively.

Its built-in functions, powerful cryptography support, and easy integration with SQL queries make it one of the most valuable extensions PostgreSQL offers.