Password Encryption between Java Swing app and Postgres db

Question

I'm building a system that has a Java Swing front end accessed a postgres database. Prior to discovering Jasypt this week, I had originally planned to use Postgres' own encryption mechanism. It worked fine, but I also now wanted the passwords over the network to be encrypted, hence why I turned to Jasypt.

Problem is, I need a fixed password to be entered into my Postgres stored function. I.e. If the input password is 'aaa' then any other inputed password into the Postgres stored function (other than 'aaa') will not match.

Is there a way to get these two encryption mechanisms to work in tandem together or do I have to dump Postgres'?

My user table:

CREATE TABLE "user"
(
  id serial NOT NULL,
  cryptpwd text NOT NULL,
  md5pwd text NOT NULL,
  ...
)

Encrypting password:

cryptedPassword = crypt(passwordIn, gen_salt('md5'));
md5Password = md5(passwordIn);
INSERT INTO "user"(username, cryptpwd, md5pwd, ...)
      VALUES (usernameIn, cryptedPassword, md5Password, ...);

Decrypting password:

select ..... from "user" .... where username = usernameIn and cryptpwd = crypt(passwordIn, cryptpwd);

If I cannot get the two of them to work together then I would have to dump Postgres' mechanism as I need to have encryption over the network.

Also, with regards to the database connection string and database username and password (not using any framework ... plain old jdbc connection hopefully with SSL - yet to implement), I don't think I'll be able to use Jasypt because I'd need to decrypt it at database level. Would SSL alone be sufficient for this case?

Thanks.

score 0 · Answer 1 · answered Apr 04 '13 at 13:04

I think SSL alone, on every piece of the path, would be sufficient. In LedgerSMB (although we are Perl-based) we do something different and rely on SSL protected links between servers and between servers and clients. There are a few things to think about with your approach though.

We actually pass the db username and password to the middleware from the client in re-usable format (plain text) over an SSL connection, and then use another SSL connection to log into PostgreSQL to authenticate this way. This works fine, but the problem areas we face are somewhat similar to the problem areas you will. These include:

Logging. Is it possible passwords will get accidently logged? This is a concern with LedgerSMB and we take what steps we can but a badly configured server or a tampered-with program could log usernames and passwords. In our case this comes primarily on the middleware level, but in your case, query logging could do this too, right?
Is it possible credentials can be re-used unintentionally? We prevent this in a couple of ways, but it is worth considering.

On the whole, we trust SSL. There isn't much to be gained from adding additional encryption beyond that, and key management adds a lot of complexity that is not worth the marginal gains IMO.

Password Encryption between Java Swing app and Postgres db

1 Answers1