\df *crypt
in psql reveals the argument types of the pgcrypto encrypt
and decrypt
functions (as do the PgCrypto docs):
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------------+------------------+--------------------------+--------
...
public | decrypt | bytea | bytea, bytea, text | normal
public | encrypt | bytea | bytea, bytea, text | normal
...
so both the encrypt
and decrypt
functions expect the key to be bytea
. As per the error message, "you might need to add explicit type casts".
However, it works fine here on Pg 9.1, so I suspect there's more to it than you've shown. Perhaps you have another function also named encrypt
with three arguments?
Here's how it works on a clean Pg 9.1:
regress=# create table demo(pw bytea);
CREATE TABLE
regress=# insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );
INSERT 0 1
regress=# select decrypt(pw, 'key', 'aes') FROM demo;
decrypt
------------
\x64617461
(1 row)
regress=# select convert_from(decrypt(pw, 'key', 'aes'), 'utf-8') FROM demo;
convert_from
--------------
data
(1 row)
Awooga! Awooga! Key exposure risk, extreme admin caution required!
BTW, please think carefully about whether PgCrypto is really the right choice. Keys in your queries can be revealed in pg_stat_activity
and the system logs via log_statement
or via crypto statements that fail with an error. IMO it's frequently better to do crypto in the application.
Witness this session, with client_min_messages
enabled so you can see what'd appear in the logs:
regress# SET client_min_messages = 'DEBUG'; SET log_statement = 'all';
regress=# select decrypt(pw, 'key', 'aes') from demo;
LOG: statement: select decrypt(pw, 'key', 'aes') from demo;
LOG: duration: 0.710 ms
decrypt
------------
\x64617461
(1 row)
Whoops, key possibly exposed in the logs if log_min_messages
is low enough. It's now on the server's storage, along with the encrypted data. Fail. Same issue without log_statement
if an error occurs to cause the statement to get logged, or possibly if auto_explain
is enabled.
Exposure via pg_stat_activity
is also possible.. Open two sessions, and:
- S1:
BEGIN;
- S1:
LOCK TABLE demo;
- S2:
select decrypt(pw, 'key', 'aes') from demo;
- S1:
select * from pg_stat_activity where current_query ILIKE '%decrypt%' AND procpid <> pg_backend_pid();
Whoops! There goes the key again. It can be reproduced without the LOCK TABLE
by an unprivileged attacker, it's just harder to time it right. The attack via pg_stat_activity
can be avoided by revoking access to pg_stat_activity
from public
, but it just goes to show that it might not be best to send your key to the DB unless you know your app is the only thing ever accessing it. Even then, I don't like to.
If it's passwords, should you store them at all?
Furthermore, if you're storing passwords, don't two-way encrypt them; if at all possible salt passwords then hash them and store the result. You usually don't need to be able to recover the password cleartext, only confirm that the stored hash matches the password the user sends you to log in when it's hashed with the same salt.
If it's auth, let someone else do it for you
Even better, don't store the password at all, authenticate against LDAP, SASL, Active Directory, an OAuth or OpenID provider, or some other external system that's already designed and working.
Resources
and lots more.
Best Answer
What you are trying to do won't work because
pgcrypto
'sdecrypt
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.