PostgreSQL: 在插入时创建访问令牌 (Access Token)

Question

在插入时，我应该如何唯一地设置access_token的值为随机字符串并将该值返回给客户端？

我有以下内容：

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

数据库服务器: PostgreSQL
应用服务器: Sinatra (Ruby)
客户端: iOS

- ma11hew28

---

为什么这是一个 Ruby 问题？ - the Tin Man

你考虑过触发器吗？当插入操作发生时，可以定义一个触发器，使access_token设置为随机字符串并返回该值。请参阅http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html。 - user1509107

@theTinMan 因为我不确定 解决方案堆栈 的哪一部分应该执行此操作。 - ma11hew28

类似问题，但不要求唯一性：http://dba.stackexchange.com/questions/19632/how-to-create-column-in-db-with-default-value-random-string - ma11hew28

在该列上使用UUID和默认值怎么样？具体信息请参考http://www.postgresql.org/docs/current/static/datatype-uuid.html。 - user330315

@user102890，感谢您告知触发器的相关信息。不过，我认为像“a_horse_with_no_name”所建议的在列上设置默认值似乎是一个更简单的解决方案。 - ma11hew28

Answer 1

在阅读了Ruby on Rails安全指南和研究其他成功应用的方法之后，我找到了一种更好的生成访问令牌的方式。

旧思路

在询问这个问题之前，我曾经尝试使用Facebook Graph API，并且我知道我想能够像这样发出RESTful请求：

GET /me?access_token=4976517fd7814040b2083864973ff422

通过 access_token，服务器可以返回关于我的信息。

因此，我认为 access_token 必须是唯一的，这样服务器才能执行类似以下操作：

SELECT * FROM users WHERE access_token = '4976517fd7814040b2083864973ff422'

新思维

让我们来看一些来自其他成功API的访问令牌示例。

Facebook: 50601675619|5Lfrygz7QmiNVSgkvryzixIVHuo (应用令牌)
Twitter: 191074378-1GWuHmFyyKQUKWV6sR6EEzSCdLGnhqyZFBqLagHp
Instagram: fb2e77d.47a0479900504cb3ab4a1f626d174d2d
GitHub: e72e16c7e42f292c6912e7710c838347ae178b4a

Facebook和Twitter都明确地在用户的访问令牌前缀中加上了他的ID。Instagram似乎也是这样做的，只是编码了。这样的访问令牌本质上有两个部分：用户ID和会话ID。

嵌入用户ID的访问令牌使服务器能够：

SELECT * FROM users WHERE id = '50601675619'

然后，它可以简单地检查访问令牌的第二部分是否与存储在数据库中该用户的会话ID匹配，类似于验证登录期间的用户名和密码。

最终解决方案

Instagram访问令牌的第二部分似乎是一个没有连字符的小写版本4 UUID。因此，我将按照@a_horse_with_no_name的评论和@ClodoaldoNeto的答案进行操作。除此之外，我将把列access_token重命名为session_id并移除UNIQUE约束。

- ma11hew28

Answer 2

如上所述，请使用 uuid 类型

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
);

然后插入默认值。

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

要使用uuid函数，需要以超级用户身份在目标数据库中安装uuid-ossp扩展。

create extension "uuid-ossp";

您可以使用Ruby中的uuid函数，或者从其他地方获取该函数。

阅读您的评论后，看起来您需要一个会话ID。

可以使用UUID生成器生成会话ID。但请注意，会话ID只是某个会话的ID。每次用户登录时都会更改。会话ID不应存储在用户表中。

通常，会话由应用程序使用一些框架提供的模块处理。该模块将具有其自己的方法来存储会话ID和会话数据。通常，会话ID放在cookie中，而不是URL中。应用程序将向会话管理器传递一个盐，并且它将负责生成会话ID并保持其状态，无论是在cookie、URL、页面中还是其他位置。该模块将根据其配置将会话数据存储在内存、磁盘文件、数据库中。

因此，请不要自己进行会话管理。让框架的解决方案来处理。这样更简单，而且非常重要的是，更安全。

- Clodoaldo Neto

---

@MattDiPasquale：由序列生成的id是完全没有意义的，因此序列中的间隙也是没有意义的。 UUID应该对您的用例足够唯一 - 尤其是如果仅在单个系统上创建。 - user330315

@a_horse_with_no_name 另外，我希望用户ID能够表示用户加入的顺序，就像Facebook上那样。我记得当Facebook变得流行时，我的朋友和我会比较用户ID，以炫耀谁是最早加入的。:-) 我觉得这很酷。 - ma11hew28

@MattDiPasquale：如果您担心插入数量，那么为什么一开始要使用smallserial？使用bigserial，您将永远不会用完数字。从一开始就限制自己只有32767行是值得怀疑的。 - user330315

@MattDiPasquale：如果你只期望有32768个用户，那么使用smallint、int或bigint都不会有任何区别。 - user330315

@ClodoaldoNeto 谢谢你的更新答案。这对我很有帮助。但是，我还需要将用户连接到Node.js WebSocket聊天服务器，该服务器与RESTful Sinatra服务器分开。所以，我想我需要一个OAuth2访问令牌。也许我会创建一个名为“clients”的表，并在其中存储“client_id”和“client_secret”。iPhone应用程序可以是一个客户端，Web应用程序可以是另一个客户端。我想我可以在Redis中存储用户的访问令牌。或者，也许我应该使用现有的OAuth2授权服务器。 - ma11hew28