4) What could actually solve our problem is something like the following scenario. Imagine that postmaster (or pg_ctrl) could accept a new CL parameter called "pw". This parameter would contain a sequence of comma-separated databasename/encryption-password pairs. I mean, something like this:
postmaster -i -pw=postnuke:"arriba!",phpnuke:"blade runner"
But you mentioned earlier that the DBA cannot know the passwords, so who is going to type all that in?
Does the law require protection from a determined DBA, or just casual viewing by the DBA? *If* it's the latter, you could do something like this:
1. Export an environment variable , say PGMASTERPASS containing a hex encoded password, something like:
PGMASTERPASS=0102000304 pg_ctl start
2. Use a C function to grab the value of the environment variable -- one exists in PL/R already. You could write your own based on that.
3. Combine the master password with other information to make it sufficiently unique as a key for your various purposes. For example, you might use the md5 hashed password for the current user from pg_shadow. This combining should be done securely -- I'd recommend taking the HMAC of the user password using the master as the key. The result of the HMAC becomes your data encryption/decryption key.
5) There is also a problem related to what pgcrypto can encrypt and what it cannot. For example: pgcrypto encrypt functions cannot be applied to DATE and TIME data types because it would mess up them and make them unacceptable by the RDBMS engine. We would need specific encrypted data types like ENCDATA and ENCTIME to handle these cases.
Just use bytea for the encrypted stuff, and write plpgsql functions to convert the bytea output of the decrypt function back to its native datatype. Here's a more-or-less complete example of what I mean by all this:
--8<--------------------------------------------------------------------
create or replace function text2bytea(text) returns bytea as ' begin return $1; end; ' language plpgsql;
create or replace function timestamp2bytea(timestamp with time zone) returns bytea as '
begin
return $1;
end;
' language plpgsql;
create or replace function encrypt_timestamp(timestamp with time zone) returns bytea as '
declare
v_in alias for $1;
v_masterpass bytea;
v_userpass bytea;
v_key bytea;
v_data bytea;
v_iv bytea; --skip for simplicity at the moment
begin
select into v_masterpass decode(value,''hex'') from plr_environ() where name=''PGMASTERPASS'';
select into v_userpass text2bytea(substr(passwd,4)) from pg_shadow where usename = current_user;
v_key := hmac(v_userpass, v_masterpass, ''sha1'');
v_data := timestamp2bytea(v_in);
return encrypt(v_data, v_key, ''aes''); end; ' language plpgsql;
create or replace function decrypt_timestamp(bytea) returns timestamp with time zone as '
declare
v_in alias for $1;
v_masterpass bytea;
v_userpass bytea;
v_key bytea;
v_data bytea;
v_iv bytea; --skip for simplicity at the moment
begin
select into v_masterpass decode(value,''hex'') from plr_environ() where name=''PGMASTERPASS'';
select into v_userpass text2bytea(substr(passwd,4)) from pg_shadow where usename = current_user;
v_key := hmac(v_userpass, v_masterpass, ''sha1'');
v_data := decrypt(v_in, v_key, ''aes'');
return v_data; end; ' language plpgsql;
-- here you can see the master password
regression=# select decode(value,'hex') from plr_environ() where name='PGMASTERPASS';
decode
----------------------
\001\002\000\003\004
(1 row)
-- here is an encrypted timestamp
regression=# select encrypt_timestamp(now());
encrypt_timestamp
--------------------------------------------------------------------------------------------------
\340\333*\0221r\177\022e\011_]X \374\302Y\201\364\264\362\351e\331\006\266\\\331\236\300\256\335
(1 row)
-- and to prove that it works, this example goes full circle
regression=# select decrypt_timestamp(encrypt_timestamp(now()));
decrypt_timestamp
-------------------------------
2004-03-07 10:16:56.192193-08
(1 row)--8<--------------------------------------------------------------------
As I said above (and others in this thread too), if the DBA (or anyone with root access on the database server) is sufficiently determined, they can get around this scheme and view whatever data they want. If you're really concerned about that scenario, the data should be encrypted in your application before it ever gets sent to the database, using a key that is unavailable on the database server.
HTH,
Joe
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
