I have written a database-based authentication framework for webapps I am planning to program in the future.

The framework is implemented by using the plpgsql-language of a PostgreSQL-Database and therefore the extension pgcrypto is used to encrypt sensitive information.

If the user of a webapp authenticates (so the webserver fires up sth.) like

select authenticate('[email protected]', 'login-token')

the database returns

False, Invalid user / login token


True, Successfully logged in, session-id

The session-id is transmitted to the webserver in cleartext, so a cookie can be created, while it is saved in the database by applying a hashing algorithm, which comes with the pgcrypto-extension. This is a kind of bcrypt-implementation (the PostgreSQL-Documentation is kind of vague). The session-id is combined with an unique salt (one per session-id).

So in the session-table you find sth. like

|primary key|user-id|bcrypt(session-id)|other information

The question is about the function which is used to check if the session-id is valid and which user it is about. This function is fired, when the user presents a session-id to the webserver, who is in turn to validate this session-id.

select get_user_by_session_id(session-id)

If there is no additional identifier the select-part has the following problem:

For each row in the session-table the hash-function must be applied to the given session-id.

This works like

  • select a row of session-table
  • read the salt and hash the user-given session-id
  • compare the result with the hash of the actual row
  • repeat until found a match (return True) or the end of table is reached without any match (return False)

This is kind of slow (slower if more sessions are there).

So the idea is to use an identifier, which is unsensitive to do the following (pseudocode)

  • select get_user_by_session_id(user-given-dentifier, session_id)
  • select hash from session-table where identifier=user-given-identifier
  • check if hash_function(session_id) = hash

This has the advantage, that only one crypto-operation is necessary, instead of one for each row in the session table until the valid one is found.

In the current design there are two possible identifiers I could give to the user.

  • A static unique member_key which is generated at the registration for each user.
  • The salt used for encrypt / hash the session-id

I would go for the salt, because I wanted to use the member_key for identification purposes (support calls) or something else. I don't see any problems with this, because of the following reasons:

  • in general the salt must not be encrypted and can be saved in plain text
  • only the webserver and the user himself sees the salt (webserver during transmit, user in the cookie)
  • the attacker only can get this salt by intercepting the connection - if he does this, he also get the session-id in plaintext

Do I miss anything? Does the static member_key have any advantages over the salt?

  • I'm having trouble following what you are doing. You mention using a hash function on lookup but don't actually say where you are using a hash funciton. The only algorithm you mention is Blowfish which is an encryption algorithm. And why are you salting the session IDs at all? Oct 17, 2015 at 20:33
  • @NeilSmithline I added some information. The used hash algorithm is based on the blowfish cipher and should be bcrypt. The postgresql-wiki is kind of vague about the explicit implementation.
    – Markus
    Oct 18, 2015 at 6:55
  • What are you trying to achieve with this method? Oct 20, 2015 at 16:51
  • I don't want to save the session-id unencrypted in the database. As the session-id is a sensitive information I just save a hash of it in the database. So potential data theft or a "lost" backup doesn't enable the attacker to get in touch with a valid (possible due to persistent validity or long expiration dates) session-id to impersonate the user. This attack vector is probably theoretical (because the cookie which is presented to the webserver is also signed, so that the attacker must still break this), but I don't see an disadvantage against this "defense-in-depth"-idea. Do I miss anything?
    – Markus
    Oct 20, 2015 at 18:36

1 Answer 1


I replaced the primary-key of the Users-Table with an uuid instead of a big serial and used this as insensitive information.

The session-id at the client looks like ||.

With this I could minimized the session-lookup from > 1 sec to about 100ms.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .