\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.
Does that mean that columns should be ordered from most space occupation to least?
No, not necessarily. You can play "column tetris" to minimize padding and thereby save some space. The rule of thumb I gave and you quoted is one simple strategy for basic types that require alignment.
As I mentioned in the quoted answer, you can test the actual storage size (excluding item identifier) with pg_column_size()
on the whole row.
text
and related varchar
and char
types do not require padding, so there is nothing to gain. The same is true for your bytea
columns.
Concerning storage size for:
bytea columns that always have constant 16-byte, 32-byte, or 64-byte lengths
The manual page on bytea
tells us :
Storage Size
1 or 4 bytes plus the actual binary string
That means, the actual space required for a bytea
column of 16-byte, 32-byte, or 64-byte
length is 17 or 20 byte, 33 or 36 byte etc. respectively.
As demonstrated in this SQL Fiddle, a bytea
variable always has an overhead of 4 bytes. When stored in a column, however, it starts out with just 1 byte of overhead and switches to 4 bytes for values of 127 bytes length or more.
24 bytes of overhead are added for the row type.
Another 4 bytes are needed for the item identifier per tuple in the data page. Details in this related answer:
As for alignment requirements of bytea
, per documentation:
Values with single-byte headers aren't aligned on any particular boundary, either.
I would suggest you read that whole chapter - probably a couple of times, it's a tough read.
Best Answer
Be careful with conditional ordering, it can create bad query plans sometimes forcing table scanning. If the filtering and joining clauses, or just the size of the actual data, mean that you have a small number of rows to sort at the end then this is not an issue and something like this will work:
In fact it will work anyway, it might just be inefficient for a large amount of data.
For larger outputs your workaround may be more efficient as it may be able to make better use of indexes for the sorting. Another alternative is to have two procedures, one for each sort, and either call each as needed or have your main procedure call the others depending on the sort order it is passed in the parameter. Depending on how postgres handles cached query plans for procedures this may[†] avoid issues of a cached plan for one case being used for another where it is vastly less efficient.
[†] I'm no expert at all on pg's internals, but single "kitchen sink" procedures and queries with conditional sorts etc. can be a performance killer in SQL Server for this sort of reason.