title: Decrypt data in a PostgreSQL query which was encrypted with C#
body: <p>There is some code we have in C# which encrypts and decrypts data for storing in a postgresql database. The code for decrypting is as follows:</p>
  <pre><code>public string Decrypt(string val)
  {
      var sb = new StringBuilder();
      string[] split = val.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
  
      foreach (string s in split)
      {
          sb.Append(Encoding.UTF8.GetString(Decode(Convert.FromBase64String(s))));
          sb.Append(&quot; &quot;);
      }
      sb.Remove(sb.Length - 1, 1); // Remove last space
  
      return sb.ToString();
  }
  
  private static byte[] Decode(byte[] encodedData)
  {
      var symmetricAlgorithm = Aes.Create();
      symmetricAlgorithm.Key = HexToByteArray(&quot;&lt;aes key&gt;&quot;);
  
      var hashAlgorithm = new HMACSHA256();
      hashAlgorithm.Key = HexToByteArray(&quot;&lt;hash key&gt;&quot;);
  
      var iv = new byte[symmetricAlgorithm.BlockSize / 8];
      var signature = new byte[hashAlgorithm.HashSize / 8];
      var data = new byte[encodedData.Length - iv.Length - signature.Length];
  
      Array.Copy(encodedData, 0, iv, 0, iv.Length);
      Array.Copy(encodedData, iv.Length, data, 0, data.Length);
      Array.Copy(encodedData, iv.Length + data.Length, signature, 0, signature.Length);
  
      // validate the signature
      byte[] mac = hashAlgorithm.ComputeHash(iv.Concat(data).ToArray());
  
      if (!mac.SequenceEqual(signature))
      {
          // message has been tampered
          throw new ArgumentException();
      }
  
      symmetricAlgorithm.IV = iv;
  
      using (var memoryStream = new MemoryStream())
      {
          using (var cryptoStream = new CryptoStream(memoryStream, symmetricAlgorithm.CreateDecryptor(), CryptoStreamMode.Write))
          {
              cryptoStream.Write(data, 0, data.Length);
              cryptoStream.FlushFinalBlock();
          }
          return memoryStream.ToArray();
      }
  }
  
  private static byte[] HexToByteArray(string hex)
  {
      return Enumerable.Range(0, hex.Length).
          Where(x =&gt; 0 == x % 2).
          Select(x =&gt; Convert.ToByte(hex.Substring(x, 2), 16)).
          ToArray();
  }
  </code></pre>
  <p>The requirement I have now is that we want to be able to decrypt within an SQL query.. I have discovered the <code>PGP_SYM_DECRYPT</code> function, as well as some others like <code>Encode()</code>/<code>Decode()</code> for base64 strings and a <code>decrypt_iv()</code> function as well. Only I am uncertain how to use these to decrypt data.</p>
  <p>Any crypto experts that could help me out here?</p>
  <p>Alternatively, is there some equivalent of MSSQL's CLR functions for Postgres?</p>