Re: Users and unique identifyers - Mailing list pgsql-general

> The system administrator will need to be able to see it, and will need
> remote
> access to it.
>
> The security requirements are extremely high, the standard in this case
> is set
> by the state, so encryption will be a must.

    I hope you remark that these two are incompatible.

    * For instance 'extremely high security', for me, in the case of credit
card numbers would be either one of these two solutions :
    1- not having them at all (the user does its transaction directly with
the bank)
    2- storing them encrypted with the bank's public key, so noone can read
them, even you, and only the bank can decipher them with the assorted
private key. I dunno if banks are evolved enough to do that. Some I
checked, gave online payment confirmations to my website, which were not
even cryptographically signed...

    In these two cases, the existing information is not compromised even if
the machine is.

    1* Now we have 'high security' :
    Information stored, encrypted with a key that is different for each user.
That can be derived from his password. So if the machine is compromised,
the hacker can still dump the session info for users logged at that time,
and recover their keys, but that's it.

    2* Medium, made high security with a trick
    If you, the administrator, can view the info, then surely a hacker can.
For instance if you store all the information encrypted with a single key,
well, this key has to be on the server somewhere, so you have to define
how hard it is to rip it, and in reality, it is little more secure than no
encryption at all. Should it be in the webserver scripts ? In a database
function ? The best place for it is in your head, so :

    You can sidestep this thing by storing the information twice :
    - encrypted with a key unique to each user (like some password hash)
    - encrypted with a global key that only you know and that is not on the
server

    Thus, when you're not logged in, your key is not available on the server,
so a hacker would only get the active sessions. When you log in, your key
musi be stored somewhere, but at least it's not in a variable named 'key'
in a function named 'decrypt'.
    Public/private keys should be used so that users can be inserted without
you having to insert your secret key !

    3* Medium security
    Encrypting the stuff in the database with a global key.
    In that case, the key can either be in the database, or in the client.

    Key in database :
    SELECT get_decrypted_user_name( user_id );
    The key will be in that function, thus the db admin (and anyone rooting
the machine) has it.
    Also here, the DB trusts the client :
    You write your app in PHP so it has some SQL injection holes. Someone
injects "SELECT user_id, get_decrypted_user_name( user_id ) FROM users".
Boom.

    Key in clien1 :
    SELECT get_decrypted_user_name( user_id, key );
    - This one will nicely dump the key in the logs or even on-screen when
some error occurs (or someone turns on statement logging)...
    Of course in cases 1) and 2) above, the same remark applies, never put
your key in a request, it will end up in a log.

    Key in client v2 :
    SELECT crypted_username ...
    decrypt in client

    Some lowly script xploit can make it public.

    You see that this is not really better than no encryption. Of course
anyone xploiting the webserver and getting a shell will have full access
to the full secret information, which was not the case in 1) and 2) above.

    From Michael Fuhr :

SELECT * FROM users;
  id |   name
----+----------
   1 | ********
   2 | ********
(2 rows)

SELECT * FROM users_private;
ERROR:  permission denied for relation users_private

    Hum, did you notice that in this case, the users can't view their own
usernames ?
    Well, does it really matter... after all you can store it in their
session, they used it for logging in !


    What I think you should try :

    Store the usernames in the database, encrypted with a public key. For an
added sense of security, make the crypted_username column hidden (with a
view) and only offer a function verify_user(  ) which is used to log in
users, but cant be used to dump the table.

    When someone logs in, encrypt their username and password, and check it
in the database.

    When the admin logs in, his password is used to decrypt the private key
which can decrypt the usernames.

    Now, how to avoid that things end up in the log which shouldn't have ?
    This could happen at login :

    SELECT verify_user( crypted_username, crypted_password )    -> returns
user_id or NULL
    -> BAD !
    Unless you use a prepared statement and verify there is no way it can end
up in a trace somewhere.

    To protect yourself from log leak, you can encrypt these two parameters
again in the client and decrypt them in the server ; again with a pair of
public/private keys, adding a salt generated by a sequence, and used only
once. This way the two crypted strings will be used only once, and more
important, be usable only once in this transaction :

    - function get_key( bool increment )
    increments (or not) a sequence and returns a throw-away secret key
generated from it.


    in the client :
    SELECT get_key( true )
    encrypt crypted_username, crypted_password with this pubkey ->
crypted_username2, crypted_password2
    SELECT verify_user( crypted_username2, crypted_password2 )
    which in turn does get_key(false) which returns the same key as the
client had, and decrypts the data.


    I hope this is useful to you, and don't think you have 'really high'
security when anyone who roots your server can read all the secret info in
it.