Active/Active denotes two live datastores that can be simultaneously accessed by the application(s) in question. SQL Server does not have a solution for this, including AlwaysOn Availability Groups, which at best will only provide you with additional readable replicas. Outside of Oracle RAC, there isn't a solution for this.
What you are trying to do won't work because pgcrypto
's decrypt
function is not going to receive the KMS key you encrypted the data with.
Essentially, when you set up KMS it generates a key pair consisting of a public key and a private key. These are generated on a Hardware Security Module (HSM) and the public key is something you can query the KMS api for. The private key remains on the HSM and never leaves it. You can hand data to it for decryption using the api, but you cannot receive the key; the whole point of using KMS is that the private key remains secret, even from you.
What setting up KMS on RDS postgres does get you is that your tablespace and your backups are encrypted. If you also want to do row level encryption over and above the data at rest protections that are provided when you create your database with the --storage-encrypted True
flag set; you will need to manage another set of keys for that.
The right way to do that is to generate symmetric encryption keys that are then encrypted with a KMS master key. Your application would use the API to decrypt those keys on start and would retain them in memory, passing them to the database with every query that needed decrypt operations.
Indexing an encrypted column is impossible by definition since the cryptext should appear random to anyone who does not have the key. The query you posted above would be slow even if you had the key to look at the column since it would have to perform the decryption operation for every row. Generally speaking if you are using row level encryption you want to be encrypting dependent attributes not key attributes; since selection, sorting and searching all require access to the plaintext of the values.
Best Answer
As per sql server blog here On SQL Server side, it is supported to use a custom key store provider for Always Encrypted, but the implementation/support of the custom key store provider comes from the service provider itself, which in this case is the AWS KMS.
For further your ref here