PostgreSQL: Create Access Token on Insert

Question

On insert, how should I uniquely set the value of access_token to a random string and return that value to the client?

I have:

CREATE TABLE user (
    id             serial PRIMARY KEY,
    access_token   char(32) NOT NULL UNIQUE,
    joined         timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Database Server: PostgreSQL
Application Server: Sinatra (Ruby)
Client: iOS

Answer 1

After reading the Ruby on Rails Security Guide and studying how other successful apps do it, I figured out a better way to generate access tokens.

Old Thinking

Before asking this question, having played with the Facebook Graph API, I knew I wanted to be able to make a RESTful request like:

GET /me?access_token=4976517fd7814040b2083864973ff422

and from the access_token, the server would be able to return the information about me.

Therefore, I thought the access_token had to be UNIQUE so that the server could do something like:

SELECT * FROM users WHERE access_token = '4976517fd7814040b2083864973ff422'

New Thinking

Let's look at some examples of access tokens from other successful APIs.

Facebook: 50601675619|5Lfrygz7QmiNVSgkvryzixIVHuo (App Token)
Twitter: 191074378-1GWuHmFyyKQUKWV6sR6EEzSCdLGnhqyZFBqLagHp
Instagram: fb2e77d.47a0479900504cb3ab4a1f626d174d2d
GitHub: e72e16c7e42f292c6912e7710c838347ae178b4a

Facebook & Twitter both clearly prefix a user's access token with his ID. Instagram appears to do the same, just encoded. Such an access token essentially has two parts: user ID & session ID.

An access token with the user ID embedded allows the server to:

SELECT * FROM users WHERE id = '50601675619'

Then, it can simply check that the second part of the access token matches the session ID stored in the database for that user, similar to validating a username & password during log-in.

Final Solution

The second part of Instagram's access token appears to be a lowercased version 4 UUID without hyphens. So, I'll do the same by following the comment by @a_horse_with_no_name & answer by @ClodoaldoNeto. Except, I'll rename the column access_token to session_id and remove the UNIQUE constraint.

Answer 2

As commented above use the uuid type

create table user_table (
    id             serial primary key,
    access_token   uuid default uuid_generate_v4() not null unique,
    joined         timestamp with time zone not null default current_timestamp
);

Then insert the default values

insert into user_table default values
returning access_token;
             access_token             
--------------------------------------
 341ab75c-6b4e-4df0-a2ea-5148434fce5a

To be able to use the uuid functions it is necessary to install the uuid-ossp extension as superuser in the target database

create extension "uuid-ossp";

You can use the uuid functions from Ruby or if they don't exist from somewhere else.

---

Now reading your comments it looks like you are after a session ID.

A session ID could be generated using a UUID generator. But notice that a session ID is just an ID for an certain session. It will change every time the user logs in. The session ID should not be stored in the user's table.

In general, sessions are handled by the application using some framework provided module. That module will have its own means to store the session ID and the session data. In general the session ID goes in a cookie, not in the URL. The application will pass a salt to the session manager and it will take care of generating the session ID and keeping its state, be it in a cookie, in the URL, in the page, whatever. The module will store the session data where it is configured to do, memory, disk file, database.

So don't do session management yourself. Let it to the framework's solution. Much simpler and very important, much safer.