Secure Signed Cookies with PostgreSQL's pgcrypto

by on

In our last post on pgcrypto, we covered using just PostgreSQL to hash and query passwords for authentication. Once a user is authenticated, you need to keep track of their session using cookies. In this post, we’ll look at how to generate and verify secure signed cookies with just PostgreSQL’s pgcrypto library.

Why do we need to sign cookies?

When a user signs in to a web site, that web site will send a cookie to set in the user’s browser to identify them. When the user presents that cookie on subsequent requests, the web site can look them up and serve them their content. So the cookie serves as proof that the user is who they claim to be.

The simplest possible cookie might look like this:

Set-Cookie: id=123456; Expires=Tue, 19 Apr 2017 16:00:00 UTC

The server simply stores the user’s id in the cookie, so the user can present it back on each request. But this is a terrible idea because it’s very insecure. Imagine that your user ids are autoincrementing primary keys. You can simply increment the id in your cookie and refresh until you have hit a user whose information you want to steal.

Or, what if your user ids are publicly viewable, like as part of links in your site? Someone could grab the id, drop it into their cookie, and they have stolen someone’s identity.

Clearly we need some way to prevent people from tampering with cookies.

Signing with pgcrypto

Here’s the process we’ll follow for user sessions:

  1. When a user signs in, create an entry in a sessions table that will have a random id for that session
  2. When they sign out, delete the entry in the sessions table
  3. If they want to invalidate all sessions, we can delete all entries with their user id

We want to use some random session id instead of the user id so that we never put any identifying information into the cookies.

In addition to the random session id, we want to sign the cookie too. This way, even if a user wants to randomly guess session identifiers, their signature won’t match their identifier without our secret signing key.

Enter pgcrypto’s hmac. hmac is a function that takes some text to sign, a secret key, and a hashing method and creates a hash.

It’s just like a regular hash method (like pgcrypto’s digest) except that since it takes a secret key, only someone who knows the key can make the hash. Let’s see it in action:


=# select hmac('data', 'secret', 'sha256');
-[ RECORD 1 ]------------------------------------------------------------
hmac | \x1b2c16b75bd2a870c114153ccda5bcfca63314bc722fa160d690de133ccbb9db

=# select hmac('data2', 'secret', 'sha256');
-[ RECORD 1 ]------------------------------------------------------------
hmac | \x4641d969bebcd4b9f586cc501217cc1630d51414d4593ebbd2fe0b85997c93f9

=# select hmac('data2', 'wrong secret', 'sha256');
-[ RECORD 1 ]------------------------------------------------------------
hmac | \x68fe12aaeefc74c6fbfdfbc15ebe49a8cccc3bfef97571c934043d85b2b8aae6

You can see the value is dependent on both the data and the secret key value.

So what does signing really mean? It means we’re going to include the signature alongside the payload in some way. The easiest way to do this is to simply stick the data and key together and compare it on the way in and out.

Let’s make our sessions table:

=# CREATE TABLE sessions (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  key text not null default gen_salt('md5')

We have a random id to identify the session with, we have our user_id (normally this would be a foreign key into the users table), and we have a session key made from gen_salt. This uses pgcrypto’s gen_salt function to create a random salt for a hash. This will serve as our secret for hmac. The cool thing about using a secret on each row is that even if someone discovers their own secret, they won’t discover other users’ secrets. Also, it’s nicer than having one global secret in our whole app we might accidentally leak.

Now we can add a record to use:

=# INSERT INTO sessions (user_id) VALUES (gen_random_uuid());

-[ RECORD 1 ]---------------------------------
id      | 38897a58-65d5-484b-9ca4-eac223a64702
user_id | 863ec703-84d6-4eee-930e-3b0691f25314
key     | $1$cr./pHpB

Now what we can do is select our cookie by using the session id plus a signature using the salt:

  id || '-' || encode(hmac(id::text, key, 'sha256'), 'hex')
) as cookie FROM sessions; 

-[ RECORD 1 ]-------------------------------------------------------------------------------------------------
cookie | 38897a58-65d5-484b-9ca4-eac223a64702-1c715bfb42d8156a684f7817f9a6891873d4667d3e0df29e8d9b971d1b691a07

This cookie has the session id in front, then a -, then the hex-encoded hmac signature of the id with the secret value (that will never be known to the user) hashed with sha256.

Now we can look up the session when the cookie is provided:

=# SELECT user_id FROM sessions WHERE (
  id || '-' || encode(hmac(id::text, key, 'sha256'), 'hex')
) = '38897a58-65d5-484b-9ca4-eac223a64702-1c715bfb42d8156a684f7817f9a6891873d4667d3e0df29e8d9b971d1b691a07';
-[ RECORD 1 ]----------------------------
user_id | 863ec703-84d6-4eee-930e-3b0691f25314

Now if you were to modify the session id but not the signature, it wouldn’t match:

=# SELECT user_id FROM sessions WHERE (
  id || '-' || encode(hmac(id::text, key, 'sha256'), 'hex')
) = 'aaaaaaaa-65d5-484b-9ca4-eac223a64702-1c715bfb42d8156a684f7817f9a6891873d4667d3e0df29e8d9b971d1b691a07';
(No rows)

A user would have to know the secret to be able to forge the signature, and since we made it a separate column, we can easily audit our application and logs to ensure that column is never used as plain text.

Finally, let’s make an index to speed this up:

=# CREATE INDEX sessions_cookie_idx ON sessions(
  (id || '-' || encode(hmac(id::text, key, 'sha256'), 'hex'))

That keeps our cookie query computed in an index so our lookups can use the index to quickly find a session match. This is really important because we’re going to have a lot of sessions and we’ll look them up on every request! PostgreSQL’s computed indices are really handy here!


In this post we learned how to make secure signed cookies with pgcrypto to make tamper-proof cookies that don’t expose any user information. It’s worth noting that there are other attack vectors for sessions that this solution doesn’t cover:

  1. Session fixation
  2. Cross-site scripting
  3. Man-in-the-middle

In addition to secure cookies, you should definitely use some form of CSRF protection, always set http-only and secure on your cookies, and always use https. And of course, there are more kinds of attacks out there, so do your homework, or even better: use your framework :)

— Nick Gauthier

Frustrated with video conferencing?

MeetSpace has the best audio quality and reliability around, and is built for distributed teams.

Start Your Free Trial