Going on with my evaluation of pgcrypto as a possible solution for our (EU)
privacy law problems, I have to report the following facts:
1) pgcrypto (understandably) supplies just a small collections of server-side
functions, not a general-purpose database-encryption system.
2) This means that pgcrypto does not supply you with any password management
tool. All pgcrypto functions expect to receive the password from the calling
SQL SELECT code as a parameter.
3) This means that you have to manage by yourself all the details of the
password one-time entry at the RDBMS start-up time, its storing (in RAM,
only) and its passage to the SQL SELECTs. IMHO, this is still far to satisfy
our need for a standard and reliable solution to our "privacy law and DB
encryption" problem.
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"
Imagine that postmaster stores these passwords in memory (and only in memory)
as database-specific and database-wide string variables. Now, pgcrypto
functions could read the required password directly from memory (like an
environment variable).
In this way, we could have an encrypted database and a simple way to manage
its passwords.
Any comment?
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.
Any comment?
PS: I apologize for bothering americans with these all-EU details but it looks
like this mailing list is the only one I can use for communicating with
others EU pgsql-admins affected by this problem.
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
[EMAIL PROTECTED]
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
