Skip site navigation (1) Skip section navigation (2)

Re: pgcrypto and database encryption

From: Joe Conway <mail(at)joeconway(dot)com>
To: Silvana Di Martino <silvanadimartino(at)tin(dot)it>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: pgcrypto and database encryption
Date: 2004-03-07 18:19:50
Message-ID: 404B67C6.6030106@joeconway.com (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-admin by date

Next:From: JoshDate: 2004-03-07 19:46:02
Subject: running pg_dumpall as superuser fails
Previous:From: Stephan SzaboDate: 2004-03-07 17:40:58
Subject: Re: pgcrypto and database encryption

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group