Reading encrypted value in SQL

[davidkeaveny]

I've got encryption up and running as per your documentation and example code; I can add my data to the database (Postgres 15.2) through EF Core, and see it in the tables in what looks like a base-64 encoded string. What I haven't been able to figure out is how to write raw SQL statements that will allows me to decrypt the value.

If I run something like this:

SELECT client_id, decrypt_iv(decode(database_password, 'base64'), decode('SXlOSWN1YzVzSEVKWkJYbg==', 'base64'), decode('SXlOSWN1YzVzSEVKWkJYbg==', 'base64'), 'aes-cbc/pad:pkcs') AS database_password
FROM clients;

then I get the following error:

[39000] ERROR: decrypt_iv error: Decryption failed

Similarly I can't figure out the correct SQL to insert a value that EF Core will successfully read. I'm a bit new to Postgres, coming from a SQL Server background - any chance you could help me out?

[nbsoftware]

This is how I got it working:

select convert_from(decrypt_iv(decode(database_password, 'base64'), key, iv, 'aes-cbc/pad:pkcs')::bytea, 'UTF-8')::text from clients;

You need to pass the iv as the 2nd argument. In your attempt you seem to pass the key twice

[davidkeaveny]

You need to pass the iv as the 2nd argument. In your attempt you seem to pass the key twice

Silly question, but what's the best way of getting the IV? At the moment it's being generated internally via the AesUtil.IvFixer method and doesn't appear to be exposed anywhere. I mean, I can copy your code into a console app or something that I control, but that feels a bit like overkill!

[nbsoftware]

Well that's something else. In my use case I just needed to be able to query manually, from time to time, so I copied the output of the IV computation and used it just as is. I never used, in the code itself, raw SQL queries that involved encrypted columns. But it should be easy to use or duplicate the AesUtil.IvFixer method Though the first thing you should have is at least a working basic SQL query to be sure what needs to be done.

[davidkeaveny]

Well that's all fair enough, thanks!

Maybe for the future, the AesUtil.PasswordFixer and AesUtil.IvFixer methods could be made public?

[AltairCA]

Sorry for the delayed response, I have mentioned the raw SQL that you can use in this StackOverflow answer