{"id":7441,"date":"2025-03-19T11:15:31","date_gmt":"2025-03-19T11:15:31","guid":{"rendered":"https:\/\/test.opensource-db.in\/wp1\/?p=7441"},"modified":"2025-03-19T11:15:34","modified_gmt":"2025-03-19T11:15:34","slug":"enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2","status":"publish","type":"post","link":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/","title":{"rendered":"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part – 2"},"content":{"rendered":"\n

In the previous blog post<\/a>, we discussed how to use pgcrypto <\/strong>for encryption and decryption to secure sensitive data within your PostgreSQL <\/strong>databases. In this blog, we will explore other important cryptographic features provided by the pgcrypto <\/strong>extension, including hashing and digital signatures. These functions offer additional layers of security for protecting data integrity, authentication, and verification.<\/p>\n\n\n\n

What is Hashing?<\/strong><\/h3>\n\n\n\n

Hashing is a process that transforms any input (such as passwords, files, or other data) into a fixed-size string of characters, typically represented as a hexadecimal number. The key property of a good hash function is that it is computationally infeasible to reverse (one-way transformation), meaning that you cannot obtain the original input from its hash value.<\/p>\n\n\n\n

Hashing is commonly used for storing passwords securely and verifying data integrity. Even if an attacker gains access to the hash, they cannot easily reverse-engineer it to obtain the original data.<\/p>\n\n\n\n

How Hashing Works in pgcrypto<\/strong><\/h3>\n\n\n\n

pgcrypto offers multiple hashing functions, including popular algorithms such as MD5, SHA-1, SHA-256, and SHA-512. These functions are designed to produce cryptographic hash values from input data, providing a unique fingerprint for that data.<\/p>\n\n\n\n

Key Hashing Functions in pgcrypto:<\/strong><\/h4>\n\n\n\n
    \n
  • One-way function<\/strong>: You can easily compute the hash from the input data, but it’s computationally infeasible to reconstruct the original input from just the hash.<\/li>\n\n\n\n
  • Deterministic<\/strong>: The same input will always produce the same hash output.<\/li>\n\n\n\n
  • Fast computation<\/strong>: Hashing algorithms are designed to be efficient.<\/li>\n\n\n\n
  • Avalanche effect<\/strong>: Even a tiny change in the input data creates a significantly different hash output.<\/li>\n\n\n\n
  • Fixed output size<\/strong>: Regardless of input size, the hash output has a fixed length.<\/li>\n\n\n\n
  • Collision resistance<\/strong>: It should be extremely difficult to find two different inputs that produce the same hash output.<\/li>\n<\/ul>\n\n\n\n

    Hashing with pgcrypto in PostgreSQL<\/strong><\/p>\n\n\n\n

    Let’s start by enabling the pgcrypto <\/strong>extension:<\/p>\n\n\n\n

    \n
    \n
    \n
    \n

    CREATE EXTENSION IF NOT EXISTS pgcrypto;<\/code><\/p>\n\n\n\n

    CREATE EXTENSION<\/code><\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n\n\n

    The digest()<\/code> Function<\/strong>

    digest(data, type)<\/code><\/strong>: This function computes the hash of the input data using the specified hash algorithm type (such as SHA-256 or MD5).<\/p>\n\n\n\n

    The digest() <\/code>function can be used to generate a hash of a given input, using various hashing algorithms. It can be applied to both text and bytea data types, and the hash is returned as a bytea.<\/p>\n\n\n\n

    Common hashing algorithms include:<\/p>\n\n\n\n

      \n
    • MD5 (now considered insecure)<\/li>\n\n\n\n
    • SHA-1 (also considered weak)<\/li>\n\n\n\n
    • SHA-256, SHA-384, and SHA-512<\/li>\n<\/ul>\n\n\n\n
      \n
      CREATE TABLE users (\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0user_id SERIAL PRIMARY KEY,\n\u00a0\u00a0\u00a0\u00a0username VARCHAR(100),\n\u00a0\u00a0\u00a0\u00a0password BYTEA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n);\nCREATE TABLE\nTime: 51.945 ms\n\nINSERT INTO users (username, password)\nVALUES (\n\u00a0\u00a0\u00a0\u00a0'Rohit',\n\u00a0\u00a0\u00a0\u00a0\u00a0digest('rohit45@', 'md5')\n);\nINSERT 0 1\nTime: 23.677 ms\n\nWhen we query the table, we'll see the hashed password:<\/code>\nSELECT * FROM users WHERE username = 'Rohit';\n\u00a0user_id \u2502 username \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 password\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 \u2502 Rohit\u00a0 \u00a0 \u2502 \\xe83f41f2cf366852f40f1bdc96e8300a\n(1 row)\nTime: 3.194 ms<\/code><\/pre>\n<\/div>\n\n\n\n
      Password Authentication<\/strong><\/h3>\n\n\n\n
      To verify a password using these hash functions, you can use the following query:<\/p>\n\n\n\n
      SELECT (digest('rohit45@', 'md5') = password) AS password_matches \nFROM users \nWHERE username = 'Rohit';\n password_matches \n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n t\n(1 row)\nTime: 0.423 ms\n<\/code><\/pre>\n\n\n\n
      The HMAC Function:<\/strong>
      The hmac(data, key, type)<\/code> function in PostgreSQL is used to generate a Hash-based Message Authentication Code (HMAC)<\/strong>, which is typically used for verifying the integrity and authenticity of data. HMAC<\/strong> is not a traditional encryption and decryption mechanism; it\u2019s a method for creating a cryptographic hash from a message and a secret key. The process is one-way (just like hashing), and it\u2019s not designed to be reversible, so it does not provide decryption<\/strong>.<\/p>\n\n\n\n
      Key Points About HMAC:<\/strong><\/h3>\n\n\n\n
        \n
      • HMAC is not encryption<\/strong>: It generates a hash based on a secret key and the data, but you cannot decrypt the hash to retrieve the original data.<\/li>\n\n\n\n
      • HMAC is useful for integrity and authentication<\/strong>: It ensures the data has not been tampered with, and it proves the authenticity of the sender if the key is known.<\/li>\n<\/ul>\n\n\n\n
        Use Case for HMAC:<\/strong><\/h3>\n\n\n\n
          \n
        • Message authentication<\/strong>: HMAC ensures that the data hasn’t been altered and that the sender was who they claimed to be. It’s often used in API authentication and digital signatures.<\/li>\n<\/ul>\n\n\n\n
          HMAC in PostgreSQL:<\/strong><\/h3>\n\n\n\n
          In PostgreSQL, the hmac()<\/code> function generates an HMAC using a cryptographic algorithm (md5, sha256, sha512, etc.) and a secret key.<\/p>\n\n\n\n
          INSERT INTO users (username, password)\nVALUES (\n    'virat',\n    hmac('virat18@'::bytea, 'mysecretkey'::bytea, 'sha256')\n);\n\nINSERT INTO users (username, password)\nVALUES (\n    'msdhoni',\n    hmac('msd07@'::bytea, 'mysecretkey'::bytea, 'md5')\n);\n\nSELECT * FROM users;\n user_id \u2502 username \u2502                              password                              \n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n       1 \u2502 virat    \u2502 \\x0dfd5b7ffe9e645854491923613c61fa43b6cf64b4d304324c1102e3b9ff02c2\n       2 \u2502 msdhoni  \u2502 \\x6b61a86fe795c8cac9f7bdabfbbb5c38\n(2 rows)\nTime: 0.705 ms\n<\/code><\/pre>\n\n\n\n
          Password Authentication<\/strong><\/h3>\n\n\n\n
          To verify a password using these hash functions, you can use the following query:<\/p>\n\n\n\n
          SELECT (hmac('virat18@', 'mysecretkey'::bytea, 'md5') = password) AS password_matches \nFROM users \nWHERE username = 'virat';\n password_matches \n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n t\n(1 row)\nTime: 0.871 ms\n<\/code><\/pre>\n\n\n\n
          Feature<\/strong><\/td>digest()<\/strong><\/td>hmac()<\/strong><\/td><\/tr>
          Purpose<\/td>Computes a hash of input data.<\/td>Computes a HMAC using a secret key.<\/td><\/tr>
          Input<\/td>Data and hash function.<\/td>Data, secret key, and hash function.<\/td><\/tr>
          Output<\/td>Hash value (data integrity).<\/td>HMAC value (data integrity + authenticity).<\/td><\/tr>
          Use Case<\/td>Data integrity checks, unique IDs.<\/td>Authentication, secure message verification.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
          \n
          Password Hashing Functions<\/strong><\/h3>\n\n\n\n
          For even better password security, pgcrypto provides specialized functions:<\/strong><\/p>\n\n\n\n
          crypt()<\/code> and gen_salt()<\/code><\/strong><\/h3>\n\n\n\n
          The crypt() <\/code>function is specifically designed for password hashing with built-in salting:<\/p>\n\n\n\n
          The gen_salt(<\/code>) function creates a random salt with the specified algorithm:<\/p>\n\n\n\n
            \n
          • ‘bf’ – Blowfish (most secure, recommended)<\/li>\n\n\n\n
          • ‘md5’ – MD5 (legacy)<\/li>\n\n\n\n
          • ‘xdes’ – Extended DES (legacy)<\/li>\n<\/ul>\n\n\n\n
            To verify a password hashed with crypt()<\/code>:<\/p>\n<\/div>\n\n\n\n
            SELECT crypt('password', gen_salt('bf'));\n                            crypt                             \n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n $2a$06$qJwxmgWJCDU8PavtS.AQDuhD87jP.OEspdfcDRyEZ8Asn4Q91qhYm\n(1 row)\nTime: 15.687 ms\n\nSELECT crypt('password', gen_salt('bf'));\n                            crypt                             \n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n $2a$06$68CfObeY0j0oZBAH1eq2w.YRmibYKyjfUsAEkL6x\/vZVd7Lm.8YhW\n(1 row)\nTime: 5.089 ms<\/code><\/pre>\n\n\n\n
            Note on Salt and Hash Generation:<\/strong><\/h3>\n\n\n\n
            The crypt()<\/code> function generates a new hash each time it’s called, even if the same password is provided. This is because the gen_salt('bf')<\/code> function generates a random salt each time, ensuring that the hash values differ, which improves security by preventing identical hashes for identical passwords<\/p>\n\n\n\n
            CREATE TABLE users (\n    user_id SERIAL PRIMARY KEY,\n    username VARCHAR(100),\n    password TEXT\n);\nCREATE TABLE\nTime: 26.376 ms\n\nINSERT INTO users (username, password)\nVALUES ('sachin', crypt('sachin10@', gen_salt('bf', 10)));\nINSERT 0 1\nTime: 50.064 ms\n\nSELECT * FROM users;\n user_id \u2502 username \u2502                           password                           \n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n       1 \u2502 sachin   \u2502 $2a$10$IJL\/OG8ycYrRK5hzvnBhM.RbI4\/013WJQ2\/rMQdTu7.rZC.j0UtKe\n(1 row)\nTime: 0.384 ms\n\nSELECT (password = crypt('sachin10@', password)) AS password_matches\nFROM users\nWHERE username = 'sachin';\n password_matches \n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n t\n(1 row)\nTime: 46.207 ms<\/code><\/pre>\n\n\n\n
            Salt and Hash Generation<\/strong><\/h3>\n\n\n\n
            The crypt()<\/code><\/strong> function generates a new hash every time it is called, even if the same password is used. This is because gen_salt()<\/code><\/strong> generates a random salt each time. The use of salt prevents attackers from using precomputed rainbow tables<\/strong> to crack passwords.<\/p>\n\n\n\n
            Security Best Practices<\/strong><\/h3>\n\n\n\n
            \n
              \n
            1. Use strong algorithms<\/strong>: Prefer SHA-256 or above for general hashing, and Blowfish (bf) for password hashing<\/li>\n\n\n\n
            2. Avoid deprecated algorithms<\/strong>: MD5 and SHA-1 are considered cryptographically weak<\/li>\n\n\n\n
            3. Always use salts for passwords<\/strong>: This prevents rainbow table attacks<\/li>\n\n\n\n
            4. Use appropriate key lengths<\/strong>: Longer keys provide better security for HMAC<\/li>\n\n\n\n
            5. Rotate secret keys periodically<\/strong>: This limits the impact of potential key compromise<\/li>\n<\/ol>\n<\/div>\n\n\n\n
              Feature<\/strong><\/td>General Hashing<\/strong><\/td>Password Hashing<\/strong><\/td><\/tr>
              Purpose<\/td>Create unique fingerprints for data.<\/td>Securely store and verify user passwords.<\/td><\/tr>
              Output<\/td>Fixed-size hash value.<\/td>Fixed-size hash value, often with salt.<\/td><\/tr>
              Common Use Cases<\/td>Data integrity, unique identifiers.<\/td>User authentication, password storage.<\/td><\/tr>
              Algorithms<\/td>MD5, SHA-1 (Weak, hence no longer recommended)
              SHA-256, SHA-512.(Recommended)<\/td>              		Blowfish (bf), bcrypt, etc.<\/td><\/tr>
              Salting<\/td>Typically not used.<\/td>Always used to enhance security.<\/td><\/tr>
              Verification<\/td>Not applicable.<\/td>Includes a verification step for login.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
              Conclusion<\/strong><\/p>\n\n\n\n
              In this blog, we explored how the pgcrypto<\/strong> extension in PostgreSQL can be used for a variety of cryptographic functions such as hashing<\/strong>, HMAC<\/strong>, and password hashing<\/strong>. These functions are crucial for ensuring data integrity, authenticating users, and securely storing sensitive information like passwords. By following best practices\u2014like using strong hashing algorithms, salting passwords, and securely managing secret keys\u2014you can significantly enhance the security of your PostgreSQL database. Whether you are securing user passwords or verifying data integrity, pgcrypto<\/strong> provides powerful tools to help protect your data from unauthorized access.<\/p>\n","protected":false},"excerpt":{"rendered":"
              In the previous blog post, we discussed how to use pgcrypto for encryption and decryption to secure sensitive data within […]<\/p>\n","protected":false},"author":20,"featured_media":7457,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[23,42,89],"tags":[135,37,24,44,26,33],"class_list":["post-7441","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql-14","category-postgresql-15","category-postgresql-16","tag-pgcrypto","tag-postgres","tag-postgres14","tag-postgres15","tag-postgresql","tag-security"],"yoast_head":"\nEnhancing data security with PostgreSQL\u2019s pgcrypto Extension Part - 2 - OpenSource DB<\/title>\n<meta name=\"robots\" content=\"noindex, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part - 2 - OpenSource DB\" \/>\n<meta property=\"og:description\" content=\"In the previous blog post, we discussed how to use pgcrypto for encryption and decryption to secure sensitive data within […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/\" \/>\n<meta property=\"og:site_name\" content=\"OpenSource DB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/people\/OpenSource-DB\/100072970755470\/\" \/>\n<meta property=\"article:published_time\" content=\"2025-03-19T11:15:31+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-03-19T11:15:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1800\" \/>\n\t<meta property=\"og:image:height\" content=\"945\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Lokesh Mandyam\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@opensource_db\" \/>\n<meta name=\"twitter:site\" content=\"@opensource_db\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Lokesh Mandyam\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/\"},\"author\":{\"name\":\"Lokesh Mandyam\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/df72407df12174ffefa34535b1120929\"},\"headline\":\"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part – 2\",\"datePublished\":\"2025-03-19T11:15:31+00:00\",\"dateModified\":\"2025-03-19T11:15:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/\"},\"wordCount\":1033,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#organization\"},\"image\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png\",\"keywords\":[\"pgcrypto\",\"postgres\",\"postgres14\",\"postgres15\",\"postgresql\",\"security\"],\"articleSection\":[\"PostgreSQL 14\",\"PostgreSQL 15\",\"PostgreSQL 16\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/\",\"name\":\"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part - 2 - OpenSource DB\",\"isPartOf\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png\",\"datePublished\":\"2025-03-19T11:15:31+00:00\",\"dateModified\":\"2025-03-19T11:15:34+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#primaryimage\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png\",\"contentUrl\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png\",\"width\":1800,\"height\":945},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/test.opensource-db.in\/wp1\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part – 2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#website\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/\",\"name\":\"OpenSource DB\",\"description\":\"Your Trusted OpenSource Databases partner\",\"publisher\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/test.opensource-db.in\/wp1\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#organization\",\"name\":\"OPENSOURCE DB PRIVATE LIMITED\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2021\/10\/osdb-logo-tm-2.png\",\"contentUrl\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2021\/10\/osdb-logo-tm-2.png\",\"width\":368,\"height\":120,\"caption\":\"OPENSOURCE DB PRIVATE LIMITED\"},\"image\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/people\/OpenSource-DB\/100072970755470\/\",\"https:\/\/x.com\/opensource_db\",\"https:\/\/www.youtube.com\/channel\/UCmTI5h\",\"https:\/\/www.linkedin.com\/company\/opensource-db\",\"https:\/\/www.instagram.com\/opensource_db\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/df72407df12174ffefa34535b1120929\",\"name\":\"Lokesh Mandyam\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ed40b2db95ad8d925a250fc4cff81508442f1b7187fb607bb790a3c801fb2072?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ed40b2db95ad8d925a250fc4cff81508442f1b7187fb607bb790a3c801fb2072?s=96&d=mm&r=g\",\"caption\":\"Lokesh Mandyam\"},\"url\":\"https:\/\/test.opensource-db.in\/wp1\/author\/lokesh\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part - 2 - OpenSource DB","robots":{"index":"noindex","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"og_locale":"en_US","og_type":"article","og_title":"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part - 2 - OpenSource DB","og_description":"In the previous blog post, we discussed how to use pgcrypto for encryption and decryption to secure sensitive data within […]","og_url":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/","og_site_name":"OpenSource DB","article_publisher":"https:\/\/www.facebook.com\/people\/OpenSource-DB\/100072970755470\/","article_published_time":"2025-03-19T11:15:31+00:00","article_modified_time":"2025-03-19T11:15:34+00:00","og_image":[{"width":1800,"height":945,"url":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png","type":"image\/png"}],"author":"Lokesh Mandyam","twitter_card":"summary_large_image","twitter_creator":"@opensource_db","twitter_site":"@opensource_db","twitter_misc":{"Written by":"Lokesh Mandyam","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#article","isPartOf":{"@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/"},"author":{"name":"Lokesh Mandyam","@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/df72407df12174ffefa34535b1120929"},"headline":"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part – 2","datePublished":"2025-03-19T11:15:31+00:00","dateModified":"2025-03-19T11:15:34+00:00","mainEntityOfPage":{"@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/"},"wordCount":1033,"commentCount":0,"publisher":{"@id":"https:\/\/test.opensource-db.in\/wp1\/#organization"},"image":{"@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#primaryimage"},"thumbnailUrl":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png","keywords":["pgcrypto","postgres","postgres14","postgres15","postgresql","security"],"articleSection":["PostgreSQL 14","PostgreSQL 15","PostgreSQL 16"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/","url":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/","name":"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part - 2 - OpenSource DB","isPartOf":{"@id":"https:\/\/test.opensource-db.in\/wp1\/#website"},"primaryImageOfPage":{"@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#primaryimage"},"image":{"@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#primaryimage"},"thumbnailUrl":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png","datePublished":"2025-03-19T11:15:31+00:00","dateModified":"2025-03-19T11:15:34+00:00","breadcrumb":{"@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#primaryimage","url":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png","contentUrl":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png","width":1800,"height":945},{"@type":"BreadcrumbList","@id":"https:\/\/test.opensource-db.in\/wp1\/enhancing-data-security-with-postgresqls-pgcrypto-extension-part-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/test.opensource-db.in\/wp1\/"},{"@type":"ListItem","position":2,"name":"Enhancing data security with PostgreSQL\u2019s pgcrypto Extension Part – 2"}]},{"@type":"WebSite","@id":"https:\/\/test.opensource-db.in\/wp1\/#website","url":"https:\/\/test.opensource-db.in\/wp1\/","name":"OpenSource DB","description":"Your Trusted OpenSource Databases partner","publisher":{"@id":"https:\/\/test.opensource-db.in\/wp1\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/test.opensource-db.in\/wp1\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/test.opensource-db.in\/wp1\/#organization","name":"OPENSOURCE DB PRIVATE LIMITED","url":"https:\/\/test.opensource-db.in\/wp1\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/logo\/image\/","url":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2021\/10\/osdb-logo-tm-2.png","contentUrl":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2021\/10\/osdb-logo-tm-2.png","width":368,"height":120,"caption":"OPENSOURCE DB PRIVATE LIMITED"},"image":{"@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/people\/OpenSource-DB\/100072970755470\/","https:\/\/x.com\/opensource_db","https:\/\/www.youtube.com\/channel\/UCmTI5h","https:\/\/www.linkedin.com\/company\/opensource-db","https:\/\/www.instagram.com\/opensource_db\/"]},{"@type":"Person","@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/df72407df12174ffefa34535b1120929","name":"Lokesh Mandyam","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ed40b2db95ad8d925a250fc4cff81508442f1b7187fb607bb790a3c801fb2072?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ed40b2db95ad8d925a250fc4cff81508442f1b7187fb607bb790a3c801fb2072?s=96&d=mm&r=g","caption":"Lokesh Mandyam"},"url":"https:\/\/test.opensource-db.in\/wp1\/author\/lokesh\/"}]}},"rttpg_featured_image_url":{"full":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png",1800,945,false],"landscape":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png",1800,945,false],"portraits":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png",1800,945,false],"thumbnail":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3-150x150.png",150,150,true],"medium":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3-300x158.png",300,158,true],"large":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3-1024x538.png",1024,538,true],"1536x1536":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3-1536x806.png",1536,806,true],"2048x2048":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png",1800,945,false],"ultp_layout_landscape_large":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png",1200,630,false],"ultp_layout_landscape":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png",870,457,false],"ultp_layout_portrait":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png",600,315,false],"ultp_layout_square":["https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2025\/03\/image-3.png",600,315,false]},"rttpg_author":{"display_name":"Lokesh Mandyam","author_link":"https:\/\/test.opensource-db.in\/wp1\/author\/lokesh\/"},"rttpg_comment":0,"rttpg_category":"<a href=\"https:\/\/test.opensource-db.in\/wp1\/category\/postgres\/postgresql-14\/\" rel=\"category tag\">PostgreSQL 14<\/a> <a href=\"https:\/\/test.opensource-db.in\/wp1\/category\/postgres\/postgresql-15\/\" rel=\"category tag\">PostgreSQL 15<\/a> <a href=\"https:\/\/test.opensource-db.in\/wp1\/category\/postgres\/postgresql-16\/\" rel=\"category tag\">PostgreSQL 16<\/a>","rttpg_excerpt":"In the previous blog post, we discussed how to use pgcrypto for encryption and decryption to secure sensitive data within […]","_links":{"self":[{"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/posts\/7441","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/users\/20"}],"replies":[{"embeddable":true,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/comments?post=7441"}],"version-history":[{"count":8,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/posts\/7441\/revisions"}],"predecessor-version":[{"id":7459,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/posts\/7441\/revisions\/7459"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/media\/7457"}],"wp:attachment":[{"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/media?parent=7441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/categories?post=7441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/tags?post=7441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}