67 lines
2.7 KiB
PL/PgSQL
67 lines
2.7 KiB
PL/PgSQL
-- OAuth2 客户端与令牌表（Authorization Code + PKCE，opaque access/refresh token）
|
||
-- 依赖 001_iam.sql
|
||
|
||
BEGIN;
|
||
|
||
CREATE TABLE IF NOT EXISTS oauth_client (
|
||
id varchar(36) PRIMARY KEY,
|
||
client_id varchar(64) NOT NULL UNIQUE,
|
||
client_secret_hash varchar(255) NULL,
|
||
redirect_uris text NOT NULL,
|
||
is_public boolean NOT NULL DEFAULT true,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS oauth_authorization_code (
|
||
id varchar(36) PRIMARY KEY,
|
||
code_hash varchar(64) NOT NULL,
|
||
client_id varchar(64) NOT NULL,
|
||
redirect_uri text NOT NULL,
|
||
user_id varchar(36) NOT NULL,
|
||
tenant_id varchar(36) NOT NULL,
|
||
scope text NOT NULL DEFAULT '',
|
||
code_challenge varchar(128) NOT NULL,
|
||
code_challenge_method varchar(16) NOT NULL,
|
||
expires_at timestamptz NOT NULL,
|
||
used boolean NOT NULL DEFAULT false,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_oauth_authorization_code_hash ON oauth_authorization_code (code_hash);
|
||
|
||
CREATE TABLE IF NOT EXISTS oauth_access_token (
|
||
id varchar(36) PRIMARY KEY,
|
||
token_hash varchar(64) NOT NULL UNIQUE,
|
||
client_id varchar(64) NOT NULL,
|
||
user_id varchar(36) NOT NULL,
|
||
tenant_id varchar(36) NOT NULL,
|
||
scope text NOT NULL DEFAULT '',
|
||
expires_at timestamptz NOT NULL,
|
||
revoked_at timestamptz NULL,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS oauth_refresh_token (
|
||
id varchar(36) PRIMARY KEY,
|
||
token_hash varchar(64) NOT NULL UNIQUE,
|
||
access_token_id varchar(36) NOT NULL,
|
||
client_id varchar(64) NOT NULL,
|
||
user_id varchar(36) NOT NULL,
|
||
tenant_id varchar(36) NOT NULL,
|
||
scope text NOT NULL DEFAULT '',
|
||
expires_at timestamptz NOT NULL,
|
||
revoked_at timestamptz NULL,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_oauth_refresh_access ON oauth_refresh_token (access_token_id);
|
||
|
||
-- 开发用公开客户端（PKCE，无 secret）；redirect 按实际前端修改
|
||
INSERT INTO oauth_client (id, client_id, client_secret_hash, redirect_uris, is_public) VALUES (
|
||
'30000000-0000-4000-8000-000000000001',
|
||
'spa',
|
||
NULL,
|
||
'["http://localhost:5173/callback","http://127.0.0.1:5173/callback","http://localhost:3000/callback"]',
|
||
true
|
||
) ON CONFLICT (client_id) DO NOTHING;
|
||
|
||
COMMIT;
|