I tried aes-encryption by using following statement:

SELECT encrypt('test', 'key', 'aes');

which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm not sure if that was the right way.

SELECT decrypt(pw, 'key', 'aes') FROM table WHERE ID = 1;

gives me the error

ERROR: function decrypt(bytea, unknown, unknown) does not exist
LINE 1: SELECT decrypt(pw, 'key', 'aes') FROM tabelle WHERE ID = 7; ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Does that really mean that encrypt() is an existing function, but not decrypt()? How else could I retreive aes-encrypted values?

2 Answers 2


\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);
regress=# insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );
regress=# select decrypt(pw, 'key', 'aes') FROM demo;
(1 row)

regress=# select convert_from(decrypt(pw, 'key', 'aes'), 'utf-8') FROM demo;
(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
(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.


and lots more.

  • It's not more than I shown, and I didn't define new functions, its a new installed postgresql. It is quite irritating that your sample and the first select-statement I've posted also not work meanwhile, returning the same error as posted above. Somewhere something went wrong...thanks anyhow for your answer.
    – 32bitfloat
    Sep 16, 2012 at 18:37
  • Try on a newly CREATEd database from template0; eg CREATE DATABASE testdb TEMPLATE template0 then CREATE EXTENSION pgcrypto; and test. See if there's something dodgy in template1. Sep 16, 2012 at 22:38
  • Just a note regarding two-way decryption in the db. I don't think it's always the wrong direction but, it adds complexity and anywhere you deal with this you really have to deal with key management which can be more complicated in the db. Sep 17, 2012 at 7:09
  • Also 100% second the notion that you should NEVER have to decrypt passwords and that hooking into a system maintained by more people is usually a significant win security-wise. Sep 17, 2012 at 7:09
  • 3
    lol, +1 for "Awooga! Awooga!" Mar 24, 2013 at 16:40

After installing pgcrypto extension, to encrypt in AES-256 use:

pgp_sym_encrypt('".$value."', '".$key."', 'compress-algo=1, cipher-algo=aes256')

result field type is bytea. Stored as is in PostgreSQL bytea type field. To decrypt the bytea:

pgp_sym_decrypt(your_table.your_bytea_column , '".$key."', 'compress-algo=1, cipher-algo=aes256')

also bytea fields can be encoded/decoded to/from base64 with encode/decode functions;

encode(your_table.your_bytea_column, 'base64') //result is string
decode(your_table.your_string_column, 'base64') //result is bytea

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.