Re: pgcrypto and database encryption - Mailing list pgsql-admin

Silvana Di Martino wrote:
> 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